# Day Range Caluculations

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

1. ### vamsimusunuruNew 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. ### khtanNew 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. ### khtanNew 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. ### vamsimusunuruNew Member

Thanks for your Prompt Reply... I have a small Doubt, what if the 'n' value has to exceed 9?
5. ### khtanNew 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. ### FrankKalisModerator

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. ### vamsimusunuruNew Member

Hi Frank Kalis, I just came accross a Seinario, where I want the Date Range in Days instead of 3 Months or Quater.
8. ### vamsimusunuruNew 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. ### khtanNew Member

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

KH

Yes, KH
11. ### khtanNew 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. ### KewinNew 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. ### khtanNew Member

excellent solution. Kennenth

KH
14. ### FrankKalisModerator

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. ### FrankKalisModerator

... that was before I checked your profile... [<img src='/community/emoticons/emotion-4.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>
16. ### KewinNew Member

And cheers to you too Frank. [<img src='/community/emoticons/emotion-2.gif' alt='' />]<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. ### FrankKalisModerator

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. ### vamsimusunuruNew Member

Thanks for your Valuble and different Solutions...
19. ### vamsimusunuruNew 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. ### KewinNew 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,
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. ### vamsimusunuruNew Member

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