SQL Server Performance

Day Range Caluculations

Discussion in 'General Developer Questions' started by vamsimusunuru, Feb 4, 2007.

  1. vamsimusunuru New Member

    I want the Days to be splited into intervals of 90 days between the two given Dates. Like if we Give the dates as '01/01/2006' AND '10/02/2007', then the Result shold be as follows..

    0 -90

    91 -180

    181 -270

    271 -360

    361 -451



    The Date Range is Dynamic, Upon the Date range Increases the Rows also has to be increased.

    Thanks in Advance for you Help!!!


  2. khtan New Member


    declare@startdatetime,
    @enddatetime

    select@start = '20060101',
    @end= '20070210'


    selectfd= (n * 90) + 1,
    td= (n + 1) * 90
    from(
    select0 as n union all select 1 union all select 2 union all select 3 union all
    select4 union all select 5 union all select 6 union all select 7 union all
    select 8 union all select 9
    ) d
    where(n + 1) * 90 < datediff(day, @start, @end)



    KH
  3. khtan New Member

    for the derived table d of numbers, you can replace with a permanent tally table. For creation of a tally table, do a search in this forum.


    KH
  4. vamsimusunuru New Member

    Thanks for your Prompt Reply... I have a small Doubt, what if the 'n' value has to exceed 9?
  5. khtan New Member

    quote:Originally posted by vamsimusunuru

    Thanks for your Prompt Reply... I have a small Doubt, what if the 'n' value has to exceed 9?

    Well as i said in my 2nd post. Use a tally table to do this. For the above query, i am using a derived table to illustrate the query.


    declare@startdatetime,
    @enddatetime

    select@start = '20060101',
    @end= '20070210'

    selectfd= (n * 90) + 1,
    td= (n + 1) * 90
    fromtally
    where(n + 1) * 90 < datediff(day, @start, @end)

    -- sample tally table structure
    create table tally
    (
    n int
    )




    KH
  6. FrankKalis Moderator

    Just out of curiosity.
    What do you want this for?
    And are you really sure you want 90 days and not 3 months or a quarter? Just asking, because in some cases 90 days != 3 months != 1 quarter.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  7. vamsimusunuru New Member

    Thanks for your Reply KH.

    Hi Frank Kalis, I just came accross a Seinario, where I want the Date Range in Days instead of 3 Months or Quater.
  8. vamsimusunuru New Member

    To the extension of the above, How to get, to Which Quarter the above Range Belong to in the same date Range i.e, '01/01/2006' AND '10/02/2007' ?



    0 -90 1st Qauter

    91 -180 2nd Quater

    181 -270 3rd Quater

    271 -360 4th Quater

    361 -451 1st Quater
  9. khtan New Member

    What is your definition of Quarter ? Is it same as the datepart(quarter,getdate()) ?


    KH
  10. vamsimusunuru New Member

  11. khtan New Member


    declare@startdatetime,
    @enddatetime

    select@start = '20060101',
    @end= '20070210'


    selectfd= (n * 90) + 1,
    td= (n + 1) * 90,
    qt= datepart(quarter, dateadd(day, (n * 90) + 1, @start))
    from(
    select0 as n union all select 1 union all select 2 union all select 3 union all
    select4 union all select 5 union all select 6 union all select 7 union all
    select 8 union all select 9
    ) d
    where(n + 1) * 90 < datediff(day, @start, @end)



    KH
  12. Kewin New Member

    Just for kicks, here's a variation where you can enter the size of the interval as a parameter too.
    The example depends on a numberstable named nums, with a single column named n.

    -- small example how to create high/low marks
    -- based on a size parameter

    declare @mod int, @start datetime, @enddatetime
    select@start = '20060101', @end = '20070210',
    @mod = 90 -- change according to the desired day-interval


    select (n.n - (n.n % @mod)) + 1 as lowrange,
    ((n.n - (n.n % @mod)) + @mod) as highrange
    from nums n
    where n < datediff(day, @start, @end)
    group by (n.n - (n.n % @mod)) + 1, ((n.n - (n.n % @mod)) + @mod)
    order by lowrange, highrange
    go



    lowrange highrange
    ----------- -----------
    1 90
    91 180
    181 270
    271 360
    361 450

    (5 row(s) affected)

    =;o)
    /Kenneth
  13. khtan New Member

    excellent solution. Kennenth


    KH
  14. FrankKalis Moderator

    There you are Kenneth! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Hoping to see "It depends..." quite often here. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  15. FrankKalis Moderator

    ... that was before I checked your profile... [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. Kewin New Member

    And cheers to you too Frank. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />So, it's here you're hiding.<br />Just found my way over here, so I figured I'd poke around from time to time when I get some time 'in between'. =;o)<br /><br />And thnx khtan. Glad you like it.<br /><br />/Kenneth
  17. FrankKalis Moderator

    I don't believe I need to hide anyway. I've justed changed preferences. Looking forward to your future postings here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  18. vamsimusunuru New Member

    Thanks for your Valuble and different Solutions...
  19. vamsimusunuru New Member

    declare@startdatetime,@enddatetime
    select@start = '20060101',@end= '20070210'
    selectfd= (n * 90) + 1,
    td= (n + 1) * 90,
    qt= datepart(quarter, dateadd(day, (n * 90) + 1, @start))
    from(select0 as n union all select 1 union all select 2 union all select 3 union allselect4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
    where(n + 1) * 90 < datediff(day, @start, @end)

    The last 'qt' is shown as 3 instead of 4, How come this is happening?
  20. Kewin New Member

    Because it depends.. on what you are referring to =;o)

    All things being relative, a 'quarter' is relative to a calendar.
    So, it depends on the startdate, and also the intervals. 90 days aren't the same as a quarter exactly.
    And, you can also see from the example below, that the starting offset makes dates slightly different
    when compared to the start or end parameter.


    declare @mod int, @start datetime, @enddatetime
    select@start = '20060101', @end = '20070210',
    @mod = 90 -- change according to the desired day-interval


    selectx.lowrange,
    x.highrange,
    dateadd(day, x.lowrange, @start) as 'lowDate',
    dateadd(day, x.highrange, @start) as 'highDate',
    datepart(quarter, dateadd(day, x.lowrange, @start)) as 'lowQuart',
    datepart(quarter, dateadd(day, x.highrange, @start)) as 'highQuart'
    from (
    select (n.n - (n.n % @mod)) + 1 as 'lowrange',
    ((n.n - (n.n % @mod)) + @mod)as 'highrange'
    from nums n
    where n < datediff(day, @start, @end)
    group by (n.n - (n.n % @mod)) + 1, ((n.n - (n.n % @mod)) + @mod)
    ) x
    order by x.lowrange, x.highrange
    go

    lowrange highrange lowDate highDate lowQuart highQuart
    -------- --------- ----------- ----------- -------- ---------
    1 90 2006-01-02 2006-04-01 1 2
    91 180 2006-04-02 2006-06-30 2 2
    181 270 2006-07-01 2006-09-28 3 3
    271 360 2006-09-29 2006-12-27 3 4
    361 450 2006-12-28 2007-03-27 4 1

    So, depending on the number you derive in your intervals, that number when 'converted backwards' to a date again,
    may lie on different places in the calendar.

    You have to choose what you want to deal with, numbers or dates.
    It's very difficult to get it all in one go.

    /Kenneth
  21. vamsimusunuru New Member

    Yes, you are correct Kenneth, I missed this logic while Itrating in the Loop. Thanks for your Clear and Detailed Example.

Share This Page