May 6, 2009 at 1:44 pm
Can someone help me understand what this date calculation is doing?
AND ISNULL(enrollment_end_date,'12/31/9999') > DATEADD(q, ([deliver_q] - 3), (DATEADD(yyyy,([deliver_y] - 1900),'1/1/1900')))
enrollment_end_date = '2007-02-26'
deliver_q = 1
deliver_y = 2009
What i think its doing is: If the enrollment end date is null, set the date to 12/31/9999
and check that it is greater than ??? (I don't know how to translate this and the creator of the statement can't remember why it was done that way).
May 6, 2009 at 2:07 pm
The DateAdd Year part gets you the first day of the delivery year. If you subtract 1900 from the year, and add that to 1/1/1900, you get 1 Jan of the delivery year. I'm used to seeing that on more complex dates.
Then, once it has the year, it does a similar thing with the quarter, and gets you day 1 of the quarter that's 3 quarters before the delivery quarter (that's what the -3 does). So, if I run it with the dates you have, it gives 1 July 2008, which is 3 quarters before quarter 1 of 2009.
So, what it's doing overall is checking to see if the enrollment date is after the first day of the third quarter before.
Can't tell you why it does it, but that's what it's doing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2009 at 2:09 pm
Okay here's a shot at it:
DATEADD(yyyy,([deliver_y] - 1900),'1/1/1900')
This takes the the number of years since 1900 and adds them to 1/1/1900 so for 2009 you get 1/1/2009. Which then makes this:
DATEADD(q, ([deliver_q] - 3), (DATEADD(yyyy,([deliver_y] - 1900),'1/1/1900')))
The start of the quarter 3 quarters ago. So [deliver_q] - 3 is going to return: -2, -1, 0 or 1. This value will be added to the the date so for your example data the comparison would be:
'2007-02-26' > 2008-07-01
Because the start of the quarter before 1/1/2009 in 7/1/2008.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 2:14 pm
thank you so much, i wanted to make sure i am understanding this okay, and you have explained it well enough that i can write up something in Plain English for future developers to use!! Now I can explain it to the other developers I am working with.
Once again, thank you! :-):-):cool:
May 6, 2009 at 2:20 pm
No problem. You may want to read and point dev's to this blog post by Lynn Pettis.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 2:22 pm
Oops, you were probably thanking Gus as his explanation is much clearer than mine. You should still check out Lynn's blog though.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 6, 2009 at 2:23 pm
Thank you, and I will have my teammates read this particular blog, although we all have started to use your forums to look for help! I really appreciate you help in this matter, you have saved me hours of banging my head on the wall.
May 6, 2009 at 2:34 pm
I just tried a couple of different ways to get that same data, and that one performs as well as any of the others I could come up with. Tested it on 100-thousand rows of data. So, should be fine.
Glad we could help you document it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply