Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Split a date-range into periods Expand / Collapse
Author
Message
Posted Wednesday, October 22, 2008 3:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #589668
Posted Wednesday, October 22, 2008 3:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #589682
Posted Wednesday, October 22, 2008 4:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
Thanks Mark, it works like a charm.
I knew a TALLY table could bring me the solution :D


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #589687
Posted Thursday, October 23, 2008 3:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 210, Visits: 520
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.? :)





Post #590327
Posted Thursday, October 23, 2008 3:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 210, Visits: 520
It seems I can not post code to the site, I wanted to post a more generic solution



Post #590329
Posted Thursday, October 23, 2008 4:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #590348
Posted Thursday, October 23, 2008 4:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 210, Visits: 520
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.



Post #590365
Posted Thursday, October 23, 2008 6:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:59 AM
Points: 2,078, Visits: 2,410
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’! **
Post #590394
Posted Thursday, October 23, 2008 7:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 210, Visits: 520
On the money.


Post #590456
Posted Thursday, October 23, 2008 11:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 7:20 AM
Points: 1,528, Visits: 1,971
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)

Internet ATM Machine
Post #590667
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse