June 14, 2019 at 8:22 am
I have a table that contains records of task’s that have a start and end time.
I need to create a query that will count how many of these tasks fall within a time frame split by 30 minute intervals.
So for example if I had the following records
ID| Start | End |
1 | 2018-07-17 16:14:10.000 | 2018-07-17 16:14:14.000 |
2 | 2018-07-17 16:49:11.000 | 2018-07-17 16:52:32.000 |
3 | 2018-07-17 16:44:47.000 | 2018-07-17 17:05:53.000 |
4 | 2018-07-17 16:41:21.000 | 2018-07-17 17:05:52.000 |
5 | 2018-07-17 16:52:27.000 | 2018-07-17 17:17:58.000 |
With the data shown in the table above the query should show the following.
Date | Interval | Count
2018-07-17 | 16:00 | 1
2018-07-17 | 16:30 | 4
2018-07-17 | 17:00 | 3
I have attached a visual example of how it will count these durations.
Thank you in advance for any help.
June 14, 2019 at 8:52 am
First, we need some sample data
CREATE TABLE #Tasks (
ID int NOT NULL
, [Start] datetime NOT NULL
, [End] datetime NOT NULL
);
INSERT INTO #Tasks ( ID, [Start], [End] )
VALUES ( 1 , '2018-07-17 16:14:10.000', '2018-07-17 16:14:14.000' )
, ( 2 , '2018-07-17 16:49:11.000', '2018-07-17 16:52:32.000' )
, ( 3 , '2018-07-17 16:44:47.000', '2018-07-17 17:05:53.000' )
, ( 4 , '2018-07-17 16:41:21.000', '2018-07-17 17:05:52.000' )
, ( 5 , '2018-07-17 16:52:27.000', '2018-07-17 17:17:58.000' );
Now, the following code should get you the requested breakdown
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(48) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
, IntervalTimes(RangeStart, RangeEnd) AS (SELECT RangeStart = CAST(DATEADD(minute, (NM.N -1) *30, 0) AS time(0))
, RangeEnd = CAST(DATEADD(minute, (NM.N) *30, 0) AS time(0))
FROM NUMS AS NM)
, BaseData AS (
SELECT tk.ID, tk.[Start], tk.[End]
, [Date] = CAST(tk.[Start] AS date)
, it.RangeStart
FROM #Tasks AS tk
INNER JOIN IntervalTimes AS it
ON CAST(tk.[Start] AS time(0)) < it.RangeEnd
AND CAST(tk.[End] AS time(0)) >= it.RangeStart
)
SELECT d.[Date], Interval = CONVERT(varchar(5), d.RangeStart, 108), [Count] = COUNT(*)
FROM BaseData AS d
GROUP BY d.[Date], d.RangeStart
ORDER BY d.[Date], d.RangeStart;
June 14, 2019 at 9:20 am
This was removed by the editor as SPAM
June 14, 2019 at 9:23 am
Hi DesNorton,
Thank you so much for this! It's a massive help and works when creating the task temp table.
I am not able to get the script working with the amendments I have made. The start and end times are actually stored as varchar so I am converting them like this - CONVERT(DATETIME, [End], 103).
The error I am receiving is -
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP(48) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
, IntervalTimes(RangeStart, RangeEnd) AS (SELECT RangeStart = CAST(DATEADD(minute, (NM.N -1) *30, 0) AS time(0))
, RangeEnd = CAST(DATEADD(minute, (NM.N) *30, 0) AS time(0))
FROM NUMS AS NM)
, BaseData AS (
SELECT tk.[Name], CONVERT(DATETIME, tk.[Start], 103) AS 'TStart', CONVERT(DATETIME, [End], 103) AS 'TEnd'
, [Date] = CAST(CONVERT(DATETIME, tk.[Start], 103) AS date)
, it.RangeStart
FROM [dbo].[tasks] AS tk
INNER JOIN IntervalTimes AS it
ON CAST(tk.[Start] AS time(0)) < it.RangeEnd
AND CAST(tk.[End] AS time(0)) >= it.RangeStart
)
SELECT d.[Date], Interval = CONVERT(varchar(5), d.RangeStart, 108), [Count] = COUNT(*)
FROM BaseData AS d
GROUP BY d.[Date], d.RangeStart
ORDER BY d.[Date], d.RangeStart;
Thank again,
P
June 14, 2019 at 9:27 am
Hi,
Sorry I've worked it out.
ON CAST(CONVERT(DATETIME, tk.[Start], 103) AS time(0)) < it.RangeEnd
AND CAST(CONVERT(DATETIME, tk.[End], 103) AS time(0)) >= it.RangeStart
Thank you so much!
June 14, 2019 at 11:48 am
Hi DesNorton, Thank you so much for this! It's a massive help and works when creating the task temp table. I am not able to get the script working with the amendments I have made. The start and end times are actually stored as varchar so I am converting them like this - CONVERT(DATETIME, [End], 103). The error I am receiving is - Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
Thank again, P
I'm glad you managed to get it working.
The highlighted part of your response is always an issue. If you have any influence, get the data types changed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply