Solved

tsql to split a time period in different months

Posted on 2014-12-18
6
320 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 69

Accepted Solution

by:
ScottPletcher 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:ScottPletcher
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now