|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to determine last "monthversary" date of an account?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 Show quote "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! > 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). Show quote "Jerry Spivey" <jspi***@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... >>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! >> > > 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 Show quote "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" <jspi***@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... >>>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! >>> >> >> > > 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. Show quote "Jerry Spivey" <jspi***@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... >> 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" <jspi***@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... >>>>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/ Show quote "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 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! > > > This only removes on month from Today's date... It does not help.
Show quote > 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 >> 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! >> >> >> 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 Hi White Echo,>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. 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!
Show quote "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) >>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)  
Other interesting topics
|
|||||||||||||||||||||||
