Split a date-range into periods
-
Hi all,
I have a problem and I don't know if it can be solved within SQL. I am thinking about a TALLY table but I can't get a good start.
By executing a query on some tables I will get a result like this:
StartDateEndDateUnits
2008-03-07 00:00:00.0002008-04-16 00:00:00.0003
2008-04-17 00:00:00.0002008-04-30 00:00:00.0005
2008-05-01 00:00:00.0002008-06-29 00:00:00.0001
2008-06-30 00:00:00.0002008-07-14 00:00:00.0004
2008-07-15 00:00:00.0002008-09-02 00:00:00.0001
2008-09-03 00:00:00.0002008-09-30 00:00:00.0008
2008-10-01 00:00:00.0002008-10-22 00:00:00.0003
As you can see, some rows contain a startdate and an enddate that are in different months. I want to split those rows into seperate rows with the startdate and the enddate in the same month. Like the period "2008-07-15" till "2008-09-02" must be split into three seperate rows ("2008-07-15" till "2008-07-31", "2008-08-01" till "2008-08-31" and "2008-09-01" till "2008-09-02")
Is this possible??
Below is some sample code. The last SELECT shows the final results I need.
if exists (select * from tempdb.sys.objects where type = 'U' and name like '#DatePeriod%')
drop table #DatePeriod
Create table #DatePeriod
(StartDate DateTime,
EndDate DateTime,
Units int)
-- create the sample data
INSERT INTO #DatePeriod
SELECT '20080307','20080416',3 UNION ALL
SELECT '20080417','20080430',5 UNION ALL
SELECT '20080501','20080629',1 UNION ALL
SELECT '20080630','20080714',4 UNION ALL
SELECT '20080715','20080902',1 UNION ALL
SELECT '20080903','20080930',8 UNION ALL
SELECT '20081001','20081022',3
SELECT * FROM #DatePeriod
DROP TABLE #DatePeriod
-- show the final result I need
SELECT '20080307' as 'begin','20080331' as 'end',3 as 'Units' UNION ALL
SELECT '20080401','20080416',3 UNION ALL
SELECT '20080417','20080430',5 UNION ALL
SELECT '20080501','20080531',1 UNION ALL
SELECT '20080601','20080629',1 UNION ALL
SELECT '20080630','20080630',4 UNION ALL
SELECT '20080701','20080714',4 UNION ALL
SELECT '20080715','20080731',1 UNION ALL
SELECT '20080801','20080831',1 UNION ALL
SELECT '20080901','20080902',1 UNION ALL
SELECT '20080903','20080930',8 UNION ALL
SELECT '20081001','20081022',3
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
You'll need a calendar table with month start and end dates
if object_id('tempdb..#Calendar') is not nulldrop table #Calendar
Create table #Calendar(dtStart DateTime,dtEnd as dateadd(day,-1,dateadd(month,1,dtStart)))
insert into #Calendar(dtStart)
select '20080301' union all
select '20080401' union all
select '20080501' union all
select '20080601' union all
select '20080701' union all
select '20080801' union all
select '20080901' union all
select '20081001'
SELECT CASE WHEN p.StartDate>c.dtStart THEN p.StartDate ELSE c.dtStart END AS 'begin',
CASE WHEN p.EndDate<c.dtEnd THEN p.EndDate ELSE c.dtEnd END AS 'end',
p.Units
FROM #DatePeriod p
INNER JOIN #Calendar c ON c.dtStart BETWEEN p.StartDate AND p.EndDate OR c.dtEnd BETWEEN p.StartDate AND p.EndDate
ORDER BY p.StartDate
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, it works like a charm.
I knew a TALLY table could bring me the solution 😀
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
hi
I know that solution provided works. But my question is;
Is this not a very Hard coded solution for the particular example that was given.
Surely the more generic solution is not much more difficult and should be the prefered solution.? 🙂
-
It seems I can not post code to the site, I wanted to post a more generic solution
-
AnzioBake (10/23/2008)
It seems I can not post code to the site, I wanted to post a more generic solutionHi Anzio,
Allthough the provided solution fits my current needs, I'm interested in your general approach.
You can copy/paste the code into a reply. Use the
-designator to seperate your code from your text (see the IFCode Shortcuts on the lefts side beside your 'new reply' window).** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
I tried that, all of that. I will attempt to put the solution in words.
1. Calculate the dateDiff in months between the Start and End Dates
S: E: Diff:
2008-06-15 2008-08-10 2
2. Join to a Table (tally, derived, CTE ) of numbers that covers your range including 0
Start End Diff Num
2008-06-15 2008-08-10 2 0
2008-06-15 2008-08-10 2 1
2008-06-15 2008-08-10 2 2
3. Calculate the Start of months (and therefore the End )
Start End Diff Num NewStart NewEnd
2008-06-15 2008-08-10 2 0 2008-06-01 2008-06-30
2008-06-15 2008-08-10 2 1 2008-07-01 2008-07-31
2008-06-15 2008-08-10 2 2 2008-08-01 2008-08-31
NewStart = DateAdd(Month, DateDiff( month, 0, Start) +Num ,0 )
Then do the Case When NewStart > Start Etc.
-
Hi Anzio,
I have work out your solution. Maybe others can benefit from this code.
As you allready mentioned, this solution is more generic. The TALLY table now only contains the number of months (between startdate and enddate) instead of a fixed startdate and enddate of possible periods. A bit more coding is involved to get the dates, but that is done only once. 😉
if exists (select * from tempdb..sysobjects where type = 'U' and name like '#Tally%')
drop table #Tally
Create table #Tally
(Numbers tinyint)
-- fill the tally-table with numeric range
INSERT INTO #Tally
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
if exists (select * from tempdb..sysobjects where type = 'U' and name like '#DatePeriod%')
drop table #DatePeriod
Create table #DatePeriod
(StartDate DateTime,
EndDate DateTime,
Units int)
-- create the sample data
INSERT INTO #DatePeriod
SELECT '20080307', '20080416', 3 UNION ALL
SELECT '20080417', '20080430', 5 UNION ALL
SELECT '20080501', '20080629', 1 UNION ALL
SELECT '20080630', '20080714', 4 UNION ALL
SELECT '20080715', '20080902', 1 UNION ALL
SELECT '20080903', '20080930', 8 UNION ALL
SELECT '20081001', '20081022', 3
-- select and calculate the periods
SELECT
StartDate AS OriginalStartDate
, EndDate AS OriginalEndDate
, CASE WHEN (DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)) <= StartDate
THEN StartDate
ELSE DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers ,0)
END AS PeriodStart
, CASE WHEN (DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers + 1 ,0))) > EndDate
THEN EndDate
ELSE DateAdd(Day, -1, DateAdd(Month, DateDiff(month, 0, StartDate) + Numbers + 1 ,0))
END AS PeriodEnd
, Units
FROM
#DatePeriod, #Tally
WHERE
datediff(m, StartDate, EndDate) >= Numbers
ORDER BY StartDate, Numbers
DROP TABLE #DatePeriod
DROP TABLE #Tally
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
On the money.
-
Glad you have a working solution, but I feel the need to point out that with this new "split" table, the meaning of the value in the UNITS field has changed, so attempts to use aggregates against that field may well return incorrect results, because the UNITS value applies to an entire period, for which you now have no indicator in the new table that would mark a record as one that began (or the one that ended) the period to which the UNITS value applies. FYI...
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
smunson (10/23/2008)
...the meaning of the value in the UNITS field has changed...Hi Steve,
Thanks for pointing that out. But I created this sample to only simulate my data. The UNITS value equals in real life multiple columns containing (mostly) text data. In my business case each row needs to have the value of the original period.
But for anyone using this code: Adjust it to your business needs!! :w00t:
** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! ** -
HanShi (10/23/2008)
I have work out your solution. Maybe others can benefit from this code.As you allready mentioned, this solution is more generic. The TALLY table now only contains the number of months (between startdate and enddate) instead of a fixed startdate and enddate of possible periods. A bit more coding is involved to get the dates, but that is done only once. 😉
I missed this post, before. Well done, HanShi!
--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) -
Great post- it looks like I will be able to utilize/modify parts of this code to work with my business needs ... the report I'm creating is tough!
See if you can follow these requirements:
I have a date range, dateA through dateB. (e.g., feb1 - feb28 ... these would be my input parameters for my report)
I have a series of events that fall anywhere inside and/or outside of that date range, partially within that date range, or fully covering that date range.
If my event falls within this date range, but the start of the event precedes my date range, then I need to split that date range at dateA. (So following my example, if my event started was jan20-feb10, then i need that split into two dates, jan20-feb1, and feb1-feb10).
If my event falls within this date range, but the end of the event exceeds my date range, then i need to split that date range at dateB. (so if my event was feb20-mar10, then i need feb20-feb28 and feb28-mar10).
On the same note, if my event precedes AND exceeds my date range, then my single event will need to be split three ways.
That's the problem I am tackling right now, and I should be able to work through it, but man that's tough! If anybody wants to add some input, please do!
-
rncruz,
Does your Events table specify the time as well as the date of the start and end of your events, or is the time portion zero, e.g. '2009-02-03 00:00:00'?
Likewise for your input date range (@DateA, @DateB)
Do these parameters allow times to be specified?
Is this date range inclusive, i.e. if an event starts on @DateB and ends at some date after @DateB, should the event be split into 2 parts?
--Andrew
-
The events table will specify the time, the dateRange values will not.
So for example, I will have, 2009-02-01 00:00:00 thru 2009-02-28 00:00:00
While an event can start at like, "2009-02-01 8:30:00" and end at "2009-03-05 00:00:00"
So this event will be partially inclusive to this date range. This specific event would need to be split into
2009-02-01 thru 2009-02-28 and
2009-02-28 thru 2009-03-05
I've actually just drawn out all my possible situations on paper ... i just need to code it up now ... but it is not proving to be easy ...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply