January 22, 2015 at 11:49 am
Hello, I am new to the forum. I hope there is someone who has run into this before. I have a table with the following columns employeeSessionID, OpDate, OpHour, sessionStartTime, sessionCloseTime. I need to see how many users remain active per hour. I can calculate how many logged in per hour, but I am stumped on how to count how many are active per hour. I have a single table that stores login data. I have created a query that pulls out the only the data needed from the table into a temp table using this query. Also note it is possible that the sessionCloseTime is null if the device has not been logged out this would need to be counted a active.
TABLE NAME #empSessionLog Contains the time stamp data OpDate, sessionStartTime and sessionCloseTime.
OpDatesessionStartTimesessionCloseTime
2015-01-202015-01-20 14:32:59.1302015-01-20 14:33:14.6299166
2015-01-202015-01-20 06:58:33.7302015-01-20 15:27:16.9133442
2015-01-202015-01-20 09:56:22.8402015-01-20 17:56:29.7555853
2015-01-202015-01-20 05:59:18.6132015-01-20 14:05:19.0426707
2015-01-202015-01-20 06:03:45.9702015-01-20 14:17:34.9328702
2015-01-202015-01-20 12:37:39.5972015-01-20 12:37:55.7542296
2015-01-202015-01-20 13:28:46.9802015-01-20 21:44:29.7902115
2015-01-202015-01-20 04:52:59.4432015-01-20 12:45:04.1644632
2015-01-202015-01-20 05:59:29.2272015-01-20 14:08:14.9452666
2015-01-202015-01-20 06:16:13.9432015-01-20 13:55:09.7511570
2015-01-202015-01-20 07:02:33.4332015-01-20 13:54:29.7691216
2015-01-202015-01-20 12:56:14.4372015-01-20 21:47:32.2838659
2015-01-202015-01-20 13:56:17.9732015-01-20 21:36:04.6284206
2015-01-202015-01-20 14:12:55.6202015-01-20 21:37:46.8277100
2015-01-212015-01-20 19:00:10.3702015-01-20 19:01:18.5469985
2015-01-202015-01-20 06:05:14.2902015-01-20 14:26:38.7769319
2015-01-202015-01-20 07:54:20.7032015-01-20 14:58:53.4825405
2015-01-202015-01-20 10:37:50.4102015-01-20 21:06:19.5611850
2015-01-202015-01-20 10:43:14.4372015-01-20 19:20:27.1715458
2015-01-202015-01-20 10:57:24.6002015-01-20 17:07:39.9467432
2015-01-202015-01-20 11:42:34.9372015-01-20 11:43:11.1604422
2015-01-202015-01-20 13:06:42.8772015-01-20 14:05:20.2009713
2015-01-202015-01-20 13:38:31.7902015-01-20 21:54:53.7340106
2015-01-202015-01-20 13:59:46.2272015-01-20 21:26:51.6078367
2015-01-202015-01-20 14:59:26.8972015-01-20 14:59:39.4799102
2015-01-202015-01-20 16:12:10.8772015-01-20 16:12:23.8861160
2015-01-212015-01-20 20:01:56.2632015-01-20 20:02:16.8894884
2015-01-212015-01-20 21:39:15.5572015-01-21 06:01:43.1597490
2015-01-202015-01-20 13:44:21.8402015-01-20 21:23:34.8060943
2015-01-212015-01-20 21:30:40.4972015-01-21 08:42:06.9555833
2015-01-202015-01-20 06:04:02.7772015-01-20 14:10:13.8869506
2015-01-202015-01-20 08:06:31.4632015-01-20 08:06:53.6402892
2015-01-202015-01-20 14:49:58.0472015-01-20 21:48:11.5114277
2015-01-212015-01-20 19:31:48.8972015-01-20 19:32:07.2625646
2015-01-202015-01-20 05:44:33.5932015-01-20 21:36:26.3447710
2015-01-202015-01-20 06:55:43.9702015-01-20 15:00:32.9154499
2015-01-202015-01-20 06:58:56.9672015-01-20 15:22:41.4673167
2015-01-202015-01-20 07:07:43.8332015-01-20 13:57:25.9191146
2015-01-202015-01-20 07:17:18.2702015-01-20 13:31:14.2577403
2015-01-202015-01-20 09:57:17.4072015-01-20 17:54:02.5617463
2015-01-202015-01-20 12:56:36.1032015-01-20 20:56:28.3974009
2015-01-202015-01-20 13:26:53.7002015-01-20 21:47:04.8738773
2015-01-202015-01-20 06:57:41.3102015-01-20 14:57:43.9601427
2015-01-202015-01-20 07:00:50.3872015-01-20 15:03:41.5787814
2015-01-202015-01-20 07:01:17.2802015-01-20 11:55:54.6993909
2015-01-202015-01-20 07:03:49.2872015-01-20 15:47:41.8889618
2015-01-202015-01-20 10:06:23.7602015-01-20 15:20:45.3993880
2015-01-202015-01-20 10:57:21.9402015-01-20 19:34:25.1427994
2015-01-202015-01-20 12:55:23.3972015-01-20 20:37:22.7280870
2015-01-202015-01-20 13:56:25.7372015-01-20 21:24:40.6800604
2015-01-202015-01-20 16:11:44.1532015-01-20 16:11:58.2644405
2015-01-202015-01-20 17:09:10.5272015-01-20 17:09:42.2642180
2015-01-202015-01-20 18:26:57.9372015-01-20 18:27:18.8651814
2015-01-212015-01-20 20:25:08.3672015-01-20 20:26:53.4870852
2015-01-202015-01-20 14:14:18.9472015-01-20 21:14:39.5385291
2015-01-202015-01-20 08:43:30.9672015-01-20 17:22:39.7275009
2015-01-202015-01-20 13:27:51.6002015-01-20 21:37:31.0845613
2015-01-202015-01-20 11:52:10.5202015-01-20 11:52:31.3193575
2015-01-202015-01-20 13:55:13.6532015-01-20 21:42:16.3716312
2015-01-202015-01-20 15:27:24.2402015-01-20 21:15:55.7206423
2015-01-212015-01-20 21:29:02.0132015-01-21 06:13:54.3112466
2015-01-202015-01-20 05:37:20.6572015-01-20 14:02:59.0021339
2015-01-202015-01-20 05:59:02.2272015-01-20 14:51:08.9077080
2015-01-202015-01-20 06:02:41.4772015-01-20 14:06:42.3786345
2015-01-202015-01-20 06:06:58.3072015-01-20 14:12:42.5994001
2015-01-202015-01-20 06:19:32.8232015-01-20 11:47:49.6103998
2015-01-202015-01-20 14:03:43.7472015-01-20 21:11:56.3315989
2015-01-202015-01-20 14:05:17.8532015-01-20 21:44:57.8803795
2015-01-202015-01-20 14:13:44.3302015-01-20 21:34:06.3004142
2015-01-202015-01-20 05:36:49.5402015-01-20 13:38:58.0820089
2015-01-202015-01-20 05:58:37.7972015-01-20 14:20:32.0060283
2015-01-202015-01-20 06:58:09.1702015-01-20 14:56:55.4300087
2015-01-202015-01-20 06:58:35.3572015-01-20 17:06:23.5524613
2015-01-202015-01-20 10:58:17.3802015-01-20 19:39:53.3562629
2015-01-202015-01-20 11:07:59.5132015-01-20 20:01:38.7771990
2015-01-202015-01-20 11:42:38.6602015-01-20 14:08:18.1047026
2015-01-202015-01-20 11:46:56.9932015-01-20 19:43:32.5519692
2015-01-202015-01-20 13:30:17.9472015-01-20 21:47:14.4841045
2015-01-202015-01-20 14:04:38.6602015-01-20 21:01:00.9489002
2015-01-202015-01-20 17:24:50.3102015-01-20 17:25:07.2505869
2015-01-212015-01-20 19:50:36.9472015-01-20 19:50:45.2724562
2015-01-212015-01-20 21:26:01.7172015-01-20 21:26:27.3945685
2015-01-202015-01-20 14:02:20.3072015-01-20 21:26:10.4938165
2015-01-212015-01-20 20:41:23.3102015-01-20 21:24:48.2849439
2015-01-202015-01-20 07:02:59.5802015-01-20 15:03:09.4201222
2015-01-202015-01-20 12:33:22.6372015-01-20 12:33:41.6719856
2015-01-202015-01-20 13:36:55.9202015-01-20 21:44:03.1902854
2015-01-202015-01-20 13:56:35.3572015-01-20 21:41:12.1944898
2015-01-202015-01-20 05:40:09.9102015-01-20 11:49:40.0552588
2015-01-202015-01-20 09:03:07.6332015-01-20 16:32:02.2312852
2015-01-202015-01-20 11:52:25.3472015-01-20 19:29:38.2940908
2015-01-202015-01-20 13:29:37.6102015-01-20 21:50:00.3525520
2015-01-202015-01-20 14:10:34.1372015-01-20 21:50:39.9676604
2015-01-212015-01-20 21:30:10.3332015-01-21 08:02:04.5393614
2015-01-212015-01-20 21:33:08.9832015-01-21 08:01:40.3326150
2015-01-212015-01-20 21:54:49.9672015-01-20 21:55:15.5970207
2015-01-202015-01-20 06:08:04.5702015-01-20 14:20:54.2859274
2015-01-202015-01-20 06:10:37.8902015-01-20 11:41:42.2219588
2015-01-202015-01-20 10:57:40.8632015-01-20 19:24:13.7005142
2015-01-202015-01-20 11:02:09.1002015-01-20 20:55:54.2942428
2015-01-202015-01-20 11:38:20.4802015-01-20 19:12:26.6052552
2015-01-202015-01-20 12:29:56.5232015-01-20 19:28:31.8472082
2015-01-202015-01-20 12:31:29.6972015-01-20 12:31:44.1097679
2015-01-202015-01-20 13:27:54.4302015-01-20 21:52:53.5069305
2015-01-202015-01-20 14:00:07.6472015-01-20 21:57:33.2832468
2015-01-202015-01-20 14:01:39.9072015-01-20 21:29:07.3326878
2015-01-202015-01-20 14:03:06.0572015-01-20 21:18:21.9986703
2015-01-202015-01-20 14:58:59.0732015-01-20 14:59:12.7897409
2015-01-202015-01-20 17:08:45.3172015-01-20 17:08:57.1577934
I can see how many sessions logged in per hour with the following statement:
SELECT
opDate,
FORMAT(DATEPART(HOUR, sessionStartTime), '00') AS opHour,
Count(*) AS Total
FROM #empSessionLog
Group BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00')
Order BY opDate, FORMAT(DATEPART(HOUR, sessionStartTime), '00') ASC
Results:
opDateopHourTotal
2015-01-20041
2015-01-20058
2015-01-200615
2015-01-20078
2015-01-20082
2015-01-20093
2015-01-20107
2015-01-20118
2015-01-20127
2015-01-201315
2015-01-201415
2015-01-20151
2015-01-20162
2015-01-20173
2015-01-20181
2015-01-21193
2015-01-21203
2015-01-21217
Where I am stuck is how do I count the sessions that remain active per hour until the session is closed with the sessionCloseTime.
Thanks in advance!!
Gene
January 22, 2015 at 11:59 am
It would be a huge help if you constructed an example for us where there was DDL for a sample table, insers of sample data , and then the expected results from that data.
As near as I can tell, your example table does not match your example data.
If just a general description is good enough, you can do a CTE with numbers for each of the hours, and then join to it where the number is between the hour of the start time and the hour of the end time, and then group it by the hours and count.
January 22, 2015 at 12:49 pm
The good news is that you are on SQL 2012 and the new Windowing Function enhancements there will make this quite doable and with good performance. The bad news is that I don't have time to hash it out. The good news is that others here probably will. The best news is that I am pretty sure Itzik Ben-Gan and likely others already have solutions to this in blog posts, sqlmag.com or other online resources.
Binoogle should help you find some samples.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 25, 2015 at 6:22 pm
IBG does have some great articles on intervals for sure, but I don't think anything particularly fancy is needed here.
WITH SampleData (OpDate, sessionStartTime, sessionCloseTime) AS
(
SELECT CAST('2015-01-20' AS DATE), CAST('2015-01-20 14:32:59.130' AS DATETIME), CAST('2015-01-20 14:33:14.6299166' AS DATETIME2)
UNION ALL SELECT '2015-01-20','2015-01-20 06:58:33.730','2015-01-20 15:27:16.9133442'
UNION ALL SELECT '2015-01-20','2015-01-20 09:56:22.840','2015-01-20 17:56:29.7555853'
UNION ALL SELECT '2015-01-20','2015-01-20 05:59:18.613','2015-01-20 14:05:19.0426707'
UNION ALL SELECT '2015-01-20','2015-01-20 06:03:45.970','2015-01-20 14:17:34.9328702'
UNION ALL SELECT '2015-01-20','2015-01-20 12:37:39.597','2015-01-20 12:37:55.7542296'
UNION ALL SELECT '2015-01-20','2015-01-20 13:28:46.980','2015-01-20 21:44:29.7902115'
UNION ALL SELECT '2015-01-20','2015-01-20 04:52:59.443','2015-01-20 12:45:04.1644632'
UNION ALL SELECT '2015-01-20','2015-01-20 05:59:29.227','2015-01-20 14:08:14.9452666'
UNION ALL SELECT '2015-01-20','2015-01-20 06:16:13.943','2015-01-20 13:55:09.7511570'
UNION ALL SELECT '2015-01-20','2015-01-20 07:02:33.433','2015-01-20 13:54:29.7691216'
UNION ALL SELECT '2015-01-20','2015-01-20 12:56:14.437','2015-01-20 21:47:32.2838659'
UNION ALL SELECT '2015-01-20','2015-01-20 13:56:17.973','2015-01-20 21:36:04.6284206'
UNION ALL SELECT '2015-01-20','2015-01-20 14:12:55.620','2015-01-20 21:37:46.8277100'
UNION ALL SELECT '2015-01-21','2015-01-20 19:00:10.370','2015-01-20 19:01:18.5469985'
UNION ALL SELECT '2015-01-20','2015-01-20 06:05:14.290','2015-01-20 14:26:38.7769319'
UNION ALL SELECT '2015-01-20','2015-01-20 07:54:20.703','2015-01-20 14:58:53.4825405'
UNION ALL SELECT '2015-01-20','2015-01-20 10:37:50.410','2015-01-20 21:06:19.5611850'
UNION ALL SELECT '2015-01-20','2015-01-20 10:43:14.437','2015-01-20 19:20:27.1715458'
UNION ALL SELECT '2015-01-20','2015-01-20 10:57:24.600','2015-01-20 17:07:39.9467432'
UNION ALL SELECT '2015-01-20','2015-01-20 11:42:34.937','2015-01-20 11:43:11.1604422'
UNION ALL SELECT '2015-01-20','2015-01-20 13:06:42.877','2015-01-20 14:05:20.2009713'
UNION ALL SELECT '2015-01-20','2015-01-20 13:38:31.790','2015-01-20 21:54:53.7340106'
UNION ALL SELECT '2015-01-20','2015-01-20 13:59:46.227','2015-01-20 21:26:51.6078367'
UNION ALL SELECT '2015-01-20','2015-01-20 14:59:26.897','2015-01-20 14:59:39.4799102'
UNION ALL SELECT '2015-01-20','2015-01-20 16:12:10.877','2015-01-20 16:12:23.8861160'
UNION ALL SELECT '2015-01-21','2015-01-20 20:01:56.263','2015-01-20 20:02:16.8894884'
UNION ALL SELECT '2015-01-21','2015-01-20 21:39:15.557','2015-01-21 06:01:43.1597490'
UNION ALL SELECT '2015-01-20','2015-01-20 13:44:21.840','2015-01-20 21:23:34.8060943'
UNION ALL SELECT '2015-01-21','2015-01-20 21:30:40.497','2015-01-21 08:42:06.9555833'
UNION ALL SELECT '2015-01-20','2015-01-20 06:04:02.777','2015-01-20 14:10:13.8869506'
UNION ALL SELECT '2015-01-20','2015-01-20 08:06:31.463','2015-01-20 08:06:53.6402892'
UNION ALL SELECT '2015-01-20','2015-01-20 14:49:58.047','2015-01-20 21:48:11.5114277'
UNION ALL SELECT '2015-01-21','2015-01-20 19:31:48.897','2015-01-20 19:32:07.2625646'
UNION ALL SELECT '2015-01-20','2015-01-20 05:44:33.593','2015-01-20 21:36:26.3447710'
UNION ALL SELECT '2015-01-20','2015-01-20 06:55:43.970','2015-01-20 15:00:32.9154499'
UNION ALL SELECT '2015-01-20','2015-01-20 06:58:56.967','2015-01-20 15:22:41.4673167'
UNION ALL SELECT '2015-01-20','2015-01-20 07:07:43.833','2015-01-20 13:57:25.9191146'
UNION ALL SELECT '2015-01-20','2015-01-20 07:17:18.270','2015-01-20 13:31:14.2577403'
UNION ALL SELECT '2015-01-20','2015-01-20 09:57:17.407','2015-01-20 17:54:02.5617463'
UNION ALL SELECT '2015-01-20','2015-01-20 12:56:36.103','2015-01-20 20:56:28.3974009'
UNION ALL SELECT '2015-01-20','2015-01-20 13:26:53.700','2015-01-20 21:47:04.8738773'
UNION ALL SELECT '2015-01-20','2015-01-20 06:57:41.310','2015-01-20 14:57:43.9601427'
UNION ALL SELECT '2015-01-20','2015-01-20 07:00:50.387','2015-01-20 15:03:41.5787814'
UNION ALL SELECT '2015-01-20','2015-01-20 07:01:17.280','2015-01-20 11:55:54.6993909'
UNION ALL SELECT '2015-01-20','2015-01-20 07:03:49.287','2015-01-20 15:47:41.8889618'
UNION ALL SELECT '2015-01-20','2015-01-20 10:06:23.760','2015-01-20 15:20:45.3993880'
UNION ALL SELECT '2015-01-20','2015-01-20 10:57:21.940','2015-01-20 19:34:25.1427994'
UNION ALL SELECT '2015-01-20','2015-01-20 12:55:23.397','2015-01-20 20:37:22.7280870'
UNION ALL SELECT '2015-01-20','2015-01-20 13:56:25.737','2015-01-20 21:24:40.6800604'
UNION ALL SELECT '2015-01-20','2015-01-20 16:11:44.153','2015-01-20 16:11:58.2644405'
UNION ALL SELECT '2015-01-20','2015-01-20 17:09:10.527','2015-01-20 17:09:42.2642180'
UNION ALL SELECT '2015-01-20','2015-01-20 18:26:57.937','2015-01-20 18:27:18.8651814'
UNION ALL SELECT '2015-01-21','2015-01-20 20:25:08.367','2015-01-20 20:26:53.4870852'
UNION ALL SELECT '2015-01-20','2015-01-20 14:14:18.947','2015-01-20 21:14:39.5385291'
UNION ALL SELECT '2015-01-20','2015-01-20 08:43:30.967','2015-01-20 17:22:39.7275009'
UNION ALL SELECT '2015-01-20','2015-01-20 13:27:51.600','2015-01-20 21:37:31.0845613'
UNION ALL SELECT '2015-01-20','2015-01-20 11:52:10.520','2015-01-20 11:52:31.3193575'
UNION ALL SELECT '2015-01-20','2015-01-20 13:55:13.653','2015-01-20 21:42:16.3716312'
UNION ALL SELECT '2015-01-20','2015-01-20 15:27:24.240','2015-01-20 21:15:55.7206423'
UNION ALL SELECT '2015-01-21','2015-01-20 21:29:02.013','2015-01-21 06:13:54.3112466'
UNION ALL SELECT '2015-01-20','2015-01-20 05:37:20.657','2015-01-20 14:02:59.0021339'
UNION ALL SELECT '2015-01-20','2015-01-20 05:59:02.227','2015-01-20 14:51:08.9077080'
UNION ALL SELECT '2015-01-20','2015-01-20 06:02:41.477','2015-01-20 14:06:42.3786345'
UNION ALL SELECT '2015-01-20','2015-01-20 06:06:58.307','2015-01-20 14:12:42.5994001'
UNION ALL SELECT '2015-01-20','2015-01-20 06:19:32.823','2015-01-20 11:47:49.6103998'
UNION ALL SELECT '2015-01-20','2015-01-20 14:03:43.747','2015-01-20 21:11:56.3315989'
UNION ALL SELECT '2015-01-20','2015-01-20 14:05:17.853','2015-01-20 21:44:57.8803795'
UNION ALL SELECT '2015-01-20','2015-01-20 14:13:44.330','2015-01-20 21:34:06.3004142'
UNION ALL SELECT '2015-01-20','2015-01-20 05:36:49.540','2015-01-20 13:38:58.0820089'
UNION ALL SELECT '2015-01-20','2015-01-20 05:58:37.797','2015-01-20 14:20:32.0060283'
UNION ALL SELECT '2015-01-20','2015-01-20 06:58:09.170','2015-01-20 14:56:55.4300087'
UNION ALL SELECT '2015-01-20','2015-01-20 06:58:35.357','2015-01-20 17:06:23.5524613'
UNION ALL SELECT '2015-01-20','2015-01-20 10:58:17.380','2015-01-20 19:39:53.3562629'
UNION ALL SELECT '2015-01-20','2015-01-20 11:07:59.513','2015-01-20 20:01:38.7771990'
UNION ALL SELECT '2015-01-20','2015-01-20 11:42:38.660','2015-01-20 14:08:18.1047026'
UNION ALL SELECT '2015-01-20','2015-01-20 11:46:56.993','2015-01-20 19:43:32.5519692'
UNION ALL SELECT '2015-01-20','2015-01-20 13:30:17.947','2015-01-20 21:47:14.4841045'
UNION ALL SELECT '2015-01-20','2015-01-20 14:04:38.660','2015-01-20 21:01:00.9489002'
UNION ALL SELECT '2015-01-20','2015-01-20 17:24:50.310','2015-01-20 17:25:07.2505869'
UNION ALL SELECT '2015-01-21','2015-01-20 19:50:36.947','2015-01-20 19:50:45.2724562'
UNION ALL SELECT '2015-01-21','2015-01-20 21:26:01.717','2015-01-20 21:26:27.3945685'
UNION ALL SELECT '2015-01-20','2015-01-20 14:02:20.307','2015-01-20 21:26:10.4938165'
UNION ALL SELECT '2015-01-21','2015-01-20 20:41:23.310','2015-01-20 21:24:48.2849439'
UNION ALL SELECT '2015-01-20','2015-01-20 07:02:59.580','2015-01-20 15:03:09.4201222'
UNION ALL SELECT '2015-01-20','2015-01-20 12:33:22.637','2015-01-20 12:33:41.6719856'
UNION ALL SELECT '2015-01-20','2015-01-20 13:36:55.920','2015-01-20 21:44:03.1902854'
UNION ALL SELECT '2015-01-20','2015-01-20 13:56:35.357','2015-01-20 21:41:12.1944898'
UNION ALL SELECT '2015-01-20','2015-01-20 05:40:09.910','2015-01-20 11:49:40.0552588'
UNION ALL SELECT '2015-01-20','2015-01-20 09:03:07.633','2015-01-20 16:32:02.2312852'
UNION ALL SELECT '2015-01-20','2015-01-20 11:52:25.347','2015-01-20 19:29:38.2940908'
UNION ALL SELECT '2015-01-20','2015-01-20 13:29:37.610','2015-01-20 21:50:00.3525520'
UNION ALL SELECT '2015-01-20','2015-01-20 14:10:34.137','2015-01-20 21:50:39.9676604'
UNION ALL SELECT '2015-01-21','2015-01-20 21:30:10.333','2015-01-21 08:02:04.5393614'
UNION ALL SELECT '2015-01-21','2015-01-20 21:33:08.983','2015-01-21 08:01:40.3326150'
UNION ALL SELECT '2015-01-21','2015-01-20 21:54:49.967','2015-01-20 21:55:15.5970207'
UNION ALL SELECT '2015-01-20','2015-01-20 06:08:04.570','2015-01-20 14:20:54.2859274'
UNION ALL SELECT '2015-01-20','2015-01-20 06:10:37.890','2015-01-20 11:41:42.2219588'
UNION ALL SELECT '2015-01-20','2015-01-20 10:57:40.863','2015-01-20 19:24:13.7005142'
UNION ALL SELECT '2015-01-20','2015-01-20 11:02:09.100','2015-01-20 20:55:54.2942428'
UNION ALL SELECT '2015-01-20','2015-01-20 11:38:20.480','2015-01-20 19:12:26.6052552'
UNION ALL SELECT '2015-01-20','2015-01-20 12:29:56.523','2015-01-20 19:28:31.8472082'
UNION ALL SELECT '2015-01-20','2015-01-20 12:31:29.697','2015-01-20 12:31:44.1097679'
UNION ALL SELECT '2015-01-20','2015-01-20 13:27:54.430','2015-01-20 21:52:53.5069305'
UNION ALL SELECT '2015-01-20','2015-01-20 14:00:07.647','2015-01-20 21:57:33.2832468'
UNION ALL SELECT '2015-01-20','2015-01-20 14:01:39.907','2015-01-20 21:29:07.3326878'
UNION ALL SELECT '2015-01-20','2015-01-20 14:03:06.057','2015-01-20 21:18:21.9986703'
UNION ALL SELECT '2015-01-20','2015-01-20 14:58:59.073','2015-01-20 14:59:12.7897409'
UNION ALL SELECT '2015-01-20','2015-01-20 17:08:45.317','2015-01-20 17:08:57.1577934'
),
DatesInData AS
(
SELECT OpDate
FROM SampleData
GROUP BY OpDate
),
HoursOfTheDay AS
(
SELECT h=DATEADD(hour, a.h-1, CAST(b.OpDate AS DATETIME))
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24)) a (h)
CROSS JOIN DatesInData b
)
SELECT h
,Logins=
(
SELECT COUNT(*)
FROM SampleData
WHERE h=DATEADD(hour, DATEDIFF(hour, 0, sessionStartTime), 0)
)
,Active=
(
SELECT COUNT(*)
FROM SampleData
WHERE h BETWEEN sessionStartTime AND sessionCloseTime
)
FROM HoursOfTheDay;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 26, 2015 at 8:06 am
That's quite interesting Dwain! I will have to set aside some time to break it down, and also modify it to work with the typical "UserID did something" scenarios I see at my web/mobile clients. I want to test scalability too.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2015 at 7:49 pm
TheSQLGuru (1/26/2015)
That's quite interesting Dwain! I will have to set aside some time to break it down, and also modify it to work with the typical "UserID did something" scenarios I see at my web/mobile clients. I want to test scalability too.
Happy to be of help.
Note that I'm not sure it is 100% correct. It could depend on your definition of when you want to do the counting. For example, is a session active in a particular hour if it was active the entire hour or only part of the hour?
I think either can be handled though.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply