I currently have an issue whereby I am doing DATEDIFF in minutes between a start date and end date, however I when this date goes over into a new month I need the figures to be separate for each month.
Please see example data (both Text and Image view);
SELECT [BookingNum]
,[StartDate]
,[EndDate]
,[Location]
,DATEPART(m,startdate) AS [Month]
,DATEDIFF(MINUTE,StartDate,EndDate) AS [Minutes]
FROM [Test].[dbo].[Booking]
BookingNum StartDate EndDate Location Month Minutes
1 2019-02-05 12:54:00.000 2019-02-08 15:00:00.000 Area 1 2 4446
2 2019-05-02 10:41:00.000 2019-05-10 12:39:00.000 Area 2 5 11638
3 2019-06-01 10:30:00.000 2019-06-04 09:25:00.000 Area 3 6 4255
4 2019-02-02 09:41:00.000 2019-04-20 11:54:00.000 Area 1 2 111013
5 2019-03-29 19:09:00.000 2019-04-02 10:41:00.000 Area 3 3 5252
For rows 4 & 5 there would need to be additional rows as they go across multiple months.
Example for the data in row 4, I would want to see;
StartDate EndDate Location Month Minutes
2019-02-02 09:41:00.000 2019-02-28 23:59:00.000 Area 1 2 38298
2019-03-01 00:00:00.000 2019-03-31 23:59:00.000 Area 1 3 44639
2019-04-01 00:00:00.000 2019-04-20 23:59:00.000 Area 1 4 28074
This would then give me the total minutes for that month only between the start and end date.
Any help much appreciated.
This can be achieved using recursive CTE as follows. This calculates multiple months between startdate and enddate. Fiddle: http://sqlfiddle.com/#!18/26568/4
create table #temp(
BookingNum int,
StartDate datetime,
EndDate datetime,
Location varchar(25),
)
insert into #temp
values(1,'2019-02-05 12:54:00','2019-02-08 15:00:00','Area 1'),
(2,'2019-05-02 10:41:00','2019-05-10 12:39:00','Area 2'),
(3,'2019-06-01 10:30:00','2019-06-04 09:25:00','Area 3'),
(4,'2019-02-02 09:41:00','2019-05-20 11:54:00','Area 1'),
(5,'2019-03-29 19:09:00','2019-04-02 10:41:00','Area 3')
;WITH cte AS
(
SELECT BookingNum,
StartDate,
CASE
WHEN DATEPART(m, EndDate) > DATEPART(m, startdate)
THEN DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, startdate) + 1, 0))
ELSE EndDate
END AS EndDate,
Location,
DATEPART(m, EndDate) - DATEPART(m, startdate) AS MonthDiff
FROM #temp
UNION ALL
SELECT cte.BookingNum,
CASE
WHEN cte.MonthDiff > 0
THEN DATEADD(month, DATEDIFF(month, 0, DATEADD(month, 1, cte.StartDate)), 0)
ELSE cte.StartDate
END AS startDate,
CASE
WHEN cte.MonthDiff > 0 AND DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, 1, cte.StartDate)) + 1, 0)) < t.EndDate
THEN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, DATEADD(month, 1, cte.StartDate)) + 1, 0))
ELSE t.EndDate
END AS EndDate,
cte.Location,
(cte.MonthDiff - 1) MonthDiff
FROM cte
INNER JOIN #temp t ON cte.BookingNum = t.BookingNum
WHERE cte.MonthDiff > 0
)
SELECT BookingNum,
StartDate,
EndDate,
Location,
DATEPART(m, startdate) AS month,
DATEDIFF(minute, startdate, enddate) AS minutes
FROM cte
ORDER BY 1;
drop table #temp
Result:
BookingNum StartDate EndDate Location month minutes
----------- ----------------------- ----------------------- ------------------------- ----------- -----------
1 2019-02-05 12:54:00.000 2019-02-08 15:00:00.000 Area 1 2 4446
2 2019-05-02 10:41:00.000 2019-05-10 12:39:00.000 Area 2 5 11638
3 2019-06-01 10:30:00.000 2019-06-04 09:25:00.000 Area 3 6 4255
4 2019-02-02 09:41:00.000 2019-02-28 23:59:59.000 Area 1 2 38298
4 2019-03-01 00:00:00.000 2019-03-31 00:00:00.000 Area 1 3 43200
4 2019-04-01 00:00:00.000 2019-04-30 00:00:00.000 Area 1 4 41760
4 2019-05-01 00:00:00.000 2019-05-20 11:54:00.000 Area 1 5 28074
5 2019-03-29 19:09:00.000 2019-03-31 23:59:59.000 Area 3 3 3170
5 2019-04-01 00:00:00.000 2019-04-02 10:41:00.000 Area 3 4 2081