Day Range Caluculations
-
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!!!
-
I am not sure exactly what you want to do with it but this should give you some idea of how it could be achieved by turning the script below into a function or stored procedure.
DECLARE
@StartDate DATETIME,
@EndDate DATETIME,
@Period INT,
@Count INT,
@Intervals INT,
@DayNum INT,
@Remainder INT
SET @StartDate = '01/01/2006'
SET
@EndDate = '10/02/2007'
SET
@Count = 1
SET
@DayNum = 0
SET
@Remainder = 0
SET
@Intervals = DATEDIFF(day, @StartDate, @EndDate)/90 + 1
SET
@Remainder = DATEDIFF(day, @StartDate, @EndDate) - ((@Intervals-1)*90)
WHILE @Count < @Intervals
BEGIN
PRINT CAST(@DayNum as varchar) + '_' + CAST((@DayNum + 90) AS VARCHAR)
SET @DayNum = @DayNum + 90
Set @Count = @Count + 1
END
PRINT CAST(@DayNum as varchar) + '_' + CAST((@DayNum + @Remainder) AS VARCHAR)
-
Set based solution... substitute a tally table for spt_Values if you have one...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
PRINT DATEDIFF(dd,@StartDate,@EndDate)+1 --Just to verify the number of days
SELECT Number*90+1,
CASE
WHEN (Number+1)*90 < DATEDIFF(dd,@StartDate,@EndDate)+1
THEN (Number+1)*90
ELSE DATEDIFF(dd,@StartDate,@EndDate)+1
END
FROM Master.dbo.spt_Values
WHERE Name IS NULL
AND Number <= CAST(DATEDIFF(dd,@StartDate,@EndDate)+1 AS INT)/90
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Same as above but single column output...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
PRINT DATEDIFF(dd,@StartDate,@EndDate)+1
SELECT STR(Number*90+1,4)
+ ' -'
+ STR(CASE
WHEN (Number+1)*90 < DATEDIFF(dd,@StartDate,@EndDate)+1
THEN (Number+1)*90
ELSE DATEDIFF(dd,@StartDate,@EndDate)+1
END,4)
FROM Master.dbo.spt_Values
WHERE Name IS NULL
AND Number <= CAST(DATEDIFF(dd,@StartDate,@EndDate)+1 AS INT)/90
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff - two questions. [ tia ]
1) WHERE Name IS NULL - is that specific to your Master.dbo.spt_Values table?
2) Does this represent your answer with a Table Variable for a "Numbers" table? If so, it looks to be missing the 0 - 90...
DECLARE @Number TABLE( Number integer)
DECLARE @Counter integer
SET @Counter = 1
WHILE @Counter <= 500
BEGIN
INSERT INTO @Number SELECT @Counter
SET @Counter = @Counter + 1
END
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
PRINT DATEDIFF( dd, @StartDate, @EndDate) + 1
SELECT STR( Number * 90 + 1, 4) + ' -' +
STR( CASE
WHEN( Number + 1) * 90 < DATEDIFF( dd, @StartDate, @EndDate) + 1
THEN( Number + 1) * 90
ELSE DATEDIFF( dd, @StartDate, @EndDate) + 1
END, 4)
FROM @Number
WHERE Number <= CAST( DATEDIFF( dd, @StartDate, @EndDate) + 1 AS integer) / 90
Output
----------
91 - 180
181 - 270
271 - 360
361 - 450
451 - 540
541 - 630
631 - 640
I wasn't born stupid - I had to study.
-
1) WHERE Name IS NULL - is that specific to your Master.dbo.spt_Values table?
It is specific to the Master.dbo.spt_Values table... however, the table isn't "my" table, it's part of SQL Server and it'll always be there. The numbers 0 through 255 appear as "constants" where the column "Name" is null.
2) Does this represent your answer with a Table Variable for a "Numbers" table? If so, it looks to be missing the 0 - 90
Heh... yeah, I thought of that after I posted... the spt_Values table starts at "0" and not "1" like most numbers tables do and I forgot about that... the corrected solution for a numbers table that starts at "1", as in your example, would be...
DECLARE @Number TABLE( Number INT)
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 500
BEGIN
INSERT INTO @Number SELECT @Counter
SET @Counter = @Counter + 1
END
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
PRINT DATEDIFF(dd, @StartDate, @EndDate) + 1
SELECT STR((Number-1) * 90 + 1, 4)
+ ' -'
+ STR(CASE
WHEN(Number) * 90 < DATEDIFF(dd, @StartDate, @EndDate) + 1
THEN(Number) * 90
ELSE DATEDIFF(dd, @StartDate, @EndDate) + 1
END, 4)
FROM @Number
WHERE Number <= (CAST(DATEDIFF(dd, @StartDate, @EndDate) + 1 AS INT) / 90) + 1
...although, if I were going to use a table variable, I'd probably use Michael Valentine Jone's solution for creating such a numbers table variable, instead of a loop... it's nasty fast, too... I renamed my copy "fnTally"
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
Of course, if you use such a virtually unlimited, highly programmable function as his or a larger Tally table instead of a table with only 256 numbers in it (spt_Values), the code becomes a lot simpler for this same thing... I'll crank out the example tonight and post it here...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ok, here's some sample code using Michael's function that I renamed as "fnTally" and changed the "Numbers" column to just an "N" to match what I also have in my Tally table... I didn't write examples using a Tally table because just about anyone should be able to figure that out from a previous example... AND, this one has "programmable" bin sizes... if you are going to use the code below with a numbers table, you either need to offset the code by 1 or start your numbers table at 0...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Days INT
DECLARE @BinSize INT
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
SET @Days = DATEDIFF(dd, @StartDate, @EndDate) + 1
SET @BinSize = 90
PRINT DATEDIFF(dd, @StartDate, @EndDate) + 1
--===== Returns what the original poster really wanted (0-90, 91-180, 181-270, etc)
SELECT CASE WHEN fnt.N = 0 THEN 0 ELSE fnt.N + 1 END AS StartDay,
CASE WHEN fnt.N + @BinSize < @Days THEN fnt.N + @BinSize ELSE @Days END AS EndDay
FROM dbo.fnTally(0, @Days) fnt
WHERE fnt.N % @BinSize = 0
--===== Return something a bit more conventional (0-89, 90-179, 180-269, etc)
SELECT fnt.N AS StartDay,
fnt.N + @BinSize - 1 AS EndDay
FROM dbo.fnTally(0, @Days) fnt
WHERE fnt.N % @BinSize = 0
--===== Return something thats real easy for >= and < calcs and people are used to seeing
-- (0-90, 90-180, 180-270, etc)
SELECT fnt.N AS StartDay,
fnt.N + @BinSize AS EndDay
FROM dbo.fnTally(0, @Days) fnt
WHERE fnt.N % @BinSize = 0
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Holy #$% Jeff! Thanks!
I will mess with this tomorrow; and probably save this in my stash of code.
I wasn't born stupid - I had to study.
-
Thanks for your Valuble time and Help Christopher, Jeff Moden and Farrell Keough.
-
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
-
Thanks Farrell,
That's a high compliment coming from folks like yourself.
Vamsi,
On my way to work so can't answer in detail just now but, I need to know, what are the boundaries of your "quarters", what is the first quarter, and what do you want to do if the number of days straddles the preverbial fence on a quarter boundary?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Quater means QUARTER in given DATEPART. Hope you understood me.
-
Perfect... using the same function I used previously, adding quarter to this is easy...
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @Days INT
DECLARE @BinSize INT
SET @StartDate = '01/01/2006'
SET @EndDate = '10/02/2007'
SET @Days = DATEDIFF(dd, @StartDate, @EndDate) + 1
SET @BinSize = 90
PRINT DATEDIFF(dd, @StartDate, @EndDate) + 1
--===== Returns what the original poster really wanted (0-90, 91-180, 181-270, etc)
SELECT CASE WHEN fnt.N = 0 THEN 0 ELSE fnt.N + 1 END AS StartDay,
CASE WHEN fnt.N + @BinSize < @Days THEN fnt.N + @BinSize ELSE @Days END AS EndDay,
DATEPART(qq,@StartDate+fnt.N) AS Qtr
FROM dbo.fnTally(0, @Days) fnt
WHERE fnt.N % @BinSize = 0
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply