Solved

tsql to split a time period in different months

Posted on 2014-12-18
6
341 Views
Last Modified: 2014-12-18
Using MS Sql Server 2008.
I have a time period table with start and end dates. I need to calculate duration (month wise) for each time period. If the end date is null, then use current date as end date. If period between start and end date spans multiple months then I need to breakup duration in different months as shown in sample target data below.
How can I do this using t-sql, preferably without using cursor.

Source data (Insert statements copied below):
ID    StartDate          EndDate
1       9/1/2014          12/3/2014
2       11/9/2014        11/30/2014
3       12/9/2014        null

Target data:
ID     StartDate       EndDate       Duration
1      9/1/2014         10/1/2014    30
1      10/1/2014        11/1/2014   31
1      11/1/2014        12/1/2014   30
1      12/1/2014       12/3/2014    2
2      11/9/2014       11/30/2014    21
3       12/9/2014       12/18/2014   9


Here is the sql for setting some sample source data in TimePeriod.
Create table #timeperiod
(ID int identity(1,1)not null,
StartDate date not null,
EndDate date null)

Insert into #timeperiod
(StartDate,EndDate)
Values ('9/1/2014','12/3/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('11/9/2014','11/30/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('12/9/2014',null)

Open in new window



Thank you for your help in advance.
0
Comment
Question by:patd1
6 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40507416
here it is:

with t as (
select 1 id, getdate()-75 StarDate, getdate()-75+30 EndDate
union select 2, getdate()-100, getdate()-100+31
union select 3, getdate()-9, null
)
select id, StarDate, EndDate, datediff(dd, StarDate, isnull(EndDate, getdate())) Duration
from t

id	StarDate	EndDate	Duration
1	2014-10-04 12:06:20.867	2014-11-03 12:06:20.867	30
2	2014-09-09 12:06:20.867	2014-10-10 12:06:20.867	31
3	2014-12-09 12:06:20.867	NULL	9

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40507420
oops, i guess you need more than that!
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40507426
Here you go:

drop table #timeperiod
drop table #mydates
go

Create table #timeperiod
(ID int identity(1,1)not null,
StartDate date not null,
EndDate date null)

Insert into #timeperiod
(StartDate,EndDate)
Values ('9/1/2014','12/3/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('11/9/2014','11/30/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('12/9/2014',null)

create table #mydates
(StartDate date not null,
EndDate date not null)

insert into #mydates
select top(datediff(month,(select min(StartDate) from #timeperiod),SYSDATETIME())+1)
dateadd(month,-1+row_number() over(order by t1.id),'9/1/2014') as StartDate,
dateadd(month,row_number() over(order by t1.id),'9/1/2014') as FinishDate
from #timeperiod t1
cross join #timeperiod t2

Select * from #mydates;

with myTable as (select T.ID, D.StartDate as MonthStart, D.EndDate as MonthEnd, T.StartDate, T.EndDate
from #mydates D
cross join #timeperiod T
where D.EndDate >= T.StartDate
and (D.startDate <= T.EndDate or T.EndDate is null)),
myTable2 as (Select ID,
case when MonthStart < StartDate then
StartDate else MonthStart end as FirstDate,
case when EndDate is null then getdate()
when EndDate < MonthEnd then EndDate else MonthEnd end as SecondDate
from myTable)
select ID, FirstDate as StartDate, SecondDate as EndDate, Datediff(day,FirstDate,SecondDate) as Duration
from myTable2

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40507460
I've used a "standard" tally table cte to gen month numbers 0 to 99 -- if you'll ever need more than a 99 month duration, just increase the size of the tally table:

;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) - 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT tp.ID, calc_dates.StartDate, calc_dates.EndDate, DATEDIFF(DAY, calc_dates.StartDate, calc_dates.EndDate) + 1 AS Duration
FROM #timeperiod tp
INNER JOIN cteTally100 t ON
    t.tally BETWEEN 0 AND DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE()))
CROSS APPLY (
    SELECT CASE WHEN t.tally = 0 THEN tp.StartDate
         ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally, 0) END AS StartDate,
    CASE WHEN t.tally = DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE())) THEN ISNULL(tp.EndDate, GETDATE())
         ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally + 1, 0)) END AS EndDate
) AS calc_dates
ORDER BY ID, StartDate
0
 

Author Comment

by:patd1
ID: 40507739
Thank you ScottPletcher. The solution is working for me.
Can you please explain what is going on in the crossApply (portion of code copied below)
SELECT CASE WHEN t.tally = 0 THEN tp.StartDate 
         ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally, 0) END AS StartDate,
    CASE WHEN t.tally = DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE())) THEN ISNULL(tp.EndDate, GETDATE()) 
         ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally + 1, 0)) END AS EndDate

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40507855
Sure.

First, as background, the CROSS APPLY as used above basically just assigns aliases to calculated values.  Otherwise, the entire expressions for StartDate and EndDate would have to be repeated when computing the Duration.  The CROSS APPLY in effect creates another column we can then use anywhere in our main query.  This example is somewhat more confusing than normal since the column names are exactly the same as the input column names, but it's actually a completely different value.

A stand-alone example may be clearer:
SELECT value, value1, value2
    --note that I can select all the different CROSS APPLY values
FROM ( SELECT 17 AS value ) AS test_data
CROSS APPLY (
    --multiply the original column value by 2 and give it a new alias name that can then be used anywhere in the query
    SELECT value * 2 AS value1
) AS cross_apply_1
CROSS APPLY (
    --note how we're using an earlier CROSS APPLY alias in this CA, which is an extremely useful capability!
    SELECT value1 * 2 AS value2
) AS cross_apply_2
ORDER BY value1 --note that I can ORDER BY any CA value(s), not just the last one(s)

Back to the original query.  The key ID is one like #1, which spans multiple months:
ID    StartDate          EndDate
 1       9/1/2014          12/3/2014
The output needs to be:
 ID     StartDate       EndDate       Duration
 1      9/1/2014         10/1/2014    30
 1      10/1/2014        11/1/2014   31
 1      11/1/2014        12/1/2014   30
 1      12/1/2014       12/3/2014    2

Here's how the query does this as it executes:
1) The INNER JOIN on tally will be for values 0, 1, 2 and 3, since the "DATEDIFF(MONTH" from StartDate to EndDate is 3 months.  Thus, a total of 4 rows will be output from the query, one for each month -- exactly what we want.
2) Since the output StartDate of the first row is the exact starting date, but for later months it is always the first of the month, when computing the new StartDate value in the CROSS APPLY we need to use "WHEN t.tally = 0" to test for the first row.
3) Similarly, since the output EndDate for the last row is the exact ending date, but for earlier months it is always the last day of the month, when computing the new EndDate value in the CROSS APPLY we need to use "WHEN t.tally = DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE())) " to test for the last row.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question