SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split a date-range into periods


Split a date-range into periods

Author
Message
HanShi
HanShi
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14534 Visits: 3765
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:
StartDate EndDate Units
2008-03-07 00:00:00.000 2008-04-16 00:00:00.000 3
2008-04-17 00:00:00.000 2008-04-30 00:00:00.000 5
2008-05-01 00:00:00.000 2008-06-29 00:00:00.000 1
2008-06-30 00:00:00.000 2008-07-14 00:00:00.000 4
2008-07-15 00:00:00.000 2008-09-02 00:00:00.000 1
2008-09-03 00:00:00.000 2008-09-30 00:00:00.000 8
2008-10-01 00:00:00.000 2008-10-22 00:00:00.000 3


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’! **
Mark Cowne
Mark Cowne
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11330 Visits: 26295
You'll need a calendar table with month start and end dates

if object_id('tempdb..#Calendar') is not null
drop 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




HanShi
HanShi
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14534 Visits: 3765
Thanks Mark, it works like a charm.
I knew a TALLY table could bring me the solution BigGrin

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
AnzioBake
AnzioBake
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1402 Visits: 700
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.? Smile



AnzioBake
AnzioBake
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1402 Visits: 700
It seems I can not post code to the site, I wanted to post a more generic solution



HanShi
HanShi
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14534 Visits: 3765
AnzioBake (10/23/2008)
It seems I can not post code to the site, I wanted to post a more generic solution


Hi 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 [code]-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’! **
AnzioBake
AnzioBake
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1402 Visits: 700
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.



HanShi
HanShi
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14534 Visits: 3765
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. Wink


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’! **
AnzioBake
AnzioBake
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1402 Visits: 700
On the money.



sgmunson
sgmunson
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32568 Visits: 5185
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)
SmileSmileSmile

Steve
‌(aka sgmunson)
Smile Smile Smile
Health & Nutrition
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search