BREAKDOWN DATE CALCULATION

  • 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).

  • 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

  • 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.

  • 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:

  • No problem. You may want to read and point dev's to this blog post by Lynn Pettis.

  • Oops, you were probably thanking Gus as his explanation is much clearer than mine. You should still check out Lynn's blog though.

  • 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.

  • 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