## Get difference between dates year by year

Author
Message
lein
Right there with Babe

Group: General Forum Members
Points: 772 Visits: 153
 Get difference between dates year by year I need to get the days difference between Start date and End Date year by year for the past five years: My date has always 5 years max interval:Sample data Start Date End Date 12/1/2013 1/10/2017 11/1/2013 11/10/2016 1/1/2013 1/31/2014 And here is my desired output (sample data for the past five years) Start Date End Date Number of days per year 2013 2014 2015 2016 2017 12/1/2013 1/10/2017 30 365 365 366 10 11/1/2013 11/10/2016 60 365 365 315 1/1/2013 1/31/2014 364 31 For this year i have 2013-2017for next year it will be 2014-2018 etc... Needs to be dynamic Getting the days difference between two dates is simple here is what I have right now: select start_date, end_date, DATEDIFF(DAY, start_date, end_date) as difference_in_days from table1

John Mitchell-245523
SSC Guru

Group: General Forum Members
Points: 137128 Visits: 19546
Here's one way of doing it:

`WITH Dates (StartDate, EndDate) AS (    SELECT '20131201', '20170110' UNION ALL    SELECT '20131101', '20161110' UNION ALL    SELECT '20130101', '20140131'    )SELECT     StartDate,    EndDate,    CASE        WHEN StartDate >= '20131231' THEN 0        WHEN EndDate < '20130101' THEN 0        WHEN StartDate >= '20130101' AND EndDate < '20131231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,StartDate,'20131231')        WHEN StartDate < '20130101' AND EndDate <= '20131231' THEN DATEDIFF(day,'20130101',EndDate) + 1        WHEN StartDate < '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,'20130101','20131231') + 1     END AS Daysin2013,    CASE        WHEN StartDate >= '20141231' THEN 0        WHEN EndDate < '20140101' THEN 0        WHEN StartDate >= '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,StartDate,'20141231')        WHEN StartDate < '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,'20140101',EndDate) + 1        WHEN StartDate < '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,'20140101','20141231') + 1     END AS Daysin2014,    CASE        WHEN StartDate >= '20151231' THEN 0        WHEN EndDate < '20150101' THEN 0        WHEN StartDate >= '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,StartDate,'20151231')        WHEN StartDate < '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,'20150101',EndDate) + 1        WHEN StartDate < '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,'20150101','20151231') + 1     END AS Daysin2015,    CASE        WHEN StartDate >= '20161231' THEN 0        WHEN EndDate < '20160101' THEN 0        WHEN StartDate >= '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,StartDate,'20161231')        WHEN StartDate < '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,'20160101',EndDate) + 1        WHEN StartDate < '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,'20160101','20161231') + 1     END AS Daysin2016,    CASE        WHEN StartDate >= '20171231' THEN 0        WHEN EndDate < '20170101' THEN 0        WHEN StartDate >= '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,StartDate,'20171231')        WHEN StartDate < '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,'20170101',EndDate) + 1        WHEN StartDate < '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,'20170101','20171231') + 1     END AS Daysin2017FROM Dates d;`

John

ScottPletcher
SSC Guru

Group: General Forum Members
Points: 88745 Visits: 9915
Edit: Sorry about the formatting. The site automatically "adjusts" (corrupts!) multiple spaces into fewer spaces.

`SELECT start_date, end_date,   CASE WHEN start_date >= '20140101' OR end_date < '20130101' THEN 0   ELSE DATEDIFF(DAY, CASE WHEN start_date < '20130101' THEN '20130101' ELSE start_date END,      CASE WHEN end_date >= '20140101' THEN '20131231' ELSE end_date END) + 1 END AS [2013],  CASE WHEN start_date >= '20150101' OR end_date < '20140101' THEN 0   ELSE DATEDIFF(DAY, CASE WHEN start_date < '20140101' THEN '20140101' ELSE start_date END,      CASE WHEN end_date >= '20150101' THEN '20141231' ELSE end_date END) + 1 END AS [2014],  CASE WHEN start_date >= '20160101' OR end_date < '20150101' THEN 0   ELSE DATEDIFF(DAY, CASE WHEN start_date < '20150101' THEN '20150101' ELSE start_date END,      CASE WHEN end_date >= '20160101' THEN '20151231' ELSE end_date END) + 1 END AS [2015],  CASE WHEN start_date >= '20170101' OR end_date < '20160101' THEN 0   ELSE DATEDIFF(DAY, CASE WHEN start_date < '20160101' THEN '20160101' ELSE start_date END,      CASE WHEN end_date >= '20170101' THEN '20161231' ELSE end_date END) + 1 END AS [2016],  CASE WHEN start_date >= '20180101' OR end_date < '20170101' THEN 0   ELSE DATEDIFF(DAY, CASE WHEN start_date < '20170101' THEN '20170101' ELSE start_date END,      CASE WHEN end_date >= '20180101' THEN '20171231' ELSE end_date END) + 1 END AS [2017]FROM ( VALUES  (CAST('12/1/2013' AS date), CAST('1/10/2017' AS date)),   ('11/1/2013', '11/10/2016'),  ('1/1/2013','1/31/2014') ) AS test_data(start_date, end_date)`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
lein
Right there with Babe

Group: General Forum Members
Points: 772 Visits: 153
John Mitchell-245523 - Thursday, February 8, 2018 9:23 AM
Here's one way of doing it:

`WITH Dates (StartDate, EndDate) AS (    SELECT '20131201', '20170110' UNION ALL    SELECT '20131101', '20161110' UNION ALL    SELECT '20130101', '20140131'    )SELECT     StartDate,    EndDate,    CASE        WHEN StartDate >= '20131231' THEN 0        WHEN EndDate < '20130101' THEN 0        WHEN StartDate >= '20130101' AND EndDate < '20131231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,StartDate,'20131231')        WHEN StartDate < '20130101' AND EndDate <= '20131231' THEN DATEDIFF(day,'20130101',EndDate) + 1        WHEN StartDate < '20130101' AND EndDate > '20131231' THEN DATEDIFF(day,'20130101','20131231') + 1     END AS Daysin2013,    CASE        WHEN StartDate >= '20141231' THEN 0        WHEN EndDate < '20140101' THEN 0        WHEN StartDate >= '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,StartDate,'20141231')        WHEN StartDate < '20140101' AND EndDate <= '20141231' THEN DATEDIFF(day,'20140101',EndDate) + 1        WHEN StartDate < '20140101' AND EndDate > '20141231' THEN DATEDIFF(day,'20140101','20141231') + 1     END AS Daysin2014,    CASE        WHEN StartDate >= '20151231' THEN 0        WHEN EndDate < '20150101' THEN 0        WHEN StartDate >= '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,StartDate,'20151231')        WHEN StartDate < '20150101' AND EndDate <= '20151231' THEN DATEDIFF(day,'20150101',EndDate) + 1        WHEN StartDate < '20150101' AND EndDate > '20151231' THEN DATEDIFF(day,'20150101','20151231') + 1     END AS Daysin2015,    CASE        WHEN StartDate >= '20161231' THEN 0        WHEN EndDate < '20160101' THEN 0        WHEN StartDate >= '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,StartDate,'20161231')        WHEN StartDate < '20160101' AND EndDate <= '20161231' THEN DATEDIFF(day,'20160101',EndDate) + 1        WHEN StartDate < '20160101' AND EndDate > '20161231' THEN DATEDIFF(day,'20160101','20161231') + 1     END AS Daysin2016,    CASE        WHEN StartDate >= '20171231' THEN 0        WHEN EndDate < '20170101' THEN 0        WHEN StartDate >= '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,StartDate,EndDate)        WHEN StartDate >= '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,StartDate,'20171231')        WHEN StartDate < '20170101' AND EndDate <= '20171231' THEN DATEDIFF(day,'20170101',EndDate) + 1        WHEN StartDate < '20170101' AND EndDate > '20171231' THEN DATEDIFF(day,'20170101','20171231') + 1     END AS Daysin2017FROM Dates d;`

John

This will work but i forgot to tell you that my years are dynamic. So basically im extracting all data for the past 5 years of the current date. Thank you for your response.

lein
Right there with Babe

Group: General Forum Members
Points: 772 Visits: 153
This is what i came up with John's suggestion. Will show the actual year in Report Builder.
If anyone has a better solution than this ginormous case statement let me know Thank you in advance!

SELECT
StartDate
, EndDate
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 5, 0)) + 1
END AS DaysinLast5Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 4 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 4, 0)) + 1
END AS DaysinLast4Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 3 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 3, 0)) + 1
END AS DaysinLast3Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 2 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 2, 0)) + 1
END AS DaysinLast2Years
, CASE
WHEN StartDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN 0
WHEN EndDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') THEN 0
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,StartDate,EndDate)+1
WHEN StartDate >= DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,StartDate,DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0))+1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate <= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01'),EndDate) + 1
WHEN StartDate < DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01') AND EndDate > DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) THEN DATEDIFF(day,DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 1 , '1900-01-01'),DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0)) + 1
END AS DaysinLastYear
FROM Table1
where StartDate >= (DATEADD(YEAR, DATEDIFF(YEAR, '1900-01-01', GETDATE()) - 5 , '1900-01-01')) and (EndDate >= StartDate or EndDate is null)
Bert-701015
Ten Centuries

Group: General Forum Members
Points: 1305 Visits: 1349
I'm not fond of the static years in this code. Perhaps it could be converted to a dynamic pivot
`declare @t table (        startdate date,    enddate date)declare @n table (    Nbr tinyint);insert @n values(0),(1),(2),(3),(4);insert @t values    ('12/1/2013',    '1/10/2017'),    ('11/1/2013',    '11/10/2016'),                        ('1/1/2013',    '1/31/2014');select StartDate, Enddate, [2013] as [2013], [2014] as [2014], [2015] as [2015], [2016] as [2016], [2017] as [2017]from (    select         StartDate,        EndDate,        DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS [Year],        DATEDIFF(            DAY,            case DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate)))                when datepart(year,startdate) then t.startdate                else CAST(DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS CHAR(4)) + '-01-01'            end,            case DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate)))                when datepart(year,enddate) then t.enddate                else CAST(DATEPART(YEAR, (DATEADD(YEAR, n.Nbr, t.StartDate))) AS CHAR(4)) + '-12-31'            end        ) AS DayCnt    from @t t    inner join @n n on n.Nbr < datepart(year,t.enddate) - datepart(year,t.startdate) + 1) ppivot (    min(DayCnt)    for [Year] in ([2013], [2014], [2015], [2016], [2017])) as pvtorder by startdate, enddate`

drew.allen
SSC Guru

Group: General Forum Members
Points: 68856 Visits: 18321
I don't know that it's faster, but it's certainly simpler.

`;WITH Dates (ID, StartDate, EndDate) AS (  SELECT 1, '20131201', '20170110' UNION ALL  SELECT 2, '20131101', '20161110' UNION ALL  SELECT 3, '20130101', '20140131'  )SELECT    ID,    MAX(StartDate) AS StartDate,    MAX(EndDate) AS EndDate,    MAX(CASE WHEN n = 5 THEN DaysInPeriod END) AS DaysInYear1,    MAX(CASE WHEN n = 4 THEN DaysInPeriod END) AS DaysInYear2,    MAX(CASE WHEN n = 3 THEN DaysInPeriod END) AS DaysInYear3,    MAX(CASE WHEN n = 2 THEN DaysInPeriod END) AS DaysInYear4,    MAX(CASE WHEN n = 1 THEN DaysInPeriod END) AS DaysInYear5FROM DatesCROSS APPLY(    SELECT n, PeriodDate, DATEDIFF(DAY, LAG(PeriodDate, 1, StartDate) OVER(PARTITION BY ID ORDER BY PeriodDate), PeriodDate) AS DaysInPeriod    FROM    (        SELECT n, DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) - n, -1) AS PeriodDate        FROM ( VALUES(1), (2), (3), (4), (5) ) t(n)        UNION        SELECT DATEDIFF(YEAR, EndDate, GETDATE()), EndDate    ) v    WHERE PeriodDate > StartDate         AND PeriodDate <= EndDate) vGROUP BY ID;`

J. Drew Allen
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.