Select Number of Times a Product sold per minute
-
Hello Everyone,
I am in need of some assistance with an odd type of query. From the data listed below, I am stuck on how to count the number of times a product has sold per minute of each day of the date listed in DateSold Column. I have been using March 17th 2018 to test, since there are only two sales for that day, 27 minutes and 10 seconds apart.DECLARE @ProductsSold TABLE
(
ProductNumber varchar(20)
, DateSold datetime
);INSERT INTO @ProductsSold
SELECT 'AH2D-PACKAGE', '2018-01-02 08:11:34.633' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 08:11:59.447' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 08:12:12.080' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 08:24:06.887' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 08:24:24.657' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 08:24:42.010' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 09:29:25.533' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 09:29:53.653' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-02 09:30:17.540' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 07:46:11.500' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 07:49:29.667' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 07:52:15.243' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 07:56:40.490' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:04:20.383' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:06:49.507' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:11:59.243' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:15:44.600' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:25:25.977' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:33:03.570' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:37:08.080' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:39:14.500' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:47:21.367' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:50:52.797' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:52:10.120' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:53:07.370' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-03 23:54:20.340' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 00:50:29.277' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 00:53:37.307' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 00:54:39.053' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 00:55:49.517' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 01:35:00.977' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-04 01:38:21.500' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-06 23:36:29.447' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-07 00:15:38.113' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-07 00:26:22.550' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 05:35:55.353' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 05:36:39.927' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 05:52:47.073' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 07:54:42.600' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 07:59:02.080' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 23:19:28.757' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 23:31:04.597' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-15 23:35:31.900' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-16 08:43:54.183' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-17 01:25:07.950' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-17 10:02:03.307' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-01-30 00:23:39.173' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-05 01:22:54.860' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-12 04:21:16.940' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-17 05:05:32.297' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-17 05:32:22.307' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:34:58.000' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:37:21.080' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:44:34.787' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:44:51.283' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:49:21.000' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:55:58.573' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:57:51.747' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-29 10:58:51.117' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 03:51:53.573' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 03:54:53.647' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 04:06:29.923' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:17:48.507' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:18:45.273' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:36:52.160' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:38:59.633' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:45:20.870' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 06:53:01.990' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 07:00:40.290' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-03-30 08:13:08.743' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 06:50:51.030' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 08:59:52.990' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 09:10:20.617' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 09:19:03.843' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 09:26:31.230' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 09:27:18.020' UNION ALL
SELECT 'AH2D-PACKAGE', '2018-04-03 09:33:35.457' UNION ALL
SELECT 'APA-A1000380', '2018-03-11 08:47:46.073' UNION ALL
SELECT 'APA-A1000380', '2018-03-11 09:17:36.717' UNION ALL
SELECT 'APA-A1000380', '2018-03-11 09:23:17.510' UNION ALL
SELECT 'APA-A1000380', '2018-03-21 06:22:19.270' UNION ALL
SELECT 'APA-A1000380', '2018-03-21 06:22:19.333' UNION ALL
SELECT 'APA-A1000380', '2018-03-21 06:22:19.413' UNION ALL
SELECT 'APA-A1000380', '2018-03-21 06:22:19.483' UNION ALL
SELECT 'APA-A1000380', '2018-03-21 06:22:19.593' UNION ALL
SELECT 'APA-A1000380', '2018-04-23 20:32:45.153' UNION ALL
SELECT 'APG-12000-6P', '2018-04-23 10:46:22.747' UNION ALL
SELECT 'APG-12000-6P', '2018-04-23 10:46:22.907' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 03:30:21.940' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 03:35:08.653' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 03:55:42.380' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 06:32:56.943' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 07:03:36.560' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 07:17:31.967' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 07:18:14.653' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:01:24.527' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:37:11.153' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:37:35.293' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:37:53.917' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:38:52.640' UNION ALL
SELECT 'APG-B219-KR5', '2018-01-25 08:40:08.367' UNION ALL
SELECT 'APG-B228FT14', '2018-01-23 02:33:07.473' UNION ALL
SELECT 'APG-B228FT14', '2018-01-23 02:49:13.200' UNION ALL
SELECT 'APG-B228FT14', '2018-01-23 02:53:11.303' UNION ALL
SELECT 'APG-B228FT14', '2018-01-23 02:54:34.253' UNION ALL
SELECT 'APG-B228FT14', '2018-01-23 03:20:41.980' UNION ALL
SELECT 'APG-B228TS14', '2018-04-03 10:31:39.840' UNION ALL
SELECT 'APG-B228TS14', '2018-04-03 10:33:45.480' UNION ALL
SELECT 'APG-B228TS14', '2018-04-03 10:36:58.663' UNION ALL
SELECT 'APG-B228TS14', '2018-04-03 10:37:57.527' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:27:31.923' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:29:22.217' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:31:52.533' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:36:53.903' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:39:13.953' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:40:39.067' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 03:43:04.580' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 05:16:32.757' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 05:35:47.163' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 08:26:50.150' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 08:33:51.370' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 09:37:38.307' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:09:39.717' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:13:19.747' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:14:32.210' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:22:55.790' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:50:53.630' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:52:34.087' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 10:55:17.607' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 11:10:53.400' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 11:17:21.833' UNION ALL
SELECT 'APG-B228TS14', '2018-04-04 11:22:45.557' UNION ALL
SELECT 'APG-B228TS14', '2018-04-05 04:30:15.760' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 01:49:36.350' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 08:14:53.583' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 08:16:57.773' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:22:18.293' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:27:56.900' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:32:18.897' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:39:00.110' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:41:07.743' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:48:15.947' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 10:53:30.340' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 11:02:21.697' UNION ALL
SELECT 'APG-B228TS14', '2018-04-06 11:05:42.763' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 04:25:56.420' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 04:28:42.790' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 04:31:26.397' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 04:32:48.440' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 06:55:45.013' UNION ALL
SELECT 'APG-B228TS14', '2018-04-09 07:37:28.177' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 08:21:57.067' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:22:58.987' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:28:33.373' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:33:28.563' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:43:38.260' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:46:08.590' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:49:59.083' UNION ALL
SELECT 'APG-B228TS14', '2018-04-10 10:56:03.240' UNION ALL
SELECT 'APG-B228TS14', '2018-04-11 10:04:38.083' UNION ALL
SELECT 'APG-B228TS14', '2018-04-11 10:31:57.503' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 04:39:21.850' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 04:42:19.873' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 04:49:49.787' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 04:54:35.653' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:02:21.300' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:10:24.067' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:16:58.470' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:29:16.777' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:48:09.323' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 05:52:18.797' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:02:23.870' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:13:50.007' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:23:03.010' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:26:45.147' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:27:51.433' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:35:30.600' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:39:43.010' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:46:16.870' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:50:59.117' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:53:30.743' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 06:57:14.433' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:04:45.700' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:06:01.007' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:06:39.527' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:16:11.240' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:21:50.287' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:22:57.703' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:23:53.060' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:32:34.230' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 07:52:51.927' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:21:05.730' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:23:28.157' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:28:31.807' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:45:18.880' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:51:28.383' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 08:55:19.000' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 09:00:06.587' UNION ALL
SELECT 'APG-B228TS14', '2018-04-13 09:27:24.880' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 02:27:20.573' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:03:13.103' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:04:26.833' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:16:03.163' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:25:49.813' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:28:33.160' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:30:14.513' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:31:38.330' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:34:22.547' UNION ALL
SELECT 'APG-B228TS14', '2018-04-16 07:41:09.510' UNION ALL
SELECT 'APG-B228TS14', '2018-04-17 04:11:23.567' UNION ALL
SELECT 'APG-B228TS14', '2018-04-17 04:17:12.283' UNION ALL
SELECT 'APG-B228TS14', '2018-04-17 04:21:00.263' UNION ALL
SELECT 'APG-B228TS14', '2018-04-17 04:21:47.827' UNION ALL
SELECT 'APG-B228TS14', '2018-04-17 04:23:01.913' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 02:26:39.677' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 02:28:06.613' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 05:52:00.967' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 06:36:26.180' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:05:10.170' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:06:57.767' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:11:54.190' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:17:55.577' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:30:27.360' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:53:03.560' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 07:57:08.490' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:12:58.970' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:16:14.400' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:36:02.973' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:36:20.987' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:39:09.240' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:46:49.973' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:50:02.750' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 08:59:37.900' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:05:25.837' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:18:40.543' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:26:17.930' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:31:40.227' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:39:21.820' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:41:58.470' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:46:01.440' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 09:53:12.237' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:11:39.143' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:26:01.370' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:28:41.797' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:29:46.993' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:32:19.743' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:40:12.570' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:41:32.243' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:42:27.817' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:45:06.807' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:49:56.107' UNION ALL
SELECT 'APG-B228TS14', '2018-04-18 10:55:29.790' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 01:30:58.567' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 01:50:41.880' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 01:57:56.733' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 02:12:14.420' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 02:14:11.737' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 02:32:43.543' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 03:37:24.880' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 03:53:19.490' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 04:11:00.840' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 04:28:29.893' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 04:53:22.210' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 05:27:11.457' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 05:35:14.713' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 05:47:38.217' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 06:05:12.853' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 06:10:41.190' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 06:38:54.110' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 06:49:32.830' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:05:37.020' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:14:18.027' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:19:42.033' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:26:01.070' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:28:05.343' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:30:24.257' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:34:41.093' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:53:29.050' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:54:04.103' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 07:55:01.453' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 08:36:39.413' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 08:49:13.170' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 09:24:38.693' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 09:46:11.547' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:15:29.390' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:33:04.913' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:41:56.893' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:45:33.620' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:49:35.460' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 10:56:22.010' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:05:24.720' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:08:43.113' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:13:33.567' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:19:01.603' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:19:58.613' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:24:58.903' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:29:42.620' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:36:09.073' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:39:52.540' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:45:17.140' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:49:48.610' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 11:57:12.873' UNION ALL
SELECT 'APG-B228TS14', '2018-04-19 12:02:37.347' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 02:10:50.043' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 02:14:05.587' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 02:19:15.367' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 02:21:55.887' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 02:24:25.783' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 03:44:54.823' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 03:48:57.147' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 03:54:59.180' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 04:07:16.310' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 04:15:16.377' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 04:25:18.300' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 04:51:04.723' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 05:13:09.493' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 05:17:35.023' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 05:20:52.327' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 05:27:21.677' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 05:51:19.517' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 06:42:53.910' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 06:49:41.210' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 06:58:28.180' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:08:50.470' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:12:06.127' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:22:04.170' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:25:15.423' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:29:48.307' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:45:43.543' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 07:50:18.250' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 08:29:20.923' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 08:34:54.587' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 08:54:23.703' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:01:57.680' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:06:39.123' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:15:49.460' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:18:43.780' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:24:40.710' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:42:57.743' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:44:58.520' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 09:52:17.243' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 10:08:14.663' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 10:14:46.077' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 10:23:25.007' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 10:26:43.197' UNION ALL
SELECT 'APG-B228TS14', '2018-04-20 10:46:23.977' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 02:12:39.597' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 02:20:33.540' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 02:24:07.983' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 05:08:20.263' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 05:12:19.780' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 05:32:32.937' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 05:47:18.167' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 05:54:22.423' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:13:10.940' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:16:07.753' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:46:24.767' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:50:07.130' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:53:58.750' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 06:58:35.793' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 07:02:42.617' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 07:06:43.017' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 07:26:21.233' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 07:39:28.207' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 07:41:56.593' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:02:54.380' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:04:48.223' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:07:08.330' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:32:18.417' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:47:34.437' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:47:36.017' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:51:29.597' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 08:51:30.877' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:07:10.980' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:07:13.780' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:19:06.580' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:19:09.010' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:34:50.250' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:34:52.607' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:51:42.910' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 09:51:45.127' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:25:50.717' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:25:52.497' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:25:53.613' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:34:38.147' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:34:39.277' UNION ALL
SELECT 'APG-B228TS14', '2018-04-23 10:34:40.320' UNION ALL
SELECT 'APG-B251-K14', '2018-01-25 01:25:57.177' UNION ALL
SELECT 'APG-B251-K14', '2018-01-25 03:14:32.687' UNION ALL
SELECT 'APG-B258-KT7', '2018-01-25 05:40:27.657' UNION ALL
SELECT 'APG-B258-KT7', '2018-01-25 05:42:20.610' UNION ALL
SELECT 'APG-B267-QS5', '2018-03-12 15:01:15.693' UNION ALL
SELECT 'APG-B267-QS5', '2018-03-12 15:10:26.730' UNION ALL
SELECT 'APG-B267-QS5', '2018-03-13 08:35:45.417' UNION ALL
SELECT 'APG-B267-QS5', '2018-03-13 08:37:12.767' UNION ALL
SELECT 'APG-B267-QS5', '2018-03-13 15:23:56.190' UNION ALL
SELECT 'APG-B271-K14', '2018-04-03 10:37:00.907' UNION ALL
SELECT 'APG-B271-K14', '2018-04-03 10:37:58.703' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:27:33.790' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:29:24.363' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:31:54.853' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:36:55.570' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:39:14.873' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:40:40.220' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 03:43:05.833' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 05:16:35.580' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 05:35:50.603' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 08:26:51.400' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 08:33:53.390' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 09:37:39.810' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:09:41.187' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:13:21.157' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:14:33.590' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:22:57.213' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:50:54.820' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:52:35.047' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 10:55:19.237' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 11:10:55.167' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 11:17:23.263' UNION ALL
SELECT 'APG-B271-K14', '2018-04-04 11:22:47.060' UNION ALL
SELECT 'APG-B271-K14', '2018-04-05 04:30:21.360' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 01:49:38.703' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 08:14:56.053' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 08:16:59.810' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:22:20.957' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:27:57.143' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:32:19.210' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:39:01.203' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:41:07.953' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:48:16.857' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 10:53:30.553' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 11:02:24.523' UNION ALL
SELECT 'APG-B271-K14', '2018-04-06 11:05:45.803' UNION ALL
SELECT 'APG-B369-K14', '2018-01-23 02:49:13.567' UNION ALL
SELECT 'APG-B369-K14', '2018-01-23 02:53:11.513' UNION ALL
SELECT 'APG-B369-K14', '2018-01-23 02:54:34.573' UNION ALL
SELECT 'APG-B369-K14', '2018-01-23 03:20:42.307' UNION ALL
SELECT 'APG-B376-QS5', '2018-06-13 17:39:53.200' UNION ALL
SELECT 'APG-B428-KP5', '2018-03-09 01:29:05.720' UNION ALL
SELECT 'APG-B697-KP5', '2018-03-14 14:18:08.200' UNION ALL
SELECT 'APG-B697-KP5', '2018-03-14 14:18:08.447' UNION ALL
SELECT 'APG-B697-QP5', '2018-06-13 17:39:53.323' UNION ALL
SELECT 'APG-B697-QP5', '2018-06-13 17:39:53.403' UNION ALL
SELECT 'APG-B709-QT5', '2018-01-23 00:49:23.283' UNION ALL
SELECT 'APG-B709-QT5', '2018-01-23 01:06:08.970' UNION ALL
SELECT 'APG-B709-QT5', '2018-01-23 01:25:19.240' UNION ALL
SELECT 'APG-B775-KS5', '2018-01-25 00:31:05.840' UNION ALL
SELECT 'APG-D158-233', '2018-01-23 06:45:49.360' UNION ALL
SELECT 'APG-D158-233', '2018-01-23 06:49:03.217' UNION ALL
SELECT 'APG-D158-233', '2018-01-23 06:51:07.337' UNION ALL
SELECT 'APG-D314-134', '2018-01-25 05:40:27.470' UNION ALL
SELECT 'APG-D314-134', '2018-01-25 05:42:19.360' UNION ALL
SELECT 'APG-D633-324', '2018-03-16 01:48:45.070' UNION ALL
SELECT 'APG-D633-324', '2018-03-16 01:50:58.713' UNION ALL
SELECT 'APG-D705-556', '2018-01-25 03:14:32.873' UNION ALL
SELECT 'APG-D705-556', '2018-01-25 03:21:33.407' UNION ALL
SELECT 'APG-H583-4PC', '2018-01-25 00:06:12.507' UNION ALL
SELECT 'APG-H583-4PC', '2018-01-25 00:17:12.990' UNION ALL
SELECT 'APG-H583-4PC', '2018-01-25 00:27:00.630' UNION ALL
SELECT 'APG-H583-4PC', '2018-01-25 00:31:05.783' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 06:15:30.997' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:12:36.130' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:35:06.017' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:36:09.453' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:38:51.880' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:39:09.377' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 07:40:02.123' UNION ALL
SELECT 'APG-H697-4E ', '2018-02-20 08:09:03.423' UNION ALL
SELECT 'APG-U759005P', '2018-01-25 03:55:38.490' UNION ALL
SELECT 'APG-W697-4PA', '2018-01-25 03:21:33.460' UNION ALL
SELECT 'APGU74900L9N', '2018-01-23 01:25:18.767' UNION ALL
SELECT 'APGU75000L9N', '2018-01-23 03:44:33.317' UNION ALL
SELECT 'APGU75000L9N', '2018-01-23 03:58:25.510' UNION ALL
SELECT 'APGU75000L9N', '2018-01-23 04:41:03.857' UNION ALL
SELECT 'APK-10601-4P', '2018-03-11 23:54:17.120' UNION ALL
SELECT 'APK-10601-4P', '2018-03-12 00:01:21.497' UNION ALL
SELECT 'APK-10601-4P', '2018-03-12 23:49:58.787' UNION ALL
SELECT 'APK-10601-4P', '2018-03-13 11:28:13.793' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 01:49:39.513' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 02:01:25.853' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 02:08:17.010' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 02:49:40.970' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 02:54:51.950' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 06:26:38.387' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 10:31:33.103' UNION ALL
SELECT 'APK-10601-4P', '2018-03-14 22:46:47.143' UNION ALL
SELECT 'APK-10601-4P', '2018-03-15 03:29:44.030';-- Verification count
select count(*) AS [NumberOf''AH2D-PACKAGE'']
FROM @ProductsSold
WHERE ProductNumber = 'AH2D-PACKAGE';WITH
ProductsSold
AS
(
SELECT
DATEPART(Day,DateSold) AS DayNumber
, DATEPART(Month,DateSold) AS MonthNumber
, DATEPART(Hour,DateSold) AS HourOfDay
, DATEPART(Minute,DateSold) AS MinuteOfHour
, DATEPART(Second,DateSold) AS SecondsNumber
--, 'The '+CAST(SUM(DATEPART(Minute,DateSold)) AS varchar(5))+' Minute of the '+CAST(DATEPART(Hour,DateSold) AS varchar(5))+' Hour' AS HourAndMinute
, CAST(DateSold AS Date) AS DateSoldAsDate
, DateSold
, ProductNumber
FROM
@ProductsSold
WHERE ProductNumber = 'AH2D-PACKAGE'
GROUP BY DateSold, ProductNumber
)SELECT
DayNumber
, MonthNumber
, HourOfDay
, MinuteOfHour
, SecondsNumber
, DateSoldFROM
ProductsSold
GROUP BY DayNumber,MonthNumber, HourOfDay, MinuteOfHour, DateSold, SecondsNumberSELECT
ProductNumber
,DateSold
FROM
@ProductsSold
WHERE ProductNumber = 'AH2D-PACKAGE'
ORDER BY DateSold ASC;
--GROUP BY DateSold, ProductNumber
This is only the request of the second query above. As you can see, only 2 "AH2D-Package" products have sold that day. 27 minutes and 10 seconds apart.
I am trying to format the resultset to be like this:
On 2018-03-17 (2) products of AH2D-Package sold at approx 27 minutes and 10 seconds from each other.And then for others
On 2018-03-29 (8) products of AH2D-Package sold on Hour 10, between 34 minutes after the hour and 58 minutes after the hour.
I am just looking for something like that. I know that is a lot of work. I am more of a DBA and not a Developer to this depth. I know there must be a way to get close so that I can tell how many products have sold during what hour of what day
Thank you in Advance for all your suggestions and code samplesAndrew SQLDBA
-
I'm not really seeing what you are trying to accomplish. What you are asking doesn't match the title of your post which is a count of products sold per minute, but it looks like you are asking for a count of products sold each hour. Not sure why you need to know the difference in time between first sale and last sale during the hour.
-
Lynn,
Then how about the number of products sold per hour. I have listed everything that I can figure in the body of the post.Andrew SQLDBA
-
Lynn makes a good point. If you actually want the number of products sold per minute, your data doesn't appear to justify that level of measurement. Seems to me, that unless your real data is considerably larger in volume, and thus has considerably more sales activity, even an hour by hour breakdown would result in fractions of a unit sold per hour... Generally, that low an average doesn't provide any kind of value to the person receiving it. It's too small to be a useful measure. However, if you are eventually going to have to make that kind of analysis, then your first task is to figure out how to generate the list of the individual elements for a given date range. With that data in hand, you then LEFT OUTER JOIN your data to the list of elements and then group by the element from the element list.
Something else that I noticed is that what you have in the way of data appears to be a list of times of day and a product, which suggests that the quantity that sold is assumed to be just one all the time. For something that someone has asked you for a by the minute analysis, that seems rather absurd. It actually starts raising questions like: Are you actually open 24/7 ? Or do you only operate during a fixed set of hours?
If you need help generating a list of date/time elements, post back what you've tried and we can assist further.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I think this will do it in the event you don't want to break it down by ProductNumber. If you want it broken down by ProductNumber, add the field to the select list and the group by.
SELECT COUNT(ProductNumber) AS CNT,
CAST(DateSold AS DATE) AS DateSold,
LEFT(CAST(CONVERT(TIME,DateSold,108) AS VARCHAR(20)),5) AS TimeSold
FROM @ProductsSold
GROUP BY CAST(DateSold AS DATE), LEFT(CAST(CONVERT(TIME,DateSold,108) AS VARCHAR(20)),5) -
I'm slightly confused. Are you looking for a rate per hour, or are you looking to find out the dispersion between two sales?
In your examples, these are two very different queries:
1. On 2018-03-17 (2) products of AH2D-Package sold at approx 27 minutes and 10 seconds from each other.
2. On 2018-03-29 (8) products of AH2D-Package sold on Hour 10, between 34 minutes after the hour and 58 minutes after the hour.Both of these are different than we sold 0.5 units of AH2D-Package on 2018-3-17.
There isn't a good way to get this data in one query, so I'm guessing you want two queries, or more, but how do you decide what results you're showing? If there are two sales on day, you get the first result? Is this per day? If there are more than 2, then how do you want things broken down if there are sales in multiple hours?
What I'd suggest is you pick 4 scenarios, give just the data for those, and show the exact results you want. Consider that there can be quite a few scenarios.
-
I can't tell specifically what you need, but I suggest using the "standard" approach to adjust a datetime to a given boundary. For example, the code below totals by MINUTE, but if you need by HOUR, just change MINUTE to HOUR in the CTE and you're all set.
;
WITH cte_totals AS (
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateSold), 0) AS DateSold,
ProductNumber,
COUNT(*) AS SalesCount
FROM #ProductsSold
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateSold), 0), ProductNumber
)
SELECT
DAY(DateSold) AS DayNumber,
MONTH(DateSold) AS MonhtNumber,
DATEPART(HOUR, DateSold) AS HourOfDay,
DATEPART(MINUTE, DateSold) AS MinuteOfHour,
CONVERT(varchar(16), DateSold, 120) AS DateSold,
ProductNumber,
SalesCount
FROM cte_totals
ORDER BY DateSold, ProductNumberSQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply