Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

tsql to split a time period in different months

Posted on 2014-12-18
6
Medium Priority
?
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 60

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 60

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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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 70

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Loops Section Overview

610 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