Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Computing First paydate of quarter Expand / Collapse
Author
Message
Posted Monday, January 25, 2010 1:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 5,473, Visits: 23,558
Go here I am sure you can find what you need.
https://www.sqlservercentral.com/blogs/lynnpettis/archive/2009/03/25/some-common-date-routines.aspx


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #853280
Posted Monday, January 25, 2010 1:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:10 PM
Points: 3,081, Visits: 11,234
declare @FirstPayDate	 datetime
set @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')
) a
order by
a.Date

Results:
Date                     FirstPayDateOfCurrentQuarter
----------------------- ----------------------------
2010-07-27 00:00:00.000 2010-07-02 00:00:00.000
2010-08-27 00:00:00.000 2010-07-02 00:00:00.000
2010-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

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 5,473, Visits: 23,558
bgsxygrmn
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.


Do not understand your:
that doesn't really help me at all.


You want someone to give you the complete T-SQL statement?

Remember People here help those who help themselves

Please read the first link in my signature block for the correct procedure to post a question, paying particular attention to table definition, sample data, expected results and what you have done


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #853313
Posted Monday, January 25, 2010 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:31 PM
Points: 2,628, Visits: 19,098
option: add it to a calendar table, logging which days are paydates.

Similar stuff: http://jasonbrimhall.info/?p=193


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally 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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse