RE: Grouping records by time interval

SSC-Forever

Points: 41690

February 28, 2013 at 9:49 am

#1592065

geert.de.vylder (2/28/2013)
Hi all,
My appologies.
I had the file with the test data ready, but forgot to attach it to the post.
Here it is.
Thank you very much for your help.
Greetz,
Geert

I had to fix your script a little, so make sure you test it before posting next time 😉

Anyway, without your expected result-set it is difficult to be sure what you want. Here's my best guess: -

SELECT d.*

FROM #Tussen_Tickets

OUTER APPLY (SELECT Interval AS StartInterval

FROM #Tijdsintervallen

WHERE CAST(Ticket_Accepted_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Accepted_DateTime AS TIME) <= eindTijdsinterval

) b

OUTER APPLY (SELECT Interval AS EndInterval

FROM #Tijdsintervallen

WHERE CAST(Ticket_Closed_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Closed_DateTime AS TIME) <= eindTijdsinterval

) c

CROSS APPLY (SELECT DISTINCT [Date], Interval, Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk,

Ticket_Accepted_DateTime, Ticket_Closed_DateTime

FROM (SELECT CAST(CAST(Ticket_Accepted_DateTime AS DATE) AS DATETIME) AS [Date], StartInterval AS Interval,

Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk,

Ticket_Accepted_DateTime, Ticket_Closed_DateTime

UNION ALL

SELECT CAST(CAST(Ticket_Accepted_DateTime AS DATE) AS DATETIME) AS [Date], EndInterval AS Interval,

Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk,

Ticket_Accepted_DateTime, Ticket_Closed_DateTime

)ua

)d;

Which produces: -

Date Interval Event_Channel DVBIntern DVBExtern Tijdsduur WrapUp OnHold Talk Ticket_Accepted_DateTime Ticket_Closed_DateTime

----------------------- -------------------------------------------------- -------------------- ----------- ----------- ----------- ---------------------- ---------------------- ---------------------- ------------------------ -----------------------

2013-01-29 00:00:00.000 12:30-12:45 295 0 0 46 0 0 46 2013-01-29 12:43:00.000 2013-01-29 12:44:00.000

2013-01-29 00:00:00.000 15:45-16:00 295 0 0 31 0 0 31 2013-01-29 15:49:00.000 2013-01-29 15:50:00.000

2013-01-31 00:00:00.000 14:30-14:45 4000 0 0 43 0 0 43 2013-01-31 14:41:00.000 2013-01-31 14:42:00.000

2013-01-31 00:00:00.000 15:00-15:15 VulIn3 0 0 30 0 0 30 2013-01-31 15:14:00.000 2013-01-31 15:14:00.000

2013-01-31 00:00:00.000 16:30-16:45 VulIn3 0 0 813 0 0 813 2013-01-31 16:39:00.000 2013-01-31 16:53:00.000

2013-01-31 00:00:00.000 16:45-17:00 VulIn3 0 0 813 0 0 813 2013-01-31 16:39:00.000 2013-01-31 16:53:00.000

2013-02-01 00:00:00.000 11:15-11:30 VulIn1 0 0 24 0 0 24 2013-02-01 11:17:00.000 2013-02-01 11:17:00.000

2013-02-01 00:00:00.000 13:45-14:00 VulIn1 1 0 79 10.052 8.348 60.6 2013-02-01 13:54:00.000 2013-02-01 13:56:00.000

2013-02-01 00:00:00.000 14:00-14:15 VulIn1 0 0 87 9.178 5.606 72.216 2013-02-01 14:01:00.000 2013-02-01 14:03:00.000

2013-02-01 00:00:00.000 14:00-14:15 293 0 0 72 0 6.776 65.224 2013-02-01 14:03:00.000 2013-02-01 14:05:00.000

2013-02-01 00:00:00.000 14:30-14:45 293 0 0 148 0 0 148 2013-02-01 14:36:00.000 2013-02-01 14:38:00.000

2013-02-01 00:00:00.000 14:45-15:00 VulIn1 0 0 56 0 0 56 2013-02-01 14:51:00.000 2013-02-01 14:52:00.000

2013-02-01 00:00:00.000 14:45-15:00 293 0 0 32 0 0 32 2013-02-01 14:52:00.000 2013-02-01 14:52:00.000

2013-02-01 00:00:00.000 14:45-15:00 VulIn3 0 0 113 0 0 113 2013-02-01 14:56:00.000 2013-02-01 14:58:00.000

2013-02-01 00:00:00.000 15:00-15:15 293 0 0 29 0 0 29 2013-02-01 15:05:00.000 2013-02-01 15:06:00.000

2013-02-01 00:00:00.000 15:00-15:15 293 0 0 61 0 0 61 2013-02-01 15:08:00.000 2013-02-01 15:09:00.000

2013-02-01 00:00:00.000 16:00-16:15 293 0 0 281 0 0 281 2013-02-01 16:12:00.000 2013-02-01 16:16:00.000

2013-02-01 00:00:00.000 16:15-16:30 293 0 0 281 0 0 281 2013-02-01 16:12:00.000 2013-02-01 16:16:00.000

2013-02-01 00:00:00.000 16:15-16:30 293 0 0 789 0 0 789 2013-02-01 16:18:00.000 2013-02-01 16:31:00.000

2013-02-01 00:00:00.000 16:30-16:45 293 0 0 789 0 0 789 2013-02-01 16:18:00.000 2013-02-01 16:31:00.000

2013-02-03 00:00:00.000 18:45-19:00 VulIn3 0 0 20 0 0 20 2013-02-03 18:48:00.000 2013-02-03 18:49:00.000

2013-02-03 00:00:00.000 18:45-19:00 4000 0 0 45 0 0 45 2013-02-03 18:49:00.000 2013-02-03 18:50:00.000

2013-02-04 00:00:00.000 09:15-09:30 4000 0 0 15 0 0 15 2013-02-04 09:16:00.000 2013-02-04 09:16:00.000

2013-02-04 00:00:00.000 09:15-09:30 4000 0 0 24 0 0 24 2013-02-04 09:19:00.000 2013-02-04 09:19:00.000

2013-02-04 00:00:00.000 09:15-09:30 4000 0 0 23 0 0 23 2013-02-04 09:22:00.000 2013-02-04 09:22:00.000

2013-02-04 00:00:00.000 09:15-09:30 293 0 0 26 0 0 26 2013-02-04 09:22:00.000 2013-02-04 09:23:00.000

2013-02-04 00:00:00.000 09:15-09:30 298 0 0 15 0 0 15 2013-02-04 09:23:00.000 2013-02-04 09:23:00.000

2013-02-04 00:00:00.000 09:15-09:30 295 0 0 38 0 0 38 2013-02-04 09:23:00.000 2013-02-04 09:24:00.000

2013-02-04 00:00:00.000 09:15-09:30 295 0 0 47 0 0 47 2013-02-04 09:24:00.000 2013-02-04 09:25:00.000

2013-02-04 00:00:00.000 09:45-10:00 293 0 0 1001 0 0 1001 2013-02-04 09:45:00.000 2013-02-04 10:02:00.000

2013-02-04 00:00:00.000 10:00-10:15 293 0 0 1001 0 0 1001 2013-02-04 09:45:00.000 2013-02-04 10:02:00.000

2013-02-04 00:00:00.000 10:00-10:15 293 0 0 246 0 0 246 2013-02-04 10:09:00.000 2013-02-04 10:13:00.000

2013-02-04 00:00:00.000 10:15-10:30 293 0 0 56 0 0 56 2013-02-04 10:21:00.000 2013-02-04 10:22:00.000

2013-02-04 00:00:00.000 10:15-10:30 293 0 0 210 0 0 210 2013-02-04 10:26:00.000 2013-02-04 10:29:00.000

2013-02-04 00:00:00.000 10:30-10:45 293 0 0 87 0 0 87 2013-02-04 10:34:00.000 2013-02-04 10:35:00.000

2013-02-06 00:00:00.000 15:15-15:30 293 0 0 332 0 0 332 2013-02-06 15:28:00.000 2013-02-06 15:34:00.000

2013-02-06 00:00:00.000 15:30-15:45 293 0 0 332 0 0 332 2013-02-06 15:28:00.000 2013-02-06 15:34:00.000

2013-02-08 00:00:00.000 15:15-15:30 VulIn1 0 0 23 0 0 23 2013-02-08 15:25:00.000 2013-02-08 15:25:00.000

2013-02-08 00:00:00.000 16:45-17:00 297 0 0 37 0 0 37 2013-02-08 16:54:00.000 2013-02-08 16:55:00.000

2013-02-18 00:00:00.000 11:30-11:45 293 0 0 19 0 0 19 2013-02-18 11:39:00.000 2013-02-18 11:39:00.000

2013-02-18 00:00:00.000 11:30-11:45 293 0 0 6 0 0 6 2013-02-18 11:42:00.000 2013-02-18 11:42:00.000

2013-02-18 00:00:00.000 11:30-11:45 293 0 0 5 0 0 5 2013-02-18 11:43:00.000 2013-02-18 11:43:00.000

2013-02-18 00:00:00.000 11:45-12:00 293 0 0 7 0 0 7 2013-02-18 11:52:00.000 2013-02-18 11:52:00.000

2013-02-18 00:00:00.000 11:45-12:00 293 0 0 7 0 0 7 2013-02-18 11:56:00.000 2013-02-18 11:56:00.000

2013-02-19 00:00:00.000 10:45-11:00 293 0 0 73 0 0 73 2013-02-19 10:53:00.000 2013-02-19 10:54:00.000

2013-02-19 00:00:00.000 10:45-11:00 VulIn1 0 0 13 0 0 13 2013-02-19 10:55:00.000 2013-02-19 10:55:00.000

2013-02-19 00:00:00.000 11:15-11:30 4000 0 0 729 0 0 729 2013-02-19 11:20:00.000 2013-02-19 11:32:00.000

2013-02-19 00:00:00.000 11:30-11:45 4000 0 0 729 0 0 729 2013-02-19 11:20:00.000 2013-02-19 11:32:00.000

2013-02-19 00:00:00.000 11:30-11:45 VulIn3 0 0 142 0 0 142 2013-02-19 11:42:00.000 2013-02-19 11:44:00.000

2013-02-19 00:00:00.000 11:45-12:00 4001 0 0 75 0 0 75 2013-02-19 11:45:00.000 2013-02-19 11:47:00.000

2013-02-19 00:00:00.000 11:45-12:00 4001 0 0 120 0 0 120 2013-02-19 11:49:00.000 2013-02-19 11:51:00.000

2013-02-19 00:00:00.000 11:45-12:00 VulIn1 0 0 207 0 0 207 2013-02-19 11:52:00.000 2013-02-19 11:56:00.000

2013-02-19 00:00:00.000 11:45-12:00 293 0 0 83 0 0 83 2013-02-19 11:56:00.000 2013-02-19 11:57:00.000

2013-02-19 00:00:00.000 11:45-12:00 298 0 0 10 0 0 10 2013-02-19 11:58:00.000 2013-02-19 11:58:00.000

2013-02-19 00:00:00.000 11:45-12:00 4000 0 0 132 0 0 132 2013-02-19 11:58:00.000 2013-02-19 12:00:00.000

2013-02-19 00:00:00.000 12:00-12:15 4000 0 0 132 0 0 132 2013-02-19 11:58:00.000 2013-02-19 12:00:00.000

2013-02-19 00:00:00.000 13:45-14:00 4000 0 0 48 0 0 48 2013-02-19 13:52:00.000 2013-02-19 13:53:00.000

2013-02-19 00:00:00.000 13:45-14:00 4001 0 1 398 5.678 0 392.322 2013-02-19 13:53:00.000 2013-02-19 14:00:00.000

2013-02-19 00:00:00.000 14:00-14:15 4001 0 1 398 5.678 0 392.322 2013-02-19 13:53:00.000 2013-02-19 14:00:00.000

2013-02-25 00:00:00.000 10:30-10:45 4001 0 0 19 0 0 19 2013-02-25 10:39:00.000 2013-02-25 10:39:00.000

2013-02-25 00:00:00.000 10:30-10:45 4001 0 0 35 0 0 35 2013-02-25 10:42:00.000 2013-02-25 10:42:00.000

2013-02-25 00:00:00.000 11:15-11:30 293 0 0 34 0 0 34 2013-02-25 11:16:00.000 2013-02-25 11:16:00.000

2013-02-25 00:00:00.000 14:30-14:45 4001 0 0 253 0 0 253 2013-02-25 14:34:00.000 2013-02-25 14:38:00.000

2013-02-25 00:00:00.000 16:30-16:45 4001 0 0 643 0 0 643 2013-02-25 16:32:00.000 2013-02-25 16:43:00.000

2013-02-25 00:00:00.000 16:30-16:45 4000 0 0 124 2.371 0 121.629 2013-02-25 16:38:00.000 2013-02-25 16:40:00.000

2013-02-25 00:00:00.000 16:30-16:45 4000 0 0 18 3.792 0 14.208 2013-02-25 16:40:00.000 2013-02-25 16:40:00.000

2013-02-25 00:00:00.000 16:30-16:45 4000 0 0 105 76.548 0 28.452 2013-02-25 16:41:00.000 2013-02-25 16:43:00.000

2013-02-25 00:00:00.000 16:30-16:45 4000 0 0 60 5.26 0 54.74 2013-02-25 16:43:00.000 2013-02-25 16:44:00.000

2013-02-25 00:00:00.000 16:30-16:45 4000 0 0 36 9.385 0 26.615 2013-02-25 16:44:00.000 2013-02-25 16:45:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 36 9.385 0 26.615 2013-02-25 16:44:00.000 2013-02-25 16:45:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 55 8.464 0 46.536 2013-02-25 16:45:00.000 2013-02-25 16:46:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 6.775 0 8.225 2013-02-25 16:46:00.000 2013-02-25 16:46:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 11.917 0 3.083 2013-02-25 16:47:00.000 2013-02-25 16:47:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 23 8.963 0 14.037 2013-02-25 16:50:00.000 2013-02-25 16:50:00.000

2013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 6.145 0 8.855 2013-02-25 16:50:00.000 2013-02-25 16:50:00.000

2013-02-26 00:00:00.000 08:45-09:00 293 0 0 81 0 0 81 2013-02-26 08:46:00.000 2013-02-26 08:47:00.000

2013-02-26 00:00:00.000 09:45-10:00 293 0 0 23 0 0 23 2013-02-26 09:48:00.000 2013-02-26 09:49:00.000

2013-02-26 00:00:00.000 09:45-10:00 294 0 0 740 0 0 740 2013-02-26 09:49:00.000 2013-02-26 10:01:00.000

2013-02-26 00:00:00.000 10:00-10:15 294 0 0 740 0 0 740 2013-02-26 09:49:00.000 2013-02-26 10:01:00.000

2013-02-26 00:00:00.000 10:45-11:00 123 0 0 130 0 0 130 2013-02-26 10:58:00.000 2013-02-26 11:00:00.000

2013-02-26 00:00:00.000 11:00-11:15 123 0 0 130 0 0 130 2013-02-26 10:58:00.000 2013-02-26 11:00:00.000

2013-02-26 00:00:00.000 11:00-11:15 293 0 0 45 0 0 45 2013-02-26 11:01:00.000 2013-02-26 11:02:00.000

2013-02-26 00:00:00.000 11:00-11:15 293 0 0 167 0 0 167 2013-02-26 11:02:00.000 2013-02-26 11:05:00.000

2013-02-26 00:00:00.000 11:00-11:15 293 0 0 364 0 0 364 2013-02-26 11:05:00.000 2013-02-26 11:11:00.000

2013-02-26 00:00:00.000 11:00-11:15 VulIn1 0 0 743 0 0 743 2013-02-26 11:14:00.000 2013-02-26 11:26:00.000

2013-02-26 00:00:00.000 11:15-11:30 VulIn1 0 0 743 0 0 743 2013-02-26 11:14:00.000 2013-02-26 11:26:00.000

2013-02-26 00:00:00.000 11:15-11:30 293 0 0 1013 0 0 1013 2013-02-26 11:26:00.000 2013-02-26 11:43:00.000

2013-02-26 00:00:00.000 11:30-11:45 293 0 0 1013 0 0 1013 2013-02-26 11:26:00.000 2013-02-26 11:43:00.000

2013-02-26 00:00:00.000 11:45-12:00 4000 0 0 26 0 0 26 2013-02-26 11:45:00.000 2013-02-26 11:45:00.000

2013-02-26 00:00:00.000 11:45-12:00 4000 0 0 20 6.99 0 13.01 2013-02-26 11:45:00.000 2013-02-26 11:46:00.000

2013-02-26 00:00:00.000 12:00-12:15 4000 0 0 97 85.372 0 11.628 2013-02-26 12:13:00.000 2013-02-26 12:14:00.000

2013-02-26 00:00:00.000 12:15-12:30 4000 0 0 113 71.516 0 41.484 2013-02-26 12:19:00.000 2013-02-26 12:21:00.000

2013-02-26 00:00:00.000 13:30-13:45 4000 0 0 113 4.665 0 108.335 2013-02-26 13:39:00.000 2013-02-26 13:41:00.000

2013-02-26 00:00:00.000 13:30-13:45 4001 0 0 12 7.504 0 4.496 2013-02-26 13:41:00.000 2013-02-26 13:41:00.000

2013-02-26 00:00:00.000 13:30-13:45 4001 0 0 59 46.303 0 12.697 2013-02-26 13:41:00.000 2013-02-26 13:42:00.000

2013-02-26 00:00:00.000 13:45-14:00 VulIn1 0 0 28 0 0 28 2013-02-26 13:48:00.000 2013-02-26 13:49:00.000

2013-02-26 00:00:00.000 14:00-14:15 4000 0 0 74 0 0 74 2013-02-26 14:02:00.000 2013-02-26 14:03:00.000

2013-02-26 00:00:00.000 14:00-14:15 123 0 0 31 0 0 31 2013-02-26 14:03:00.000 2013-02-26 14:04:00.000

2013-02-26 00:00:00.000 14:00-14:15 298 0 0 23 0 0 23 2013-02-26 14:04:00.000 2013-02-26 14:04:00.000

2013-02-26 00:00:00.000 14:30-14:45 4000 0 0 108 0 0 108 2013-02-26 14:30:00.000 2013-02-26 14:32:00.000

2013-02-26 00:00:00.000 14:45-15:00 4000 0 0 2828 0 0 2828 2013-02-26 14:50:00.000 2013-02-26 15:37:00.000

2013-02-26 00:00:00.000 15:30-15:45 4000 0 0 2828 0 0 2828 2013-02-26 14:50:00.000 2013-02-26 15:37:00.000

2013-02-26 00:00:00.000 16:00-16:15 293 0 0 1363 0 0 1363 2013-02-26 16:02:00.000 2013-02-26 16:25:00.000

2013-02-26 00:00:00.000 16:15-16:30 293 0 0 1363 0 0 1363 2013-02-26 16:02:00.000 2013-02-26 16:25:00.000

2013-02-26 00:00:00.000 16:00-16:15 298 0 0 9 0 0 9 2013-02-26 16:07:00.000 2013-02-26 16:07:00.000

2013-02-27 00:00:00.000 09:00-09:15 4000 0 0 25 0 0 25 2013-02-27 09:13:00.000 2013-02-27 09:14:00.000

2013-02-27 00:00:00.000 09:45-10:00 293 0 0 98 0 0 98 2013-02-27 09:52:00.000 2013-02-27 09:53:00.000

2013-02-27 00:00:00.000 10:15-10:30 293 0 0 36 0 0 36 2013-02-27 10:21:00.000 2013-02-27 10:21:00.000

2013-02-27 00:00:00.000 10:30-10:45 293 0 0 14 0 0 14 2013-02-27 10:38:00.000 2013-02-27 10:39:00.000

2013-02-27 00:00:00.000 10:30-10:45 293 0 0 224 0 0 224 2013-02-27 10:40:00.000 2013-02-27 10:44:00.000

2013-02-27 00:00:00.000 10:45-11:00 293 0 0 39 0 0 39 2013-02-27 10:47:00.000 2013-02-27 10:48:00.000

2013-02-27 00:00:00.000 10:45-11:00 293 0 0 51 0 0 51 2013-02-27 10:52:00.000 2013-02-27 10:52:00.000

2013-02-27 00:00:00.000 11:45-12:00 4001 0 1 51 27.416 0 23.584 2013-02-27 11:50:00.000 2013-02-27 11:51:00.000

2013-02-27 00:00:00.000 11:45-12:00 4001 0 0 27 3.124 0 23.876 2013-02-27 11:52:00.000 2013-02-27 11:52:00.000

2013-02-27 00:00:00.000 11:45-12:00 4001 0 1 19 4.893 0 14.107 2013-02-27 11:52:00.000 2013-02-27 11:52:00.000

2013-02-27 00:00:00.000 11:45-12:00 4001 0 0 4055 4044.14 0 10.857 2013-02-27 11:53:00.000 2013-02-27 13:00:00.000

2013-02-27 00:00:00.000 13:00-13:15 4001 0 0 4055 4044.14 0 10.857 2013-02-27 11:53:00.000 2013-02-27 13:00:00.000

2013-02-27 00:00:00.000 13:00-13:15 4001 0 0 135 5.538 0 129.462 2013-02-27 13:07:00.000 2013-02-27 13:09:00.000

2013-02-27 00:00:00.000 13:15-13:30 4001 0 0 58 8.149 35.52 14.331 2013-02-27 13:21:00.000 2013-02-27 13:22:00.000

2013-02-27 00:00:00.000 13:15-13:30 4001 0 0 21 6.392 14.527 0.081 2013-02-27 13:22:00.000 2013-02-27 13:22:00.000

2013-02-27 00:00:00.000 13:30-13:45 4001 0 0 131 0.771 0 130.229 2013-02-27 13:30:00.000 2013-02-27 13:32:00.000

2013-02-27 00:00:00.000 13:30-13:45 4001 0 0 225 198.061 0 26.939 2013-02-27 13:32:00.000 2013-02-27 13:36:00.000

2013-02-27 00:00:00.000 13:30-13:45 293 0 0 106 0 0 106 2013-02-27 13:43:00.000 2013-02-27 13:44:00.000

2013-02-27 00:00:00.000 13:45-14:00 4001 0 0 73 0 0 73 2013-02-27 13:48:00.000 2013-02-27 13:49:00.000

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/