How to determine last "monthversary" date of an account?

I work on a project where advertisers on a classifieds Web site have the
possibility to place a certain number of ads per month.
The difficulty comes from the fact that a month period starts at each
"monthversary", meaning that if somebody created his account on June 15, I
would need to take into account the ads placed since the 15th of this
month...
I am simply trying to see how I can extract, in a very compact way, the last
"monthversary" date knowing the date the account was created and the current
date.
Right now I am using a method that works: I extract the day from the
creation date, prepend the current month and append the year, then:
1- if the "monthversary" is after today's date, go back one month
2- take care of the 30-day months issue and of of February as well (even
leap year)
3- take care of the year changes
And then I have a string that represents the last "monthversary" date, but I
am sure there is a simple, one-liner way to do this.
Thanks a lot!White,
Can you post the DDL and sample data? Sounds like the MAX function may work
here.
HTH
Jerry
"White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>I work on a project where advertisers on a classifieds Web site have the
>possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the
> last "monthversary" date knowing the date the account was created and the
> current date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
> I am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>|||lookup datedd and datepart in BOL
select dateadd(m,-1,getdate())
http://sqlservercode.blogspot.com/
"White Echo" wrote:

> I work on a project where advertisers on a classifieds Web site have the
> possibility to place a certain number of ads per month.
> The difficulty comes from the fact that a month period starts at each
> "monthversary", meaning that if somebody created his account on June 15, I
> would need to take into account the ads placed since the 15th of this
> month...
> I am simply trying to see how I can extract, in a very compact way, the la
st
> "monthversary" date knowing the date the account was created and the curre
nt
> date.
> Right now I am using a method that works: I extract the day from the
> creation date, prepend the current month and append the year, then:
> 1- if the "monthversary" is after today's date, go back one month
> 2- take care of the 30-day months issue and of of February as well (even
> leap year)
> 3- take care of the year changes
> And then I have a string that represents the last "monthversary" date, but
I
> am sure there is a simple, one-liner way to do this.
> Thanks a lot!
>
>|||Sorry, different computer if you see what I mean.
But the function is something that I could also use at this computer.
Simply put:
If I opened my account on June 15 2005, what function would help me to
determine what would be the day of the beginning of the current account
period, knowing that an accounting period starts at each "monthversary"?
(By the way I was surprised to see that the expression "monthversary" is out
there).
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
> White,
> Can you post the DDL and sample data? Sounds like the MAX function may
> work here.
> HTH
> Jerry
> "White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote in message
> news:435922b5$0$3767$39cecf19@.news.twtelecom.net...
>|||Nicolas,
Sometimes data works better than narratives. Could you post me some sample
data i.e, here is the date the account was opened, here is the monthversary,
and here is the date I want with x being the current date? Sample data -->
desired results?
Thanks
Jerry
"Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote
in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
> Sorry, different computer if you see what I mean.
> But the function is something that I could also use at this computer.
> Simply put:
> If I opened my account on June 15 2005, what function would help me to
> determine what would be the day of the beginning of the current account
> period, knowing that an accounting period starts at each "monthversary"?
> (By the way I was surprised to see that the expression "monthversary" is
> out there).
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23zVNfTm1FHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||This only removes on month from Today's date... It does not help.
> lookup datedd and datepart in BOL
> select dateadd(m,-1,getdate())
> http://sqlservercode.blogspot.com/
> "White Echo" wrote:
>|||I thought I did this, I am sorry.
For instance:
Account created on June 15, 2005.
Today's date: October 21, 2005.
The result here would be: October 15, 2005
Account created on January 31, 2004.
Today's date: October 21, 2005.
The result here would be: September 30, 2005
If the account is created on June 15, 2005...
The first "month" started on July 15, 2004.
The second "month" on August 15, 2004
Etc...
But what I need to calculate is when the current "month" started for this
account.
The start day of a month is not the first day of a month, but rather what I
call the "monthversary": the same day number as that of the creation date.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elZhW9m1FHA.904@.tk2msftngp13.phx.gbl...
> Nicolas,
> Sometimes data works better than narratives. Could you post me some
> sample data i.e, here is the date the account was opened, here is the
> monthversary, and here is the date I want with x being the current date?
> Sample data --> desired results?
> Thanks
> Jerry
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:435930ec$0$3759$39cecf19@.news.twtelecom.net...
>|||On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
(snip)
>Right now I am using a method that works: I extract the day from the
>creation date, prepend the current month and append the year, then:
>1- if the "monthversary" is after today's date, go back one month
>2- take care of the 30-day months issue and of of February as well (even
>leap year)
>3- take care of the year changes
>And then I have a string that represents the last "monthversary" date, but
I
>am sure there is a simple, one-liner way to do this.
Hi White Echo,
Not sure if it's simpler, but you could write it in one line (though I
prefer slightly more formatting - read this emssage with a fixed font
for best effect), and it has the definite advantage that it can be used
in a query to process all rows at once (though my example uses only a
variable):
DECLARE @.StartDate datetime
SET @.StartDate = '20050615'
SELECT DATEADD(month,
DATEDIFF(month,
@.StartDate,
CURRENT_TIMESTAMP)
- CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
THEN 1
ELSE 0
END,
@.StartDate)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you very much, my friend!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7mmil1t3gtgk6ktgcisgteocp120egtvlh@.
4ax.com...
> On Fri, 21 Oct 2005 10:17:33 -0700, White Echo wrote:
> (snip)
> Hi White Echo,
> Not sure if it's simpler, but you could write it in one line (though I
> prefer slightly more formatting - read this emssage with a fixed font
> for best effect), and it has the definite advantage that it can be used
> in a query to process all rows at once (though my example uses only a
> variable):
> DECLARE @.StartDate datetime
> SET @.StartDate = '20050615'
> SELECT DATEADD(month,
> DATEDIFF(month,
> @.StartDate,
> CURRENT_TIMESTAMP)
> - CASE WHEN DAY(@.StartDate) > DAY(CURRENT_TIMESTAMP)
> THEN 1
> ELSE 0
> END,
> @.StartDate)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)