Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Computing First paydate of quarter Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, January 25, 2010 1:20 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, August 14, 2012 7:54 AM Points: 5, Visits: 46
 Can anyone help me compute the first paydate of the current quarter? This is assuming that there's 14 day intervals for paydates and 1/1/2010 is a paydate. So say the current date is july 27th 2010. The first paydate of the quarter would be july 2nd 2010.Can anyone help?Thanks
Post #853270
 Posted Monday, January 25, 2010 1:33 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 7:22 PM Points: 5,334, Visits: 21,999
Post #853280
 Posted Monday, January 25, 2010 1:39 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, August 14, 2012 7:54 AM Points: 5, Visits: 46
 Thanks, but that doesn't really help me at all. I think that I would have to combine two of the functions that are listed in the link.
Post #853284
 Posted Monday, January 25, 2010 2:24 PM
 Hall of Fame Group: General Forum Members Last Login: Today @ 3:18 PM Points: 3,024, Visits: 10,997
 `declare @FirstPayDate datetimeset @FirstPayDate = '20100101'select *, FirstPayDateOfCurrentQuarter = dateadd(dd,(datediff(dd,@FirstPayDate,dateadd(dd,13, dateadd(qq,datediff(qq,@FirstPayDate,Date),@FirstPayDate)))/14)*14,@FirstPayDate)from ( select Date = convert(datetime,'20100727') union all select Date = convert(datetime,'20100827') union all select Date = convert(datetime,'20101001') ) aorder by a.Date`Results:`Date FirstPayDateOfCurrentQuarter----------------------- ----------------------------2010-07-27 00:00:00.000 2010-07-02 00:00:00.0002010-08-27 00:00:00.000 2010-07-02 00:00:00.0002010-10-01 00:00:00.000 2010-10-08 00:00:00.000(3 row(s) affected)`
Post #853312
 Posted Monday, January 25, 2010 2:24 PM
 SSCertifiable Group: General Forum Members Last Login: Today @ 7:22 PM Points: 5,334, Visits: 21,999
Post #853313
 Posted Monday, January 25, 2010 2:33 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, August 14, 2012 7:54 AM Points: 5, Visits: 46
 Thanks Michael, That did the trick. I edited your code to:select dateadd(dd,(datediff(dd,'1/1/2010',dateadd(dd,13, dateadd(qq,datediff(qq,'1/1/2010',getdate()),'1/1/2010')))/14)*14,'1/1/2010') and that worked perfectly. I don't think that I would have figured it out just using one dateadd and one datediff
Post #853322
 Posted Monday, January 25, 2010 3:26 PM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 1:55 PM Points: 2,591, Visits: 18,997
 option: add it to a calendar table, logging which days are paydates. Similar stuff: http://jasonbrimhall.info/?p=193 ---------------------------------------------------------How best to post your questionHow to post performance problemsTally Table:What it is and how it replaces a loop"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #853351

 Permissions