Column 'HVQ_Vehicle.timestamp' is invalid in the select list because it is not contained in either an...
-3
I have written a query to get the number of vehicles in a particular day of specific month. Here is my query,
SELECT CASE WHEN DATEPART(DAY, timestamp) = '1' THEN (select count(Vehicle_Id)) ELSE '0' END AS [1],
CASE WHEN DATEPART(DAY, timestamp) = '2' THEN (select count(Vehicle_Id)) ELSE '0' END AS [2],
CASE WHEN DATEPART(DAY, timestamp) = '3' THEN (select count(Vehicle_Id)) ELSE '0' END AS [3],
CASE WHEN DATEPART(DAY, timestamp) = '4' THEN (select count(Vehicle_Id)) ELSE '0' END AS [4],
CASE WHEN DATEPART(DAY, timestamp) = '5' THEN (select count(Vehicle_Id)) ELSE '0' END AS [5],
CASE WHEN DATEPART(DAY, timestamp) = '6' THEN (select count(Vehicle_Id)) ELSE '0' END AS [6],
CASE WHEN DATEPART(DAY, timestamp) = '7' THEN (select count(Vehicle_Id)) ELSE '0' END AS [7],
CASE WHEN DATEPART(DAY, timestamp) = '8' THEN (select count(Vehicle_Id)) ELSE '0' END AS [8],
CASE WHEN DATEPART(DAY, timestamp) = '9' THEN (select count(Vehicle_Id)) ELSE '0' END AS [9],
CASE WHEN DATEPART(DAY, timestamp) = '10' THEN (select count(Vehicle_Id)) ELSE '0' END AS [10],
CASE WHEN DATEPART(DAY, timestamp) = '11' THEN (select count(Vehicle_Id)) ELSE '0' END AS [11],
CASE WHEN DATEPART(DAY, timestamp) = '12' THEN (select count(Vehicle_Id)) ELSE '0' END AS [12],
CASE WHEN DATEPART(DAY, timestamp) = '13' THEN (select count(Vehicle_Id)) ELSE '0' END AS [13],
CASE WHEN DATEPART(DAY, timestamp) = '14' THEN (select count(Vehicle_Id)) ELSE '0' END AS [14],
CASE WHEN DATEPART(DAY, timestamp) = '15' THEN (select count(Vehicle_Id)) ELSE '0' END AS [15],
CASE WHEN DATEPART(DAY, timestamp) = '16' THEN (select count(Vehicle_Id)) ELSE '0' END AS [16],
CASE WHEN DATEPART(DAY, timestamp) = '17' THEN (select count(Vehicle_Id)) ELSE '0' END AS [17],
CASE WHEN DATEPART(DAY, timestamp) = '18' THEN (select count(Vehicle_Id)) ELSE '0' END AS [18],
CASE WHEN DATEPART(DAY, timestamp) = '19' THEN (select count(Vehicle_Id)) ELSE '0' END AS [19],
CASE WHEN DATEPART(DAY, timestamp) = '20' THEN (select count(Vehicle_Id)) ELSE '0' END AS [20],
CASE WHEN DATEPART(DAY, timestamp) = '21' THEN (select count(Vehicle_Id)) ELSE '0' END AS [21],
CASE WHEN DATEPART(DAY, timestamp) = '22' THEN (select count(Vehicle_Id)) ELSE '0' END AS [22],
CASE WHEN DATEPART(DAY, timestamp) = '23' THEN (select count(Vehicle_Id)) ELSE '0' END AS [23],
CASE WHEN DATEPART(DAY, timestamp) = '24' THEN (select count(Vehicle_Id)) ELSE '0' END AS [24],
CASE WHEN DATEPART(DAY, timestamp) = '25' THEN (select count(Vehicle_Id)) ELSE '0' END AS [25],
CASE WHEN DATEPART(DAY, timestamp) = '26' THEN (select count(Vehicle_Id)) ELSE '0' END AS [26],
CASE WHEN DATEPART(DAY, timestamp) = '27' THEN (select count(Vehicle_Id)) ELSE '0' END AS [27],
CASE WHEN DATEPART(DAY, timestamp) = '28' THEN (select count(Vehicle_Id)) ELSE '0' END AS [28],
CASE WHEN DATEPART(DAY, timestamp) = '29' THEN (select count(Vehicle_Id)) ELSE '0' END AS [29],
CASE WHEN DATEPART(DAY, timestamp) = '30' THEN (select count(Vehicle_Id)) ELSE '0' END AS [30],
CASE WHEN DATEPART(DAY, timestamp) = '31' THEN (select count(Vehicle_Id)) ELSE '0' END AS [31]
FROM HVQ_Vehicle
where month(timestamp) = '08'
GROUP BY day(timestamp);
This gives result in two rows instead of single row. How to obtain in a single row. Here is the sample data.
Vehicle_Id timestamp
8901361307130 2018-08-28 15:22:29.687
ME4JF39JGJT011230 2018-08-16 12:29:10.800
ME4JF39JGJT011231 2018-08-16 12:28:34.473
ME4JF39JGJT011232 2018-08-16 12:28:44.930
ME4JF39JGJT011233 2018-08-16 12:28:51.747
ME4JF39JGJT011234 2018-08-16 12:28:59.653
ME4JF39JGJT011235 2018-08-16 12:49:56.463
ME4JF39JGJT011236 2018-08-16 12:29:20.683
ME4JF39JGJT011237 2018-08-16 12:29:15.917
ME4JF39JGJT011239 2018-08-16 12:29:30.487
ME4JF39JGJT011240 2018-08-16 12:51:02.883
ME4JF39JGJT011241 2018-08-16 12:47:25.167
ME4JF39JGJT011242 2018-08-16 12:29:25.900
ME4JF39JGJT011243 2018-08-16 12:50:39.793
ME4JF39JGJT011244 2018-08-16 11:39:32.040
ME4JF39JGJT011245 2018-08-16 12:29:34.940
ME4JF39JGJT011246 2018-08-16 12:50:48.110
ME4JF39JGJT011248 2018-08-16 11:56:47.163
ME4JF39JGJT011250 2018-08-16 12:17:24.847
ME4JF39JGJT011252 2018-08-16 12:24:01.873
ME4JF39JGJT011253 2018-08-16 12:17:20.093
ME4JF39JGJT011254 2018-08-16 12:51:15.540
ME4JF39JGJT011255 2018-08-16 12:49:31.407
ME4JF39JGJT011257 2018-08-16 12:50:34.420
ME4JF39JGJT011260 2018-08-16 12:24:07.197
ME4JF39JGJT011262 2018-08-16 12:50:23.643
ME4JF39JGJT011263 2018-08-16 12:17:32.140
ME4JF39JGJT011264 2018-08-16 12:24:12.553
ME4JF39JGJT011265 2018-08-16 12:24:25.897
ME4JF39JGJT011266 2018-08-16 12:24:50.730
ME4JF39JGJT011267 2018-08-16 12:24:31.730
ME4JF39JGJT011271 2018-08-16 12:24:34.087
ME4JF39JGJT011275 2018-08-16 12:51:10.437
ME4JF39JGJT011288 2018-08-16 12:47:55.863
ME4JF39JGJT011293 2018-08-16 12:28:04.703
ME4JF39JGJT011294 2018-08-16 12:48:00.583
ME4JF39JGJT011316 2018-08-16 12:32:49.480
ME4JF39JGJT011324 2018-08-16 12:33:28.817
ME4JF39JGJT011327 2018-08-16 12:50:00.590
ME4JF39JGJT011330 2018-08-16 12:50:04.307
ME4JF39JGJT011336 2018-08-16 12:47:12.133
ME4JF39JGJT011340 2018-08-16 12:48:04.950
ME4JF39JGJT011347 2018-08-16 12:49:22.493
ME4JF39JGJT011353 2018-08-16 12:48:10.610
ME4JF39JGJT011367 2018-08-16 12:48:57.543
ME4JF39JGJT011370 2018-08-16 12:38:07.503
ME4JF39JGJT011373 2018-08-16 12:48:22.317
ME4JF39JGJT011394 2018-08-16 12:33:12.457
ME4JF39JGJT011400 2018-08-16 12:46:59.320
ME4JF39JGJT011405 2018-08-16 12:50:14.230
ME4JF39JGJT011406 2018-08-16 12:32:26.487
ME4JF39JGJT011407 2018-08-16 12:37:41.817
ME4JF39JGJT011408 2018-08-16 12:32:15.720
ME4JF39JGJT011409 2018-08-16 12:48:34.663
ME4JF39JGJT011410 2018-08-16 12:32:43.490
ME4JF39JGJT011411 2018-08-16 12:33:01.567
ME4JF39JGJT011412 2018-08-16 12:34:34.630
ME4JF39JGJT011413 2018-08-16 12:33:18.223
ME4JF39JGJT011415 2018-08-16 12:33:50.363
ME4JF39JGJT011416 2018-08-16 12:33:07.580
ME4JF39JGJT011417 2018-08-16 12:47:51.753
ME4JF39JGJT011418 2018-08-16 12:37:09.753
ME4JF39JGJT011419 2018-08-16 12:37:15.697
ME4JF39JGJT011423 2018-08-16 12:37:25.063
ME4JF39JGJT011425 2018-08-16 12:37:37.083
ME4JF39JGJT011426 2018-08-16 12:37:31.773
ME4JF39JGJT011427 2018-08-16 12:37:49.160
ME4JF39JGJT011428 2018-08-16 12:38:00.410
ME4JF39JGJT011429 2018-08-16 12:43:04.080
ME4JF39JGJT011430 2018-08-16 12:37:55.323
ME4JF39JGJT011431 2018-08-16 12:38:22.783
ME4JF39JGJT011432 2018-08-16 12:38:18.207
ME4JF39JGJT011433 2018-08-16 12:38:12.427
ME4JF39JGJT011434 2018-08-16 12:47:29.280
ME4JF39JGJT011435 2018-08-16 12:47:42.513
ME4JF39JGJT011436 2018-08-16 12:52:20.077
ME4JF39JGJT011437 2018-08-16 12:46:50.240
ME4JF39JGJT011443 2018-08-16 12:50:58.637
ME4JF39JGJT011444 2018-08-16 12:25:07.663
ME4JF39KGJT021354 2018-08-16 13:05:40.750
ME4JF39KGJT021362 2018-08-16 12:17:38.870
ME4JF39KGJT021364 2018-08-16 12:47:18.993
ME4JF39KGJT021372 2018-08-16 12:18:08.897
ME4JF39KGJT021377 2018-08-16 12:48:39.300
ME4JF39KGJT021384 2018-08-16 12:47:47.220
ME4JF39KGJT021386 2018-08-16 12:25:34.700
ME4JF39KGJT021388 2018-08-16 12:48:52.810
ME4JF39KGJT021397 2018-08-16 12:29:57.287
ME4JF39KGJT021398 2018-08-16 11:29:34.220
ME4JF39KGJT021412 2018-08-16 12:14:36.840
ME4JF39KGJT021420 2018-08-16 12:29:39.367
ME4JF39KGJT021423 2018-08-16 12:16:35.893
ME4JF39KGJT021424 2018-08-16 12:29:43.547
ME4JF39KGJT021425 2018-08-16 12:30:01.567
ME4JF39KGJT021426 2018-08-16 11:30:13.553
ME4JF39KGJT021427 2018-08-16 12:29:48.300
ME4JF39KGJT021428 2018-08-16 12:30:19.863
ME4JF39KGJT021429 2018-08-16 12:29:52.657
ME4JF39KGJT021430 2018-08-16 12:30:10.003
ME4JF39KGJT021431 2018-08-16 11:38:44.177
ME4JF39KGJT021432 2018-08-16 12:30:33.460
ME4JF39KGJT021434 2018-08-16 12:30:26.050
ME4JF39KGJT021435 2018-08-16 12:15:58.357
ME4JF39KGJT021438 2018-08-16 12:26:03.990
ME4JF39KGJT021439 2018-08-16 12:13:01.380
ME4JF39KGJT021440 2018-08-16 12:03:35.790
ME4JF39KGJT021441 2018-08-16 12:22:33.293
ME4JF39KGJT021442 2018-08-16 11:32:37.747
ME4JF39KGJT021443 2018-08-16 11:32:15.900
ME4JF39KGJT021444 2018-08-16 11:35:11.967
ME4JF39KGJT021446 2018-08-16 11:32:52.153
ME4JF39KGJT021447 2018-08-16 11:30:48.090
ME4JF39KGJT021449 2018-08-16 12:20:59.583
ME4JF39KGJT021452 2018-08-16 11:29:48.190
ME4JF39KGJT021453 2018-08-16 11:29:41.440
ME4JF39KGJT021454 2018-08-16 12:23:17.540
ME4JF39KGJT021455 2018-08-16 12:18:21.760
ME4JF39KGJT021461 2018-08-16 12:18:00.293
ME4JF39KGJT021462 2018-08-16 12:18:27.483
ME4JF39KGJT021463 2018-08-16 12:17:45.117
ME4JF39KGJT021466 2018-08-16 12:18:15.113
ME4JF39KGJT021467 2018-08-16 12:25:48.760
ME4JF39KGJT021469 2018-08-16 12:19:27.310
ME4JF39KGJT021471 2018-08-16 12:18:32.743
ME4JF39KGJT021473 2018-08-16 12:19:19.240
ME4JF39KGJT021474 2018-08-16 12:19:06.933
ME4JF39KGJT021475 2018-08-16 12:23:28.230
ME4JF39KGJT021478 2018-08-16 12:19:34.240
ME4JF39KGJT021479 2018-08-16 12:26:38.173
ME4JF39KGJT021481 2018-08-16 12:19:50.300
ME4JF39KGJT021482 2018-08-16 12:21:28.387
ME4JF39KGJT021483 2018-08-16 12:21:05.973
ME4JF39KGJT021484 2018-08-16 12:25:43.087
ME4JF39KGJT021486 2018-08-16 11:29:14.470
ME4JF39KGJT021487 2018-08-16 12:22:09.257
ME4JF39KGJT021488 2018-08-16 12:23:56.220
ME4JF39KGJT021490 2018-08-16 12:23:49.507
ME4JF39KGJT021491 2018-08-16 12:25:27.283
ME4JF39KGJT021492 2018-08-16 12:25:58.003
ME4JF39KGJT021494 2018-08-16 12:26:22.797
ME4JF39KGJT021497 2018-08-16 12:52:16.193
ME4JF39KGJT021498 2018-08-16 12:27:33.660
ME4JF39KGJT021499 2018-08-16 13:01:57.043
ME4JF39KGJT021501 2018-08-16 12:27:39.693
ME4JF39KGJT021504 2018-08-16 12:53:01.293
ME4JF39KGJT021509 2018-08-16 12:51:56.460
ME4JF39KGJT021510 2018-08-16 12:52:11.157
ME4JF39KGJT021511 2018-08-16 12:52:01.523
ME4JF39KGJT021513 2018-08-16 12:27:44.803
ME4JF39KGJT021516 2018-08-16 12:26:08.757
ME4JF39KGJT021517 2018-08-16 12:26:42.980
ME4JF39KGJT021518 2018-08-16 12:26:28.160
ME4JF39KGJT021532 2018-08-16 13:02:29.980
ME4JF39KGJT021538 2018-08-16 13:00:47.727
ME4JF39KGJT021542 2018-08-16 12:46:38.143
ME4JF39KGJT021550 2018-08-16 11:21:27.917
ME4JF39KGJT021557 2018-08-16 12:57:43.287
ME4JF39KGJT021558 2018-08-16 12:24:56.433
ME4JF39KGJT021560 2018-08-16 12:58:39.753
ME4JF39KGJT021561 2018-08-16 12:52:51.077
ME4JF39KGJT021562 2018-08-16 12:57:28.667
ME4JF39KGJT021564 2018-08-16 12:56:58.510
ME4JF39KGJT021565 2018-08-16 12:58:04.210
ME4JF39KGJT021566 2018-08-16 12:53:29.837
ME4JF39KGJT021567 2018-08-16 12:52:32.150
ME4JF39KGJT021568 2018-08-16 12:53:24.850
ME4JF39KGJT021569 2018-08-16 12:52:27.643
ME4JF39KGJT021570 2018-08-16 12:57:20.223
ME4JF39KGJT021571 2018-08-16 12:53:45.243
ME4JF39KGJT021572 2018-08-16 12:25:21.203
ME4JF39KGJT021573 2018-08-16 12:25:01.650
ME4JF39KGJT021575 2018-08-16 12:53:20.980
ME4JF39KGJT021577 2018-08-16 12:52:35.453
ME4JF39KGJT021578 2018-08-16 13:02:25.413
ME4JF39KGJT021580 2018-08-16 12:53:11.620
ME4JF39KGJT021581 2018-08-16 12:45:48.337
ME4JF39KGJT021582 2018-08-16 13:05:21.917
ME4JF39KGJT021584 2018-08-16 13:00:41.867
ME4JF39KGJT021585 2018-08-16 12:53:34.590
ME4JF39KGJT021586 2018-08-16 12:53:40.650
ME4JF39KGJT021587 2018-08-16 13:00:54.730
ME4JF39KGJT021588 2018-08-16 13:00:21.457
ME4JF39KGJT021589 2018-08-16 13:00:28.360
ME4JF39KGJT021591 2018-08-16 13:01:06.947
ME4JF39KGJT021592 2018-08-16 12:51:41.110
ME4JF39KGJT021593 2018-08-16 13:02:31.723
ME4JF39KGJT021594 2018-08-16 13:02:34.067
ME4JF39KGJT021595 2018-08-16 13:01:11.773
ME4JF39KGJT021597 2018-08-16 13:02:38.293
ME4JF39KGJT021598 2018-08-16 13:02:36.473
ME4JF39KGJT021599 2018-08-16 13:02:27.450
ME4JF39KGJT021600 2018-08-16 13:04:37.527
ME4JF39KGJT021601 2018-08-16 13:02:42.420
ME4JF39KGJT021602 2018-08-16 13:02:40.047
ME4JF39KGJT021603 2018-08-16 13:05:20.157
ME4JF39KGJT021604 2018-08-16 13:05:09.630
ME4JF39KGJT021605 2018-08-16 13:05:17.503
ME4JF39KGJT021606 2018-08-16 13:05:11.843
ME4JF39KGJT021607 2018-08-16 13:05:14.000
ME4JF39KGJT021608 2018-08-16 12:46:42.440
ME4JF39KGJT021609 2018-08-16 13:05:25.323
ME4JF39KGJT021610 2018-08-16 13:05:37.200
ME4JF39KGJT021612 2018-08-16 13:05:29.637
ME4JF39KGJT021613 2018-08-16 13:05:26.920
ME4JF39KGJT021614 2018-08-16 13:05:31.503
ME4JF39KGJT021615 2018-08-16 13:05:33.310
ME4JF39KGJT021616 2018-08-16 13:05:39.063
ME4JF39KGJT021617 2018-08-16 13:02:22.700
ME4JF39KGJT021618 2018-08-16 12:46:00.277
ME4JF39KGJT021619 2018-08-16 12:46:46.460
ME4JF39KGJT021620 2018-08-16 13:01:59.257
ME4JF39KGJT021622 2018-08-16 13:05:44.497
ME4JF39KGJT021623 2018-08-16 13:16:06.093
ME4JF39KGJT021624 2018-08-16 13:09:34.527
ME4JF39KGJT021625 2018-08-16 13:16:19.583
ME4JF39KGJT021626 2018-08-16 13:09:02.427
ME4JF39KGJT021628 2018-08-16 12:27:12.850
ME4JF39KGJT021629 2018-08-16 13:13:53.940
ME4JF39KGJT021630 2018-08-16 13:13:40.230
ME4JF39KGJT021631 2018-08-16 14:15:12.827
ME4JF39KGJT021632 2018-08-16 12:26:49.637
ME4JF39KGJT021633 2018-08-16 12:26:59.393
ME4JF39KGJT021634 2018-08-16 12:27:04.823
ME4JF39KGJT021635 2018-08-16 12:26:54.150
ME4JF39KGJT021637 2018-08-16 12:27:22.893
ME4JF39KGJT021639 2018-08-16 14:16:09.507
ME4JF39KGJT021642 2018-08-16 12:45:58.010
ME4JF39KGJT021647 2018-08-16 12:46:04.480
ME4JF39LGJT004941 2018-08-16 13:05:35.230
ME4JF39LGJT004944 2018-08-16 12:48:30.933
ME4JF39LGJT004948 2018-08-16 12:51:26.400
ME4JF39LGJT004955 2018-08-16 12:49:16.207
ME4JF39LGJT004960 2018-08-16 12:51:19.993
ME4JF39LGJT004961 2018-08-16 12:40:53.050
ME4JF39LGJT004962 2018-08-16 12:42:20.123
ME4JF39LGJT004963 2018-08-16 12:41:38.940
ME4JF39LGJT004964 2018-08-16 12:48:26.393
ME4JF39LGJT004965 2018-08-16 12:45:40.180
ME4JF39LGJT004971 2018-08-16 12:42:25.570
ME4JF39LGJT004973 2018-08-16 12:42:00.537
ME4JF39LGJT004975 2018-08-16 12:46:54.520
ME4JF39LGJT004976 2018-08-16 12:41:07.600
ME4JF39LGJT004977 2018-08-16 12:42:53.910
ME4JF39LGJT004978 2018-08-16 12:41:51.473
ME4JF39LGJT004979 2018-08-16 12:42:48.970
ME4JF39LGJT004980 2018-08-16 12:41:26.520
ME4JF39LGJT004981 2018-08-16 12:42:59.330
ME4JF39LGJT004982 2018-08-16 12:41:46.800
ME4JF39LGJT004983 2018-08-16 12:45:04.287
ME4JF39LGJT004984 2018-08-16 12:42:14.223
ME4JF39LGJT004985 2018-08-16 12:28:11.770
ME4JF39LGJT004986 2018-08-16 12:51:31.970
ME4JF39LGJT004987 2018-08-16 12:47:36.343
ME4JF39LGJT004988 2018-08-16 12:42:33.300
ME4JF39LGJT004989 2018-08-16 12:42:06.740
ME4JF39LGJT004990 2018-08-16 12:43:24.860
ME4JF39LGJT004994 2018-08-16 12:42:43.567
ME4JF39LGJT004995 2018-08-16 12:43:12.427
ME4JF39LGJT004996 2018-08-16 12:45:36.163
ME4JF39LGJT004997 2018-08-16 12:46:08.607
ME4JF39LGJT004998 2018-08-16 12:39:02.707
ME4JF39LGJT004999 2018-08-16 12:43:18.433
ME4JF39LGJT005000 2018-08-16 12:48:43.570
ME4JF39LGJT005001 2018-08-16 12:45:20.320
ME4JF39LGJT005003 2018-08-16 12:27:51.207
ME4JF39LGJT005004 2018-08-16 12:45:24.617
ME4JF39LGJT005007 2018-08-16 12:39:13.550
ME4JF39LGJT005009 2018-08-16 12:27:56.553
ME4JF39LGJT005011 2018-08-16 12:38:56.800
ME4JF39LGJT005012 2018-08-16 12:50:53.890
ME4JF39LGJT005013 2018-08-16 12:38:27.473
ME4JF39LGJT005014 2018-08-16 12:38:33.690
ME4JF39LGJT005015 2018-08-16 12:47:04.227
ME4JF39LGJT005016 2018-08-16 12:39:07.973
ME4JF39LGJT005017 2018-08-16 12:39:32.520
ME4JF39LGJT005018 2018-08-16 12:40:06.283
ME4JF39LGJT005019 2018-08-16 12:39:44.737
ME4JF39LGJT005020 2018-08-16 12:39:18.817
ME4JF50AGJT341071 2018-08-16 11:33:59.723
ME4JF50AGJT344724 2018-08-16 13:30:00.280
ME4JF50AGJT344816 2018-08-16 13:28:37.483
ME4JF50AGJT344818 2018-08-16 13:29:28.207
ME4JF50AGJT344824 2018-08-16 13:28:08.810
ME4JF50AGJT344826 2018-08-16 13:29:41.537
ME4JF50AGJT344830 2018-08-16 13:27:57.753
ME4JF50AGJT344873 2018-08-16 13:28:50.010
ME4JF50AGJT344882 2018-08-16 13:27:32.163
ME4JF50AGJT344886 2018-08-16 13:27:43.630
ME4JF50BGJT157862 2018-08-16 13:20:02.167
ME4JF50BGJT157891 2018-08-16 13:22:06.110
ME4JF50BGJT157909 2018-08-16 13:18:54.710
ME4JF50BGJT157910 2018-08-16 13:26:59.917
ME4JF50BGJT157934 2018-08-16 13:23:02.110
ME4JF50BGJT157962 2018-08-16 13:22:22.743
ME4JF50BGJT157965 2018-08-16 13:27:18.143
ME4JF50BGJT157997 2018-08-16 13:22:47.020
ME4JF50BGJT158010 2018-08-16 13:27:08.810
ME4JF50BGJT158014 2018-08-16 13:21:02.943
ME4JF50BGJT158018 2018-08-16 13:20:52.230
ME4JF50BGJT158119 2018-08-16 12:24:18.903
ME4JF50BGJT158138 2018-08-16 12:45:29.070
P1022734-004 2018-08-28 15:23:44.017
I need to print on date 16 count is 300, on 28 count is 2 and rest 1,2...31 it should display 0.
sql
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
add a comment |
-3
I have written a query to get the number of vehicles in a particular day of specific month. Here is my query,
SELECT CASE WHEN DATEPART(DAY, timestamp) = '1' THEN (select count(Vehicle_Id)) ELSE '0' END AS [1],
CASE WHEN DATEPART(DAY, timestamp) = '2' THEN (select count(Vehicle_Id)) ELSE '0' END AS [2],
CASE WHEN DATEPART(DAY, timestamp) = '3' THEN (select count(Vehicle_Id)) ELSE '0' END AS [3],
CASE WHEN DATEPART(DAY, timestamp) = '4' THEN (select count(Vehicle_Id)) ELSE '0' END AS [4],
CASE WHEN DATEPART(DAY, timestamp) = '5' THEN (select count(Vehicle_Id)) ELSE '0' END AS [5],
CASE WHEN DATEPART(DAY, timestamp) = '6' THEN (select count(Vehicle_Id)) ELSE '0' END AS [6],
CASE WHEN DATEPART(DAY, timestamp) = '7' THEN (select count(Vehicle_Id)) ELSE '0' END AS [7],
CASE WHEN DATEPART(DAY, timestamp) = '8' THEN (select count(Vehicle_Id)) ELSE '0' END AS [8],
CASE WHEN DATEPART(DAY, timestamp) = '9' THEN (select count(Vehicle_Id)) ELSE '0' END AS [9],
CASE WHEN DATEPART(DAY, timestamp) = '10' THEN (select count(Vehicle_Id)) ELSE '0' END AS [10],
CASE WHEN DATEPART(DAY, timestamp) = '11' THEN (select count(Vehicle_Id)) ELSE '0' END AS [11],
CASE WHEN DATEPART(DAY, timestamp) = '12' THEN (select count(Vehicle_Id)) ELSE '0' END AS [12],
CASE WHEN DATEPART(DAY, timestamp) = '13' THEN (select count(Vehicle_Id)) ELSE '0' END AS [13],
CASE WHEN DATEPART(DAY, timestamp) = '14' THEN (select count(Vehicle_Id)) ELSE '0' END AS [14],
CASE WHEN DATEPART(DAY, timestamp) = '15' THEN (select count(Vehicle_Id)) ELSE '0' END AS [15],
CASE WHEN DATEPART(DAY, timestamp) = '16' THEN (select count(Vehicle_Id)) ELSE '0' END AS [16],
CASE WHEN DATEPART(DAY, timestamp) = '17' THEN (select count(Vehicle_Id)) ELSE '0' END AS [17],
CASE WHEN DATEPART(DAY, timestamp) = '18' THEN (select count(Vehicle_Id)) ELSE '0' END AS [18],
CASE WHEN DATEPART(DAY, timestamp) = '19' THEN (select count(Vehicle_Id)) ELSE '0' END AS [19],
CASE WHEN DATEPART(DAY, timestamp) = '20' THEN (select count(Vehicle_Id)) ELSE '0' END AS [20],
CASE WHEN DATEPART(DAY, timestamp) = '21' THEN (select count(Vehicle_Id)) ELSE '0' END AS [21],
CASE WHEN DATEPART(DAY, timestamp) = '22' THEN (select count(Vehicle_Id)) ELSE '0' END AS [22],
CASE WHEN DATEPART(DAY, timestamp) = '23' THEN (select count(Vehicle_Id)) ELSE '0' END AS [23],
CASE WHEN DATEPART(DAY, timestamp) = '24' THEN (select count(Vehicle_Id)) ELSE '0' END AS [24],
CASE WHEN DATEPART(DAY, timestamp) = '25' THEN (select count(Vehicle_Id)) ELSE '0' END AS [25],
CASE WHEN DATEPART(DAY, timestamp) = '26' THEN (select count(Vehicle_Id)) ELSE '0' END AS [26],
CASE WHEN DATEPART(DAY, timestamp) = '27' THEN (select count(Vehicle_Id)) ELSE '0' END AS [27],
CASE WHEN DATEPART(DAY, timestamp) = '28' THEN (select count(Vehicle_Id)) ELSE '0' END AS [28],
CASE WHEN DATEPART(DAY, timestamp) = '29' THEN (select count(Vehicle_Id)) ELSE '0' END AS [29],
CASE WHEN DATEPART(DAY, timestamp) = '30' THEN (select count(Vehicle_Id)) ELSE '0' END AS [30],
CASE WHEN DATEPART(DAY, timestamp) = '31' THEN (select count(Vehicle_Id)) ELSE '0' END AS [31]
FROM HVQ_Vehicle
where month(timestamp) = '08'
GROUP BY day(timestamp);
This gives result in two rows instead of single row. How to obtain in a single row. Here is the sample data.
Vehicle_Id timestamp
8901361307130 2018-08-28 15:22:29.687
ME4JF39JGJT011230 2018-08-16 12:29:10.800
ME4JF39JGJT011231 2018-08-16 12:28:34.473
ME4JF39JGJT011232 2018-08-16 12:28:44.930
ME4JF39JGJT011233 2018-08-16 12:28:51.747
ME4JF39JGJT011234 2018-08-16 12:28:59.653
ME4JF39JGJT011235 2018-08-16 12:49:56.463
ME4JF39JGJT011236 2018-08-16 12:29:20.683
ME4JF39JGJT011237 2018-08-16 12:29:15.917
ME4JF39JGJT011239 2018-08-16 12:29:30.487
ME4JF39JGJT011240 2018-08-16 12:51:02.883
ME4JF39JGJT011241 2018-08-16 12:47:25.167
ME4JF39JGJT011242 2018-08-16 12:29:25.900
ME4JF39JGJT011243 2018-08-16 12:50:39.793
ME4JF39JGJT011244 2018-08-16 11:39:32.040
ME4JF39JGJT011245 2018-08-16 12:29:34.940
ME4JF39JGJT011246 2018-08-16 12:50:48.110
ME4JF39JGJT011248 2018-08-16 11:56:47.163
ME4JF39JGJT011250 2018-08-16 12:17:24.847
ME4JF39JGJT011252 2018-08-16 12:24:01.873
ME4JF39JGJT011253 2018-08-16 12:17:20.093
ME4JF39JGJT011254 2018-08-16 12:51:15.540
ME4JF39JGJT011255 2018-08-16 12:49:31.407
ME4JF39JGJT011257 2018-08-16 12:50:34.420
ME4JF39JGJT011260 2018-08-16 12:24:07.197
ME4JF39JGJT011262 2018-08-16 12:50:23.643
ME4JF39JGJT011263 2018-08-16 12:17:32.140
ME4JF39JGJT011264 2018-08-16 12:24:12.553
ME4JF39JGJT011265 2018-08-16 12:24:25.897
ME4JF39JGJT011266 2018-08-16 12:24:50.730
ME4JF39JGJT011267 2018-08-16 12:24:31.730
ME4JF39JGJT011271 2018-08-16 12:24:34.087
ME4JF39JGJT011275 2018-08-16 12:51:10.437
ME4JF39JGJT011288 2018-08-16 12:47:55.863
ME4JF39JGJT011293 2018-08-16 12:28:04.703
ME4JF39JGJT011294 2018-08-16 12:48:00.583
ME4JF39JGJT011316 2018-08-16 12:32:49.480
ME4JF39JGJT011324 2018-08-16 12:33:28.817
ME4JF39JGJT011327 2018-08-16 12:50:00.590
ME4JF39JGJT011330 2018-08-16 12:50:04.307
ME4JF39JGJT011336 2018-08-16 12:47:12.133
ME4JF39JGJT011340 2018-08-16 12:48:04.950
ME4JF39JGJT011347 2018-08-16 12:49:22.493
ME4JF39JGJT011353 2018-08-16 12:48:10.610
ME4JF39JGJT011367 2018-08-16 12:48:57.543
ME4JF39JGJT011370 2018-08-16 12:38:07.503
ME4JF39JGJT011373 2018-08-16 12:48:22.317
ME4JF39JGJT011394 2018-08-16 12:33:12.457
ME4JF39JGJT011400 2018-08-16 12:46:59.320
ME4JF39JGJT011405 2018-08-16 12:50:14.230
ME4JF39JGJT011406 2018-08-16 12:32:26.487
ME4JF39JGJT011407 2018-08-16 12:37:41.817
ME4JF39JGJT011408 2018-08-16 12:32:15.720
ME4JF39JGJT011409 2018-08-16 12:48:34.663
ME4JF39JGJT011410 2018-08-16 12:32:43.490
ME4JF39JGJT011411 2018-08-16 12:33:01.567
ME4JF39JGJT011412 2018-08-16 12:34:34.630
ME4JF39JGJT011413 2018-08-16 12:33:18.223
ME4JF39JGJT011415 2018-08-16 12:33:50.363
ME4JF39JGJT011416 2018-08-16 12:33:07.580
ME4JF39JGJT011417 2018-08-16 12:47:51.753
ME4JF39JGJT011418 2018-08-16 12:37:09.753
ME4JF39JGJT011419 2018-08-16 12:37:15.697
ME4JF39JGJT011423 2018-08-16 12:37:25.063
ME4JF39JGJT011425 2018-08-16 12:37:37.083
ME4JF39JGJT011426 2018-08-16 12:37:31.773
ME4JF39JGJT011427 2018-08-16 12:37:49.160
ME4JF39JGJT011428 2018-08-16 12:38:00.410
ME4JF39JGJT011429 2018-08-16 12:43:04.080
ME4JF39JGJT011430 2018-08-16 12:37:55.323
ME4JF39JGJT011431 2018-08-16 12:38:22.783
ME4JF39JGJT011432 2018-08-16 12:38:18.207
ME4JF39JGJT011433 2018-08-16 12:38:12.427
ME4JF39JGJT011434 2018-08-16 12:47:29.280
ME4JF39JGJT011435 2018-08-16 12:47:42.513
ME4JF39JGJT011436 2018-08-16 12:52:20.077
ME4JF39JGJT011437 2018-08-16 12:46:50.240
ME4JF39JGJT011443 2018-08-16 12:50:58.637
ME4JF39JGJT011444 2018-08-16 12:25:07.663
ME4JF39KGJT021354 2018-08-16 13:05:40.750
ME4JF39KGJT021362 2018-08-16 12:17:38.870
ME4JF39KGJT021364 2018-08-16 12:47:18.993
ME4JF39KGJT021372 2018-08-16 12:18:08.897
ME4JF39KGJT021377 2018-08-16 12:48:39.300
ME4JF39KGJT021384 2018-08-16 12:47:47.220
ME4JF39KGJT021386 2018-08-16 12:25:34.700
ME4JF39KGJT021388 2018-08-16 12:48:52.810
ME4JF39KGJT021397 2018-08-16 12:29:57.287
ME4JF39KGJT021398 2018-08-16 11:29:34.220
ME4JF39KGJT021412 2018-08-16 12:14:36.840
ME4JF39KGJT021420 2018-08-16 12:29:39.367
ME4JF39KGJT021423 2018-08-16 12:16:35.893
ME4JF39KGJT021424 2018-08-16 12:29:43.547
ME4JF39KGJT021425 2018-08-16 12:30:01.567
ME4JF39KGJT021426 2018-08-16 11:30:13.553
ME4JF39KGJT021427 2018-08-16 12:29:48.300
ME4JF39KGJT021428 2018-08-16 12:30:19.863
ME4JF39KGJT021429 2018-08-16 12:29:52.657
ME4JF39KGJT021430 2018-08-16 12:30:10.003
ME4JF39KGJT021431 2018-08-16 11:38:44.177
ME4JF39KGJT021432 2018-08-16 12:30:33.460
ME4JF39KGJT021434 2018-08-16 12:30:26.050
ME4JF39KGJT021435 2018-08-16 12:15:58.357
ME4JF39KGJT021438 2018-08-16 12:26:03.990
ME4JF39KGJT021439 2018-08-16 12:13:01.380
ME4JF39KGJT021440 2018-08-16 12:03:35.790
ME4JF39KGJT021441 2018-08-16 12:22:33.293
ME4JF39KGJT021442 2018-08-16 11:32:37.747
ME4JF39KGJT021443 2018-08-16 11:32:15.900
ME4JF39KGJT021444 2018-08-16 11:35:11.967
ME4JF39KGJT021446 2018-08-16 11:32:52.153
ME4JF39KGJT021447 2018-08-16 11:30:48.090
ME4JF39KGJT021449 2018-08-16 12:20:59.583
ME4JF39KGJT021452 2018-08-16 11:29:48.190
ME4JF39KGJT021453 2018-08-16 11:29:41.440
ME4JF39KGJT021454 2018-08-16 12:23:17.540
ME4JF39KGJT021455 2018-08-16 12:18:21.760
ME4JF39KGJT021461 2018-08-16 12:18:00.293
ME4JF39KGJT021462 2018-08-16 12:18:27.483
ME4JF39KGJT021463 2018-08-16 12:17:45.117
ME4JF39KGJT021466 2018-08-16 12:18:15.113
ME4JF39KGJT021467 2018-08-16 12:25:48.760
ME4JF39KGJT021469 2018-08-16 12:19:27.310
ME4JF39KGJT021471 2018-08-16 12:18:32.743
ME4JF39KGJT021473 2018-08-16 12:19:19.240
ME4JF39KGJT021474 2018-08-16 12:19:06.933
ME4JF39KGJT021475 2018-08-16 12:23:28.230
ME4JF39KGJT021478 2018-08-16 12:19:34.240
ME4JF39KGJT021479 2018-08-16 12:26:38.173
ME4JF39KGJT021481 2018-08-16 12:19:50.300
ME4JF39KGJT021482 2018-08-16 12:21:28.387
ME4JF39KGJT021483 2018-08-16 12:21:05.973
ME4JF39KGJT021484 2018-08-16 12:25:43.087
ME4JF39KGJT021486 2018-08-16 11:29:14.470
ME4JF39KGJT021487 2018-08-16 12:22:09.257
ME4JF39KGJT021488 2018-08-16 12:23:56.220
ME4JF39KGJT021490 2018-08-16 12:23:49.507
ME4JF39KGJT021491 2018-08-16 12:25:27.283
ME4JF39KGJT021492 2018-08-16 12:25:58.003
ME4JF39KGJT021494 2018-08-16 12:26:22.797
ME4JF39KGJT021497 2018-08-16 12:52:16.193
ME4JF39KGJT021498 2018-08-16 12:27:33.660
ME4JF39KGJT021499 2018-08-16 13:01:57.043
ME4JF39KGJT021501 2018-08-16 12:27:39.693
ME4JF39KGJT021504 2018-08-16 12:53:01.293
ME4JF39KGJT021509 2018-08-16 12:51:56.460
ME4JF39KGJT021510 2018-08-16 12:52:11.157
ME4JF39KGJT021511 2018-08-16 12:52:01.523
ME4JF39KGJT021513 2018-08-16 12:27:44.803
ME4JF39KGJT021516 2018-08-16 12:26:08.757
ME4JF39KGJT021517 2018-08-16 12:26:42.980
ME4JF39KGJT021518 2018-08-16 12:26:28.160
ME4JF39KGJT021532 2018-08-16 13:02:29.980
ME4JF39KGJT021538 2018-08-16 13:00:47.727
ME4JF39KGJT021542 2018-08-16 12:46:38.143
ME4JF39KGJT021550 2018-08-16 11:21:27.917
ME4JF39KGJT021557 2018-08-16 12:57:43.287
ME4JF39KGJT021558 2018-08-16 12:24:56.433
ME4JF39KGJT021560 2018-08-16 12:58:39.753
ME4JF39KGJT021561 2018-08-16 12:52:51.077
ME4JF39KGJT021562 2018-08-16 12:57:28.667
ME4JF39KGJT021564 2018-08-16 12:56:58.510
ME4JF39KGJT021565 2018-08-16 12:58:04.210
ME4JF39KGJT021566 2018-08-16 12:53:29.837
ME4JF39KGJT021567 2018-08-16 12:52:32.150
ME4JF39KGJT021568 2018-08-16 12:53:24.850
ME4JF39KGJT021569 2018-08-16 12:52:27.643
ME4JF39KGJT021570 2018-08-16 12:57:20.223
ME4JF39KGJT021571 2018-08-16 12:53:45.243
ME4JF39KGJT021572 2018-08-16 12:25:21.203
ME4JF39KGJT021573 2018-08-16 12:25:01.650
ME4JF39KGJT021575 2018-08-16 12:53:20.980
ME4JF39KGJT021577 2018-08-16 12:52:35.453
ME4JF39KGJT021578 2018-08-16 13:02:25.413
ME4JF39KGJT021580 2018-08-16 12:53:11.620
ME4JF39KGJT021581 2018-08-16 12:45:48.337
ME4JF39KGJT021582 2018-08-16 13:05:21.917
ME4JF39KGJT021584 2018-08-16 13:00:41.867
ME4JF39KGJT021585 2018-08-16 12:53:34.590
ME4JF39KGJT021586 2018-08-16 12:53:40.650
ME4JF39KGJT021587 2018-08-16 13:00:54.730
ME4JF39KGJT021588 2018-08-16 13:00:21.457
ME4JF39KGJT021589 2018-08-16 13:00:28.360
ME4JF39KGJT021591 2018-08-16 13:01:06.947
ME4JF39KGJT021592 2018-08-16 12:51:41.110
ME4JF39KGJT021593 2018-08-16 13:02:31.723
ME4JF39KGJT021594 2018-08-16 13:02:34.067
ME4JF39KGJT021595 2018-08-16 13:01:11.773
ME4JF39KGJT021597 2018-08-16 13:02:38.293
ME4JF39KGJT021598 2018-08-16 13:02:36.473
ME4JF39KGJT021599 2018-08-16 13:02:27.450
ME4JF39KGJT021600 2018-08-16 13:04:37.527
ME4JF39KGJT021601 2018-08-16 13:02:42.420
ME4JF39KGJT021602 2018-08-16 13:02:40.047
ME4JF39KGJT021603 2018-08-16 13:05:20.157
ME4JF39KGJT021604 2018-08-16 13:05:09.630
ME4JF39KGJT021605 2018-08-16 13:05:17.503
ME4JF39KGJT021606 2018-08-16 13:05:11.843
ME4JF39KGJT021607 2018-08-16 13:05:14.000
ME4JF39KGJT021608 2018-08-16 12:46:42.440
ME4JF39KGJT021609 2018-08-16 13:05:25.323
ME4JF39KGJT021610 2018-08-16 13:05:37.200
ME4JF39KGJT021612 2018-08-16 13:05:29.637
ME4JF39KGJT021613 2018-08-16 13:05:26.920
ME4JF39KGJT021614 2018-08-16 13:05:31.503
ME4JF39KGJT021615 2018-08-16 13:05:33.310
ME4JF39KGJT021616 2018-08-16 13:05:39.063
ME4JF39KGJT021617 2018-08-16 13:02:22.700
ME4JF39KGJT021618 2018-08-16 12:46:00.277
ME4JF39KGJT021619 2018-08-16 12:46:46.460
ME4JF39KGJT021620 2018-08-16 13:01:59.257
ME4JF39KGJT021622 2018-08-16 13:05:44.497
ME4JF39KGJT021623 2018-08-16 13:16:06.093
ME4JF39KGJT021624 2018-08-16 13:09:34.527
ME4JF39KGJT021625 2018-08-16 13:16:19.583
ME4JF39KGJT021626 2018-08-16 13:09:02.427
ME4JF39KGJT021628 2018-08-16 12:27:12.850
ME4JF39KGJT021629 2018-08-16 13:13:53.940
ME4JF39KGJT021630 2018-08-16 13:13:40.230
ME4JF39KGJT021631 2018-08-16 14:15:12.827
ME4JF39KGJT021632 2018-08-16 12:26:49.637
ME4JF39KGJT021633 2018-08-16 12:26:59.393
ME4JF39KGJT021634 2018-08-16 12:27:04.823
ME4JF39KGJT021635 2018-08-16 12:26:54.150
ME4JF39KGJT021637 2018-08-16 12:27:22.893
ME4JF39KGJT021639 2018-08-16 14:16:09.507
ME4JF39KGJT021642 2018-08-16 12:45:58.010
ME4JF39KGJT021647 2018-08-16 12:46:04.480
ME4JF39LGJT004941 2018-08-16 13:05:35.230
ME4JF39LGJT004944 2018-08-16 12:48:30.933
ME4JF39LGJT004948 2018-08-16 12:51:26.400
ME4JF39LGJT004955 2018-08-16 12:49:16.207
ME4JF39LGJT004960 2018-08-16 12:51:19.993
ME4JF39LGJT004961 2018-08-16 12:40:53.050
ME4JF39LGJT004962 2018-08-16 12:42:20.123
ME4JF39LGJT004963 2018-08-16 12:41:38.940
ME4JF39LGJT004964 2018-08-16 12:48:26.393
ME4JF39LGJT004965 2018-08-16 12:45:40.180
ME4JF39LGJT004971 2018-08-16 12:42:25.570
ME4JF39LGJT004973 2018-08-16 12:42:00.537
ME4JF39LGJT004975 2018-08-16 12:46:54.520
ME4JF39LGJT004976 2018-08-16 12:41:07.600
ME4JF39LGJT004977 2018-08-16 12:42:53.910
ME4JF39LGJT004978 2018-08-16 12:41:51.473
ME4JF39LGJT004979 2018-08-16 12:42:48.970
ME4JF39LGJT004980 2018-08-16 12:41:26.520
ME4JF39LGJT004981 2018-08-16 12:42:59.330
ME4JF39LGJT004982 2018-08-16 12:41:46.800
ME4JF39LGJT004983 2018-08-16 12:45:04.287
ME4JF39LGJT004984 2018-08-16 12:42:14.223
ME4JF39LGJT004985 2018-08-16 12:28:11.770
ME4JF39LGJT004986 2018-08-16 12:51:31.970
ME4JF39LGJT004987 2018-08-16 12:47:36.343
ME4JF39LGJT004988 2018-08-16 12:42:33.300
ME4JF39LGJT004989 2018-08-16 12:42:06.740
ME4JF39LGJT004990 2018-08-16 12:43:24.860
ME4JF39LGJT004994 2018-08-16 12:42:43.567
ME4JF39LGJT004995 2018-08-16 12:43:12.427
ME4JF39LGJT004996 2018-08-16 12:45:36.163
ME4JF39LGJT004997 2018-08-16 12:46:08.607
ME4JF39LGJT004998 2018-08-16 12:39:02.707
ME4JF39LGJT004999 2018-08-16 12:43:18.433
ME4JF39LGJT005000 2018-08-16 12:48:43.570
ME4JF39LGJT005001 2018-08-16 12:45:20.320
ME4JF39LGJT005003 2018-08-16 12:27:51.207
ME4JF39LGJT005004 2018-08-16 12:45:24.617
ME4JF39LGJT005007 2018-08-16 12:39:13.550
ME4JF39LGJT005009 2018-08-16 12:27:56.553
ME4JF39LGJT005011 2018-08-16 12:38:56.800
ME4JF39LGJT005012 2018-08-16 12:50:53.890
ME4JF39LGJT005013 2018-08-16 12:38:27.473
ME4JF39LGJT005014 2018-08-16 12:38:33.690
ME4JF39LGJT005015 2018-08-16 12:47:04.227
ME4JF39LGJT005016 2018-08-16 12:39:07.973
ME4JF39LGJT005017 2018-08-16 12:39:32.520
ME4JF39LGJT005018 2018-08-16 12:40:06.283
ME4JF39LGJT005019 2018-08-16 12:39:44.737
ME4JF39LGJT005020 2018-08-16 12:39:18.817
ME4JF50AGJT341071 2018-08-16 11:33:59.723
ME4JF50AGJT344724 2018-08-16 13:30:00.280
ME4JF50AGJT344816 2018-08-16 13:28:37.483
ME4JF50AGJT344818 2018-08-16 13:29:28.207
ME4JF50AGJT344824 2018-08-16 13:28:08.810
ME4JF50AGJT344826 2018-08-16 13:29:41.537
ME4JF50AGJT344830 2018-08-16 13:27:57.753
ME4JF50AGJT344873 2018-08-16 13:28:50.010
ME4JF50AGJT344882 2018-08-16 13:27:32.163
ME4JF50AGJT344886 2018-08-16 13:27:43.630
ME4JF50BGJT157862 2018-08-16 13:20:02.167
ME4JF50BGJT157891 2018-08-16 13:22:06.110
ME4JF50BGJT157909 2018-08-16 13:18:54.710
ME4JF50BGJT157910 2018-08-16 13:26:59.917
ME4JF50BGJT157934 2018-08-16 13:23:02.110
ME4JF50BGJT157962 2018-08-16 13:22:22.743
ME4JF50BGJT157965 2018-08-16 13:27:18.143
ME4JF50BGJT157997 2018-08-16 13:22:47.020
ME4JF50BGJT158010 2018-08-16 13:27:08.810
ME4JF50BGJT158014 2018-08-16 13:21:02.943
ME4JF50BGJT158018 2018-08-16 13:20:52.230
ME4JF50BGJT158119 2018-08-16 12:24:18.903
ME4JF50BGJT158138 2018-08-16 12:45:29.070
P1022734-004 2018-08-28 15:23:44.017
I need to print on date 16 count is 300, on 28 count is 2 and rest 1,2...31 it should display 0.
sql
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
1
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09
add a comment |
-3
-3
-3
I have written a query to get the number of vehicles in a particular day of specific month. Here is my query,
SELECT CASE WHEN DATEPART(DAY, timestamp) = '1' THEN (select count(Vehicle_Id)) ELSE '0' END AS [1],
CASE WHEN DATEPART(DAY, timestamp) = '2' THEN (select count(Vehicle_Id)) ELSE '0' END AS [2],
CASE WHEN DATEPART(DAY, timestamp) = '3' THEN (select count(Vehicle_Id)) ELSE '0' END AS [3],
CASE WHEN DATEPART(DAY, timestamp) = '4' THEN (select count(Vehicle_Id)) ELSE '0' END AS [4],
CASE WHEN DATEPART(DAY, timestamp) = '5' THEN (select count(Vehicle_Id)) ELSE '0' END AS [5],
CASE WHEN DATEPART(DAY, timestamp) = '6' THEN (select count(Vehicle_Id)) ELSE '0' END AS [6],
CASE WHEN DATEPART(DAY, timestamp) = '7' THEN (select count(Vehicle_Id)) ELSE '0' END AS [7],
CASE WHEN DATEPART(DAY, timestamp) = '8' THEN (select count(Vehicle_Id)) ELSE '0' END AS [8],
CASE WHEN DATEPART(DAY, timestamp) = '9' THEN (select count(Vehicle_Id)) ELSE '0' END AS [9],
CASE WHEN DATEPART(DAY, timestamp) = '10' THEN (select count(Vehicle_Id)) ELSE '0' END AS [10],
CASE WHEN DATEPART(DAY, timestamp) = '11' THEN (select count(Vehicle_Id)) ELSE '0' END AS [11],
CASE WHEN DATEPART(DAY, timestamp) = '12' THEN (select count(Vehicle_Id)) ELSE '0' END AS [12],
CASE WHEN DATEPART(DAY, timestamp) = '13' THEN (select count(Vehicle_Id)) ELSE '0' END AS [13],
CASE WHEN DATEPART(DAY, timestamp) = '14' THEN (select count(Vehicle_Id)) ELSE '0' END AS [14],
CASE WHEN DATEPART(DAY, timestamp) = '15' THEN (select count(Vehicle_Id)) ELSE '0' END AS [15],
CASE WHEN DATEPART(DAY, timestamp) = '16' THEN (select count(Vehicle_Id)) ELSE '0' END AS [16],
CASE WHEN DATEPART(DAY, timestamp) = '17' THEN (select count(Vehicle_Id)) ELSE '0' END AS [17],
CASE WHEN DATEPART(DAY, timestamp) = '18' THEN (select count(Vehicle_Id)) ELSE '0' END AS [18],
CASE WHEN DATEPART(DAY, timestamp) = '19' THEN (select count(Vehicle_Id)) ELSE '0' END AS [19],
CASE WHEN DATEPART(DAY, timestamp) = '20' THEN (select count(Vehicle_Id)) ELSE '0' END AS [20],
CASE WHEN DATEPART(DAY, timestamp) = '21' THEN (select count(Vehicle_Id)) ELSE '0' END AS [21],
CASE WHEN DATEPART(DAY, timestamp) = '22' THEN (select count(Vehicle_Id)) ELSE '0' END AS [22],
CASE WHEN DATEPART(DAY, timestamp) = '23' THEN (select count(Vehicle_Id)) ELSE '0' END AS [23],
CASE WHEN DATEPART(DAY, timestamp) = '24' THEN (select count(Vehicle_Id)) ELSE '0' END AS [24],
CASE WHEN DATEPART(DAY, timestamp) = '25' THEN (select count(Vehicle_Id)) ELSE '0' END AS [25],
CASE WHEN DATEPART(DAY, timestamp) = '26' THEN (select count(Vehicle_Id)) ELSE '0' END AS [26],
CASE WHEN DATEPART(DAY, timestamp) = '27' THEN (select count(Vehicle_Id)) ELSE '0' END AS [27],
CASE WHEN DATEPART(DAY, timestamp) = '28' THEN (select count(Vehicle_Id)) ELSE '0' END AS [28],
CASE WHEN DATEPART(DAY, timestamp) = '29' THEN (select count(Vehicle_Id)) ELSE '0' END AS [29],
CASE WHEN DATEPART(DAY, timestamp) = '30' THEN (select count(Vehicle_Id)) ELSE '0' END AS [30],
CASE WHEN DATEPART(DAY, timestamp) = '31' THEN (select count(Vehicle_Id)) ELSE '0' END AS [31]
FROM HVQ_Vehicle
where month(timestamp) = '08'
GROUP BY day(timestamp);
This gives result in two rows instead of single row. How to obtain in a single row. Here is the sample data.
Vehicle_Id timestamp
8901361307130 2018-08-28 15:22:29.687
ME4JF39JGJT011230 2018-08-16 12:29:10.800
ME4JF39JGJT011231 2018-08-16 12:28:34.473
ME4JF39JGJT011232 2018-08-16 12:28:44.930
ME4JF39JGJT011233 2018-08-16 12:28:51.747
ME4JF39JGJT011234 2018-08-16 12:28:59.653
ME4JF39JGJT011235 2018-08-16 12:49:56.463
ME4JF39JGJT011236 2018-08-16 12:29:20.683
ME4JF39JGJT011237 2018-08-16 12:29:15.917
ME4JF39JGJT011239 2018-08-16 12:29:30.487
ME4JF39JGJT011240 2018-08-16 12:51:02.883
ME4JF39JGJT011241 2018-08-16 12:47:25.167
ME4JF39JGJT011242 2018-08-16 12:29:25.900
ME4JF39JGJT011243 2018-08-16 12:50:39.793
ME4JF39JGJT011244 2018-08-16 11:39:32.040
ME4JF39JGJT011245 2018-08-16 12:29:34.940
ME4JF39JGJT011246 2018-08-16 12:50:48.110
ME4JF39JGJT011248 2018-08-16 11:56:47.163
ME4JF39JGJT011250 2018-08-16 12:17:24.847
ME4JF39JGJT011252 2018-08-16 12:24:01.873
ME4JF39JGJT011253 2018-08-16 12:17:20.093
ME4JF39JGJT011254 2018-08-16 12:51:15.540
ME4JF39JGJT011255 2018-08-16 12:49:31.407
ME4JF39JGJT011257 2018-08-16 12:50:34.420
ME4JF39JGJT011260 2018-08-16 12:24:07.197
ME4JF39JGJT011262 2018-08-16 12:50:23.643
ME4JF39JGJT011263 2018-08-16 12:17:32.140
ME4JF39JGJT011264 2018-08-16 12:24:12.553
ME4JF39JGJT011265 2018-08-16 12:24:25.897
ME4JF39JGJT011266 2018-08-16 12:24:50.730
ME4JF39JGJT011267 2018-08-16 12:24:31.730
ME4JF39JGJT011271 2018-08-16 12:24:34.087
ME4JF39JGJT011275 2018-08-16 12:51:10.437
ME4JF39JGJT011288 2018-08-16 12:47:55.863
ME4JF39JGJT011293 2018-08-16 12:28:04.703
ME4JF39JGJT011294 2018-08-16 12:48:00.583
ME4JF39JGJT011316 2018-08-16 12:32:49.480
ME4JF39JGJT011324 2018-08-16 12:33:28.817
ME4JF39JGJT011327 2018-08-16 12:50:00.590
ME4JF39JGJT011330 2018-08-16 12:50:04.307
ME4JF39JGJT011336 2018-08-16 12:47:12.133
ME4JF39JGJT011340 2018-08-16 12:48:04.950
ME4JF39JGJT011347 2018-08-16 12:49:22.493
ME4JF39JGJT011353 2018-08-16 12:48:10.610
ME4JF39JGJT011367 2018-08-16 12:48:57.543
ME4JF39JGJT011370 2018-08-16 12:38:07.503
ME4JF39JGJT011373 2018-08-16 12:48:22.317
ME4JF39JGJT011394 2018-08-16 12:33:12.457
ME4JF39JGJT011400 2018-08-16 12:46:59.320
ME4JF39JGJT011405 2018-08-16 12:50:14.230
ME4JF39JGJT011406 2018-08-16 12:32:26.487
ME4JF39JGJT011407 2018-08-16 12:37:41.817
ME4JF39JGJT011408 2018-08-16 12:32:15.720
ME4JF39JGJT011409 2018-08-16 12:48:34.663
ME4JF39JGJT011410 2018-08-16 12:32:43.490
ME4JF39JGJT011411 2018-08-16 12:33:01.567
ME4JF39JGJT011412 2018-08-16 12:34:34.630
ME4JF39JGJT011413 2018-08-16 12:33:18.223
ME4JF39JGJT011415 2018-08-16 12:33:50.363
ME4JF39JGJT011416 2018-08-16 12:33:07.580
ME4JF39JGJT011417 2018-08-16 12:47:51.753
ME4JF39JGJT011418 2018-08-16 12:37:09.753
ME4JF39JGJT011419 2018-08-16 12:37:15.697
ME4JF39JGJT011423 2018-08-16 12:37:25.063
ME4JF39JGJT011425 2018-08-16 12:37:37.083
ME4JF39JGJT011426 2018-08-16 12:37:31.773
ME4JF39JGJT011427 2018-08-16 12:37:49.160
ME4JF39JGJT011428 2018-08-16 12:38:00.410
ME4JF39JGJT011429 2018-08-16 12:43:04.080
ME4JF39JGJT011430 2018-08-16 12:37:55.323
ME4JF39JGJT011431 2018-08-16 12:38:22.783
ME4JF39JGJT011432 2018-08-16 12:38:18.207
ME4JF39JGJT011433 2018-08-16 12:38:12.427
ME4JF39JGJT011434 2018-08-16 12:47:29.280
ME4JF39JGJT011435 2018-08-16 12:47:42.513
ME4JF39JGJT011436 2018-08-16 12:52:20.077
ME4JF39JGJT011437 2018-08-16 12:46:50.240
ME4JF39JGJT011443 2018-08-16 12:50:58.637
ME4JF39JGJT011444 2018-08-16 12:25:07.663
ME4JF39KGJT021354 2018-08-16 13:05:40.750
ME4JF39KGJT021362 2018-08-16 12:17:38.870
ME4JF39KGJT021364 2018-08-16 12:47:18.993
ME4JF39KGJT021372 2018-08-16 12:18:08.897
ME4JF39KGJT021377 2018-08-16 12:48:39.300
ME4JF39KGJT021384 2018-08-16 12:47:47.220
ME4JF39KGJT021386 2018-08-16 12:25:34.700
ME4JF39KGJT021388 2018-08-16 12:48:52.810
ME4JF39KGJT021397 2018-08-16 12:29:57.287
ME4JF39KGJT021398 2018-08-16 11:29:34.220
ME4JF39KGJT021412 2018-08-16 12:14:36.840
ME4JF39KGJT021420 2018-08-16 12:29:39.367
ME4JF39KGJT021423 2018-08-16 12:16:35.893
ME4JF39KGJT021424 2018-08-16 12:29:43.547
ME4JF39KGJT021425 2018-08-16 12:30:01.567
ME4JF39KGJT021426 2018-08-16 11:30:13.553
ME4JF39KGJT021427 2018-08-16 12:29:48.300
ME4JF39KGJT021428 2018-08-16 12:30:19.863
ME4JF39KGJT021429 2018-08-16 12:29:52.657
ME4JF39KGJT021430 2018-08-16 12:30:10.003
ME4JF39KGJT021431 2018-08-16 11:38:44.177
ME4JF39KGJT021432 2018-08-16 12:30:33.460
ME4JF39KGJT021434 2018-08-16 12:30:26.050
ME4JF39KGJT021435 2018-08-16 12:15:58.357
ME4JF39KGJT021438 2018-08-16 12:26:03.990
ME4JF39KGJT021439 2018-08-16 12:13:01.380
ME4JF39KGJT021440 2018-08-16 12:03:35.790
ME4JF39KGJT021441 2018-08-16 12:22:33.293
ME4JF39KGJT021442 2018-08-16 11:32:37.747
ME4JF39KGJT021443 2018-08-16 11:32:15.900
ME4JF39KGJT021444 2018-08-16 11:35:11.967
ME4JF39KGJT021446 2018-08-16 11:32:52.153
ME4JF39KGJT021447 2018-08-16 11:30:48.090
ME4JF39KGJT021449 2018-08-16 12:20:59.583
ME4JF39KGJT021452 2018-08-16 11:29:48.190
ME4JF39KGJT021453 2018-08-16 11:29:41.440
ME4JF39KGJT021454 2018-08-16 12:23:17.540
ME4JF39KGJT021455 2018-08-16 12:18:21.760
ME4JF39KGJT021461 2018-08-16 12:18:00.293
ME4JF39KGJT021462 2018-08-16 12:18:27.483
ME4JF39KGJT021463 2018-08-16 12:17:45.117
ME4JF39KGJT021466 2018-08-16 12:18:15.113
ME4JF39KGJT021467 2018-08-16 12:25:48.760
ME4JF39KGJT021469 2018-08-16 12:19:27.310
ME4JF39KGJT021471 2018-08-16 12:18:32.743
ME4JF39KGJT021473 2018-08-16 12:19:19.240
ME4JF39KGJT021474 2018-08-16 12:19:06.933
ME4JF39KGJT021475 2018-08-16 12:23:28.230
ME4JF39KGJT021478 2018-08-16 12:19:34.240
ME4JF39KGJT021479 2018-08-16 12:26:38.173
ME4JF39KGJT021481 2018-08-16 12:19:50.300
ME4JF39KGJT021482 2018-08-16 12:21:28.387
ME4JF39KGJT021483 2018-08-16 12:21:05.973
ME4JF39KGJT021484 2018-08-16 12:25:43.087
ME4JF39KGJT021486 2018-08-16 11:29:14.470
ME4JF39KGJT021487 2018-08-16 12:22:09.257
ME4JF39KGJT021488 2018-08-16 12:23:56.220
ME4JF39KGJT021490 2018-08-16 12:23:49.507
ME4JF39KGJT021491 2018-08-16 12:25:27.283
ME4JF39KGJT021492 2018-08-16 12:25:58.003
ME4JF39KGJT021494 2018-08-16 12:26:22.797
ME4JF39KGJT021497 2018-08-16 12:52:16.193
ME4JF39KGJT021498 2018-08-16 12:27:33.660
ME4JF39KGJT021499 2018-08-16 13:01:57.043
ME4JF39KGJT021501 2018-08-16 12:27:39.693
ME4JF39KGJT021504 2018-08-16 12:53:01.293
ME4JF39KGJT021509 2018-08-16 12:51:56.460
ME4JF39KGJT021510 2018-08-16 12:52:11.157
ME4JF39KGJT021511 2018-08-16 12:52:01.523
ME4JF39KGJT021513 2018-08-16 12:27:44.803
ME4JF39KGJT021516 2018-08-16 12:26:08.757
ME4JF39KGJT021517 2018-08-16 12:26:42.980
ME4JF39KGJT021518 2018-08-16 12:26:28.160
ME4JF39KGJT021532 2018-08-16 13:02:29.980
ME4JF39KGJT021538 2018-08-16 13:00:47.727
ME4JF39KGJT021542 2018-08-16 12:46:38.143
ME4JF39KGJT021550 2018-08-16 11:21:27.917
ME4JF39KGJT021557 2018-08-16 12:57:43.287
ME4JF39KGJT021558 2018-08-16 12:24:56.433
ME4JF39KGJT021560 2018-08-16 12:58:39.753
ME4JF39KGJT021561 2018-08-16 12:52:51.077
ME4JF39KGJT021562 2018-08-16 12:57:28.667
ME4JF39KGJT021564 2018-08-16 12:56:58.510
ME4JF39KGJT021565 2018-08-16 12:58:04.210
ME4JF39KGJT021566 2018-08-16 12:53:29.837
ME4JF39KGJT021567 2018-08-16 12:52:32.150
ME4JF39KGJT021568 2018-08-16 12:53:24.850
ME4JF39KGJT021569 2018-08-16 12:52:27.643
ME4JF39KGJT021570 2018-08-16 12:57:20.223
ME4JF39KGJT021571 2018-08-16 12:53:45.243
ME4JF39KGJT021572 2018-08-16 12:25:21.203
ME4JF39KGJT021573 2018-08-16 12:25:01.650
ME4JF39KGJT021575 2018-08-16 12:53:20.980
ME4JF39KGJT021577 2018-08-16 12:52:35.453
ME4JF39KGJT021578 2018-08-16 13:02:25.413
ME4JF39KGJT021580 2018-08-16 12:53:11.620
ME4JF39KGJT021581 2018-08-16 12:45:48.337
ME4JF39KGJT021582 2018-08-16 13:05:21.917
ME4JF39KGJT021584 2018-08-16 13:00:41.867
ME4JF39KGJT021585 2018-08-16 12:53:34.590
ME4JF39KGJT021586 2018-08-16 12:53:40.650
ME4JF39KGJT021587 2018-08-16 13:00:54.730
ME4JF39KGJT021588 2018-08-16 13:00:21.457
ME4JF39KGJT021589 2018-08-16 13:00:28.360
ME4JF39KGJT021591 2018-08-16 13:01:06.947
ME4JF39KGJT021592 2018-08-16 12:51:41.110
ME4JF39KGJT021593 2018-08-16 13:02:31.723
ME4JF39KGJT021594 2018-08-16 13:02:34.067
ME4JF39KGJT021595 2018-08-16 13:01:11.773
ME4JF39KGJT021597 2018-08-16 13:02:38.293
ME4JF39KGJT021598 2018-08-16 13:02:36.473
ME4JF39KGJT021599 2018-08-16 13:02:27.450
ME4JF39KGJT021600 2018-08-16 13:04:37.527
ME4JF39KGJT021601 2018-08-16 13:02:42.420
ME4JF39KGJT021602 2018-08-16 13:02:40.047
ME4JF39KGJT021603 2018-08-16 13:05:20.157
ME4JF39KGJT021604 2018-08-16 13:05:09.630
ME4JF39KGJT021605 2018-08-16 13:05:17.503
ME4JF39KGJT021606 2018-08-16 13:05:11.843
ME4JF39KGJT021607 2018-08-16 13:05:14.000
ME4JF39KGJT021608 2018-08-16 12:46:42.440
ME4JF39KGJT021609 2018-08-16 13:05:25.323
ME4JF39KGJT021610 2018-08-16 13:05:37.200
ME4JF39KGJT021612 2018-08-16 13:05:29.637
ME4JF39KGJT021613 2018-08-16 13:05:26.920
ME4JF39KGJT021614 2018-08-16 13:05:31.503
ME4JF39KGJT021615 2018-08-16 13:05:33.310
ME4JF39KGJT021616 2018-08-16 13:05:39.063
ME4JF39KGJT021617 2018-08-16 13:02:22.700
ME4JF39KGJT021618 2018-08-16 12:46:00.277
ME4JF39KGJT021619 2018-08-16 12:46:46.460
ME4JF39KGJT021620 2018-08-16 13:01:59.257
ME4JF39KGJT021622 2018-08-16 13:05:44.497
ME4JF39KGJT021623 2018-08-16 13:16:06.093
ME4JF39KGJT021624 2018-08-16 13:09:34.527
ME4JF39KGJT021625 2018-08-16 13:16:19.583
ME4JF39KGJT021626 2018-08-16 13:09:02.427
ME4JF39KGJT021628 2018-08-16 12:27:12.850
ME4JF39KGJT021629 2018-08-16 13:13:53.940
ME4JF39KGJT021630 2018-08-16 13:13:40.230
ME4JF39KGJT021631 2018-08-16 14:15:12.827
ME4JF39KGJT021632 2018-08-16 12:26:49.637
ME4JF39KGJT021633 2018-08-16 12:26:59.393
ME4JF39KGJT021634 2018-08-16 12:27:04.823
ME4JF39KGJT021635 2018-08-16 12:26:54.150
ME4JF39KGJT021637 2018-08-16 12:27:22.893
ME4JF39KGJT021639 2018-08-16 14:16:09.507
ME4JF39KGJT021642 2018-08-16 12:45:58.010
ME4JF39KGJT021647 2018-08-16 12:46:04.480
ME4JF39LGJT004941 2018-08-16 13:05:35.230
ME4JF39LGJT004944 2018-08-16 12:48:30.933
ME4JF39LGJT004948 2018-08-16 12:51:26.400
ME4JF39LGJT004955 2018-08-16 12:49:16.207
ME4JF39LGJT004960 2018-08-16 12:51:19.993
ME4JF39LGJT004961 2018-08-16 12:40:53.050
ME4JF39LGJT004962 2018-08-16 12:42:20.123
ME4JF39LGJT004963 2018-08-16 12:41:38.940
ME4JF39LGJT004964 2018-08-16 12:48:26.393
ME4JF39LGJT004965 2018-08-16 12:45:40.180
ME4JF39LGJT004971 2018-08-16 12:42:25.570
ME4JF39LGJT004973 2018-08-16 12:42:00.537
ME4JF39LGJT004975 2018-08-16 12:46:54.520
ME4JF39LGJT004976 2018-08-16 12:41:07.600
ME4JF39LGJT004977 2018-08-16 12:42:53.910
ME4JF39LGJT004978 2018-08-16 12:41:51.473
ME4JF39LGJT004979 2018-08-16 12:42:48.970
ME4JF39LGJT004980 2018-08-16 12:41:26.520
ME4JF39LGJT004981 2018-08-16 12:42:59.330
ME4JF39LGJT004982 2018-08-16 12:41:46.800
ME4JF39LGJT004983 2018-08-16 12:45:04.287
ME4JF39LGJT004984 2018-08-16 12:42:14.223
ME4JF39LGJT004985 2018-08-16 12:28:11.770
ME4JF39LGJT004986 2018-08-16 12:51:31.970
ME4JF39LGJT004987 2018-08-16 12:47:36.343
ME4JF39LGJT004988 2018-08-16 12:42:33.300
ME4JF39LGJT004989 2018-08-16 12:42:06.740
ME4JF39LGJT004990 2018-08-16 12:43:24.860
ME4JF39LGJT004994 2018-08-16 12:42:43.567
ME4JF39LGJT004995 2018-08-16 12:43:12.427
ME4JF39LGJT004996 2018-08-16 12:45:36.163
ME4JF39LGJT004997 2018-08-16 12:46:08.607
ME4JF39LGJT004998 2018-08-16 12:39:02.707
ME4JF39LGJT004999 2018-08-16 12:43:18.433
ME4JF39LGJT005000 2018-08-16 12:48:43.570
ME4JF39LGJT005001 2018-08-16 12:45:20.320
ME4JF39LGJT005003 2018-08-16 12:27:51.207
ME4JF39LGJT005004 2018-08-16 12:45:24.617
ME4JF39LGJT005007 2018-08-16 12:39:13.550
ME4JF39LGJT005009 2018-08-16 12:27:56.553
ME4JF39LGJT005011 2018-08-16 12:38:56.800
ME4JF39LGJT005012 2018-08-16 12:50:53.890
ME4JF39LGJT005013 2018-08-16 12:38:27.473
ME4JF39LGJT005014 2018-08-16 12:38:33.690
ME4JF39LGJT005015 2018-08-16 12:47:04.227
ME4JF39LGJT005016 2018-08-16 12:39:07.973
ME4JF39LGJT005017 2018-08-16 12:39:32.520
ME4JF39LGJT005018 2018-08-16 12:40:06.283
ME4JF39LGJT005019 2018-08-16 12:39:44.737
ME4JF39LGJT005020 2018-08-16 12:39:18.817
ME4JF50AGJT341071 2018-08-16 11:33:59.723
ME4JF50AGJT344724 2018-08-16 13:30:00.280
ME4JF50AGJT344816 2018-08-16 13:28:37.483
ME4JF50AGJT344818 2018-08-16 13:29:28.207
ME4JF50AGJT344824 2018-08-16 13:28:08.810
ME4JF50AGJT344826 2018-08-16 13:29:41.537
ME4JF50AGJT344830 2018-08-16 13:27:57.753
ME4JF50AGJT344873 2018-08-16 13:28:50.010
ME4JF50AGJT344882 2018-08-16 13:27:32.163
ME4JF50AGJT344886 2018-08-16 13:27:43.630
ME4JF50BGJT157862 2018-08-16 13:20:02.167
ME4JF50BGJT157891 2018-08-16 13:22:06.110
ME4JF50BGJT157909 2018-08-16 13:18:54.710
ME4JF50BGJT157910 2018-08-16 13:26:59.917
ME4JF50BGJT157934 2018-08-16 13:23:02.110
ME4JF50BGJT157962 2018-08-16 13:22:22.743
ME4JF50BGJT157965 2018-08-16 13:27:18.143
ME4JF50BGJT157997 2018-08-16 13:22:47.020
ME4JF50BGJT158010 2018-08-16 13:27:08.810
ME4JF50BGJT158014 2018-08-16 13:21:02.943
ME4JF50BGJT158018 2018-08-16 13:20:52.230
ME4JF50BGJT158119 2018-08-16 12:24:18.903
ME4JF50BGJT158138 2018-08-16 12:45:29.070
P1022734-004 2018-08-28 15:23:44.017
I need to print on date 16 count is 300, on 28 count is 2 and rest 1,2...31 it should display 0.
sql
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
I have written a query to get the number of vehicles in a particular day of specific month. Here is my query,
SELECT CASE WHEN DATEPART(DAY, timestamp) = '1' THEN (select count(Vehicle_Id)) ELSE '0' END AS [1],
CASE WHEN DATEPART(DAY, timestamp) = '2' THEN (select count(Vehicle_Id)) ELSE '0' END AS [2],
CASE WHEN DATEPART(DAY, timestamp) = '3' THEN (select count(Vehicle_Id)) ELSE '0' END AS [3],
CASE WHEN DATEPART(DAY, timestamp) = '4' THEN (select count(Vehicle_Id)) ELSE '0' END AS [4],
CASE WHEN DATEPART(DAY, timestamp) = '5' THEN (select count(Vehicle_Id)) ELSE '0' END AS [5],
CASE WHEN DATEPART(DAY, timestamp) = '6' THEN (select count(Vehicle_Id)) ELSE '0' END AS [6],
CASE WHEN DATEPART(DAY, timestamp) = '7' THEN (select count(Vehicle_Id)) ELSE '0' END AS [7],
CASE WHEN DATEPART(DAY, timestamp) = '8' THEN (select count(Vehicle_Id)) ELSE '0' END AS [8],
CASE WHEN DATEPART(DAY, timestamp) = '9' THEN (select count(Vehicle_Id)) ELSE '0' END AS [9],
CASE WHEN DATEPART(DAY, timestamp) = '10' THEN (select count(Vehicle_Id)) ELSE '0' END AS [10],
CASE WHEN DATEPART(DAY, timestamp) = '11' THEN (select count(Vehicle_Id)) ELSE '0' END AS [11],
CASE WHEN DATEPART(DAY, timestamp) = '12' THEN (select count(Vehicle_Id)) ELSE '0' END AS [12],
CASE WHEN DATEPART(DAY, timestamp) = '13' THEN (select count(Vehicle_Id)) ELSE '0' END AS [13],
CASE WHEN DATEPART(DAY, timestamp) = '14' THEN (select count(Vehicle_Id)) ELSE '0' END AS [14],
CASE WHEN DATEPART(DAY, timestamp) = '15' THEN (select count(Vehicle_Id)) ELSE '0' END AS [15],
CASE WHEN DATEPART(DAY, timestamp) = '16' THEN (select count(Vehicle_Id)) ELSE '0' END AS [16],
CASE WHEN DATEPART(DAY, timestamp) = '17' THEN (select count(Vehicle_Id)) ELSE '0' END AS [17],
CASE WHEN DATEPART(DAY, timestamp) = '18' THEN (select count(Vehicle_Id)) ELSE '0' END AS [18],
CASE WHEN DATEPART(DAY, timestamp) = '19' THEN (select count(Vehicle_Id)) ELSE '0' END AS [19],
CASE WHEN DATEPART(DAY, timestamp) = '20' THEN (select count(Vehicle_Id)) ELSE '0' END AS [20],
CASE WHEN DATEPART(DAY, timestamp) = '21' THEN (select count(Vehicle_Id)) ELSE '0' END AS [21],
CASE WHEN DATEPART(DAY, timestamp) = '22' THEN (select count(Vehicle_Id)) ELSE '0' END AS [22],
CASE WHEN DATEPART(DAY, timestamp) = '23' THEN (select count(Vehicle_Id)) ELSE '0' END AS [23],
CASE WHEN DATEPART(DAY, timestamp) = '24' THEN (select count(Vehicle_Id)) ELSE '0' END AS [24],
CASE WHEN DATEPART(DAY, timestamp) = '25' THEN (select count(Vehicle_Id)) ELSE '0' END AS [25],
CASE WHEN DATEPART(DAY, timestamp) = '26' THEN (select count(Vehicle_Id)) ELSE '0' END AS [26],
CASE WHEN DATEPART(DAY, timestamp) = '27' THEN (select count(Vehicle_Id)) ELSE '0' END AS [27],
CASE WHEN DATEPART(DAY, timestamp) = '28' THEN (select count(Vehicle_Id)) ELSE '0' END AS [28],
CASE WHEN DATEPART(DAY, timestamp) = '29' THEN (select count(Vehicle_Id)) ELSE '0' END AS [29],
CASE WHEN DATEPART(DAY, timestamp) = '30' THEN (select count(Vehicle_Id)) ELSE '0' END AS [30],
CASE WHEN DATEPART(DAY, timestamp) = '31' THEN (select count(Vehicle_Id)) ELSE '0' END AS [31]
FROM HVQ_Vehicle
where month(timestamp) = '08'
GROUP BY day(timestamp);
This gives result in two rows instead of single row. How to obtain in a single row. Here is the sample data.
Vehicle_Id timestamp
8901361307130 2018-08-28 15:22:29.687
ME4JF39JGJT011230 2018-08-16 12:29:10.800
ME4JF39JGJT011231 2018-08-16 12:28:34.473
ME4JF39JGJT011232 2018-08-16 12:28:44.930
ME4JF39JGJT011233 2018-08-16 12:28:51.747
ME4JF39JGJT011234 2018-08-16 12:28:59.653
ME4JF39JGJT011235 2018-08-16 12:49:56.463
ME4JF39JGJT011236 2018-08-16 12:29:20.683
ME4JF39JGJT011237 2018-08-16 12:29:15.917
ME4JF39JGJT011239 2018-08-16 12:29:30.487
ME4JF39JGJT011240 2018-08-16 12:51:02.883
ME4JF39JGJT011241 2018-08-16 12:47:25.167
ME4JF39JGJT011242 2018-08-16 12:29:25.900
ME4JF39JGJT011243 2018-08-16 12:50:39.793
ME4JF39JGJT011244 2018-08-16 11:39:32.040
ME4JF39JGJT011245 2018-08-16 12:29:34.940
ME4JF39JGJT011246 2018-08-16 12:50:48.110
ME4JF39JGJT011248 2018-08-16 11:56:47.163
ME4JF39JGJT011250 2018-08-16 12:17:24.847
ME4JF39JGJT011252 2018-08-16 12:24:01.873
ME4JF39JGJT011253 2018-08-16 12:17:20.093
ME4JF39JGJT011254 2018-08-16 12:51:15.540
ME4JF39JGJT011255 2018-08-16 12:49:31.407
ME4JF39JGJT011257 2018-08-16 12:50:34.420
ME4JF39JGJT011260 2018-08-16 12:24:07.197
ME4JF39JGJT011262 2018-08-16 12:50:23.643
ME4JF39JGJT011263 2018-08-16 12:17:32.140
ME4JF39JGJT011264 2018-08-16 12:24:12.553
ME4JF39JGJT011265 2018-08-16 12:24:25.897
ME4JF39JGJT011266 2018-08-16 12:24:50.730
ME4JF39JGJT011267 2018-08-16 12:24:31.730
ME4JF39JGJT011271 2018-08-16 12:24:34.087
ME4JF39JGJT011275 2018-08-16 12:51:10.437
ME4JF39JGJT011288 2018-08-16 12:47:55.863
ME4JF39JGJT011293 2018-08-16 12:28:04.703
ME4JF39JGJT011294 2018-08-16 12:48:00.583
ME4JF39JGJT011316 2018-08-16 12:32:49.480
ME4JF39JGJT011324 2018-08-16 12:33:28.817
ME4JF39JGJT011327 2018-08-16 12:50:00.590
ME4JF39JGJT011330 2018-08-16 12:50:04.307
ME4JF39JGJT011336 2018-08-16 12:47:12.133
ME4JF39JGJT011340 2018-08-16 12:48:04.950
ME4JF39JGJT011347 2018-08-16 12:49:22.493
ME4JF39JGJT011353 2018-08-16 12:48:10.610
ME4JF39JGJT011367 2018-08-16 12:48:57.543
ME4JF39JGJT011370 2018-08-16 12:38:07.503
ME4JF39JGJT011373 2018-08-16 12:48:22.317
ME4JF39JGJT011394 2018-08-16 12:33:12.457
ME4JF39JGJT011400 2018-08-16 12:46:59.320
ME4JF39JGJT011405 2018-08-16 12:50:14.230
ME4JF39JGJT011406 2018-08-16 12:32:26.487
ME4JF39JGJT011407 2018-08-16 12:37:41.817
ME4JF39JGJT011408 2018-08-16 12:32:15.720
ME4JF39JGJT011409 2018-08-16 12:48:34.663
ME4JF39JGJT011410 2018-08-16 12:32:43.490
ME4JF39JGJT011411 2018-08-16 12:33:01.567
ME4JF39JGJT011412 2018-08-16 12:34:34.630
ME4JF39JGJT011413 2018-08-16 12:33:18.223
ME4JF39JGJT011415 2018-08-16 12:33:50.363
ME4JF39JGJT011416 2018-08-16 12:33:07.580
ME4JF39JGJT011417 2018-08-16 12:47:51.753
ME4JF39JGJT011418 2018-08-16 12:37:09.753
ME4JF39JGJT011419 2018-08-16 12:37:15.697
ME4JF39JGJT011423 2018-08-16 12:37:25.063
ME4JF39JGJT011425 2018-08-16 12:37:37.083
ME4JF39JGJT011426 2018-08-16 12:37:31.773
ME4JF39JGJT011427 2018-08-16 12:37:49.160
ME4JF39JGJT011428 2018-08-16 12:38:00.410
ME4JF39JGJT011429 2018-08-16 12:43:04.080
ME4JF39JGJT011430 2018-08-16 12:37:55.323
ME4JF39JGJT011431 2018-08-16 12:38:22.783
ME4JF39JGJT011432 2018-08-16 12:38:18.207
ME4JF39JGJT011433 2018-08-16 12:38:12.427
ME4JF39JGJT011434 2018-08-16 12:47:29.280
ME4JF39JGJT011435 2018-08-16 12:47:42.513
ME4JF39JGJT011436 2018-08-16 12:52:20.077
ME4JF39JGJT011437 2018-08-16 12:46:50.240
ME4JF39JGJT011443 2018-08-16 12:50:58.637
ME4JF39JGJT011444 2018-08-16 12:25:07.663
ME4JF39KGJT021354 2018-08-16 13:05:40.750
ME4JF39KGJT021362 2018-08-16 12:17:38.870
ME4JF39KGJT021364 2018-08-16 12:47:18.993
ME4JF39KGJT021372 2018-08-16 12:18:08.897
ME4JF39KGJT021377 2018-08-16 12:48:39.300
ME4JF39KGJT021384 2018-08-16 12:47:47.220
ME4JF39KGJT021386 2018-08-16 12:25:34.700
ME4JF39KGJT021388 2018-08-16 12:48:52.810
ME4JF39KGJT021397 2018-08-16 12:29:57.287
ME4JF39KGJT021398 2018-08-16 11:29:34.220
ME4JF39KGJT021412 2018-08-16 12:14:36.840
ME4JF39KGJT021420 2018-08-16 12:29:39.367
ME4JF39KGJT021423 2018-08-16 12:16:35.893
ME4JF39KGJT021424 2018-08-16 12:29:43.547
ME4JF39KGJT021425 2018-08-16 12:30:01.567
ME4JF39KGJT021426 2018-08-16 11:30:13.553
ME4JF39KGJT021427 2018-08-16 12:29:48.300
ME4JF39KGJT021428 2018-08-16 12:30:19.863
ME4JF39KGJT021429 2018-08-16 12:29:52.657
ME4JF39KGJT021430 2018-08-16 12:30:10.003
ME4JF39KGJT021431 2018-08-16 11:38:44.177
ME4JF39KGJT021432 2018-08-16 12:30:33.460
ME4JF39KGJT021434 2018-08-16 12:30:26.050
ME4JF39KGJT021435 2018-08-16 12:15:58.357
ME4JF39KGJT021438 2018-08-16 12:26:03.990
ME4JF39KGJT021439 2018-08-16 12:13:01.380
ME4JF39KGJT021440 2018-08-16 12:03:35.790
ME4JF39KGJT021441 2018-08-16 12:22:33.293
ME4JF39KGJT021442 2018-08-16 11:32:37.747
ME4JF39KGJT021443 2018-08-16 11:32:15.900
ME4JF39KGJT021444 2018-08-16 11:35:11.967
ME4JF39KGJT021446 2018-08-16 11:32:52.153
ME4JF39KGJT021447 2018-08-16 11:30:48.090
ME4JF39KGJT021449 2018-08-16 12:20:59.583
ME4JF39KGJT021452 2018-08-16 11:29:48.190
ME4JF39KGJT021453 2018-08-16 11:29:41.440
ME4JF39KGJT021454 2018-08-16 12:23:17.540
ME4JF39KGJT021455 2018-08-16 12:18:21.760
ME4JF39KGJT021461 2018-08-16 12:18:00.293
ME4JF39KGJT021462 2018-08-16 12:18:27.483
ME4JF39KGJT021463 2018-08-16 12:17:45.117
ME4JF39KGJT021466 2018-08-16 12:18:15.113
ME4JF39KGJT021467 2018-08-16 12:25:48.760
ME4JF39KGJT021469 2018-08-16 12:19:27.310
ME4JF39KGJT021471 2018-08-16 12:18:32.743
ME4JF39KGJT021473 2018-08-16 12:19:19.240
ME4JF39KGJT021474 2018-08-16 12:19:06.933
ME4JF39KGJT021475 2018-08-16 12:23:28.230
ME4JF39KGJT021478 2018-08-16 12:19:34.240
ME4JF39KGJT021479 2018-08-16 12:26:38.173
ME4JF39KGJT021481 2018-08-16 12:19:50.300
ME4JF39KGJT021482 2018-08-16 12:21:28.387
ME4JF39KGJT021483 2018-08-16 12:21:05.973
ME4JF39KGJT021484 2018-08-16 12:25:43.087
ME4JF39KGJT021486 2018-08-16 11:29:14.470
ME4JF39KGJT021487 2018-08-16 12:22:09.257
ME4JF39KGJT021488 2018-08-16 12:23:56.220
ME4JF39KGJT021490 2018-08-16 12:23:49.507
ME4JF39KGJT021491 2018-08-16 12:25:27.283
ME4JF39KGJT021492 2018-08-16 12:25:58.003
ME4JF39KGJT021494 2018-08-16 12:26:22.797
ME4JF39KGJT021497 2018-08-16 12:52:16.193
ME4JF39KGJT021498 2018-08-16 12:27:33.660
ME4JF39KGJT021499 2018-08-16 13:01:57.043
ME4JF39KGJT021501 2018-08-16 12:27:39.693
ME4JF39KGJT021504 2018-08-16 12:53:01.293
ME4JF39KGJT021509 2018-08-16 12:51:56.460
ME4JF39KGJT021510 2018-08-16 12:52:11.157
ME4JF39KGJT021511 2018-08-16 12:52:01.523
ME4JF39KGJT021513 2018-08-16 12:27:44.803
ME4JF39KGJT021516 2018-08-16 12:26:08.757
ME4JF39KGJT021517 2018-08-16 12:26:42.980
ME4JF39KGJT021518 2018-08-16 12:26:28.160
ME4JF39KGJT021532 2018-08-16 13:02:29.980
ME4JF39KGJT021538 2018-08-16 13:00:47.727
ME4JF39KGJT021542 2018-08-16 12:46:38.143
ME4JF39KGJT021550 2018-08-16 11:21:27.917
ME4JF39KGJT021557 2018-08-16 12:57:43.287
ME4JF39KGJT021558 2018-08-16 12:24:56.433
ME4JF39KGJT021560 2018-08-16 12:58:39.753
ME4JF39KGJT021561 2018-08-16 12:52:51.077
ME4JF39KGJT021562 2018-08-16 12:57:28.667
ME4JF39KGJT021564 2018-08-16 12:56:58.510
ME4JF39KGJT021565 2018-08-16 12:58:04.210
ME4JF39KGJT021566 2018-08-16 12:53:29.837
ME4JF39KGJT021567 2018-08-16 12:52:32.150
ME4JF39KGJT021568 2018-08-16 12:53:24.850
ME4JF39KGJT021569 2018-08-16 12:52:27.643
ME4JF39KGJT021570 2018-08-16 12:57:20.223
ME4JF39KGJT021571 2018-08-16 12:53:45.243
ME4JF39KGJT021572 2018-08-16 12:25:21.203
ME4JF39KGJT021573 2018-08-16 12:25:01.650
ME4JF39KGJT021575 2018-08-16 12:53:20.980
ME4JF39KGJT021577 2018-08-16 12:52:35.453
ME4JF39KGJT021578 2018-08-16 13:02:25.413
ME4JF39KGJT021580 2018-08-16 12:53:11.620
ME4JF39KGJT021581 2018-08-16 12:45:48.337
ME4JF39KGJT021582 2018-08-16 13:05:21.917
ME4JF39KGJT021584 2018-08-16 13:00:41.867
ME4JF39KGJT021585 2018-08-16 12:53:34.590
ME4JF39KGJT021586 2018-08-16 12:53:40.650
ME4JF39KGJT021587 2018-08-16 13:00:54.730
ME4JF39KGJT021588 2018-08-16 13:00:21.457
ME4JF39KGJT021589 2018-08-16 13:00:28.360
ME4JF39KGJT021591 2018-08-16 13:01:06.947
ME4JF39KGJT021592 2018-08-16 12:51:41.110
ME4JF39KGJT021593 2018-08-16 13:02:31.723
ME4JF39KGJT021594 2018-08-16 13:02:34.067
ME4JF39KGJT021595 2018-08-16 13:01:11.773
ME4JF39KGJT021597 2018-08-16 13:02:38.293
ME4JF39KGJT021598 2018-08-16 13:02:36.473
ME4JF39KGJT021599 2018-08-16 13:02:27.450
ME4JF39KGJT021600 2018-08-16 13:04:37.527
ME4JF39KGJT021601 2018-08-16 13:02:42.420
ME4JF39KGJT021602 2018-08-16 13:02:40.047
ME4JF39KGJT021603 2018-08-16 13:05:20.157
ME4JF39KGJT021604 2018-08-16 13:05:09.630
ME4JF39KGJT021605 2018-08-16 13:05:17.503
ME4JF39KGJT021606 2018-08-16 13:05:11.843
ME4JF39KGJT021607 2018-08-16 13:05:14.000
ME4JF39KGJT021608 2018-08-16 12:46:42.440
ME4JF39KGJT021609 2018-08-16 13:05:25.323
ME4JF39KGJT021610 2018-08-16 13:05:37.200
ME4JF39KGJT021612 2018-08-16 13:05:29.637
ME4JF39KGJT021613 2018-08-16 13:05:26.920
ME4JF39KGJT021614 2018-08-16 13:05:31.503
ME4JF39KGJT021615 2018-08-16 13:05:33.310
ME4JF39KGJT021616 2018-08-16 13:05:39.063
ME4JF39KGJT021617 2018-08-16 13:02:22.700
ME4JF39KGJT021618 2018-08-16 12:46:00.277
ME4JF39KGJT021619 2018-08-16 12:46:46.460
ME4JF39KGJT021620 2018-08-16 13:01:59.257
ME4JF39KGJT021622 2018-08-16 13:05:44.497
ME4JF39KGJT021623 2018-08-16 13:16:06.093
ME4JF39KGJT021624 2018-08-16 13:09:34.527
ME4JF39KGJT021625 2018-08-16 13:16:19.583
ME4JF39KGJT021626 2018-08-16 13:09:02.427
ME4JF39KGJT021628 2018-08-16 12:27:12.850
ME4JF39KGJT021629 2018-08-16 13:13:53.940
ME4JF39KGJT021630 2018-08-16 13:13:40.230
ME4JF39KGJT021631 2018-08-16 14:15:12.827
ME4JF39KGJT021632 2018-08-16 12:26:49.637
ME4JF39KGJT021633 2018-08-16 12:26:59.393
ME4JF39KGJT021634 2018-08-16 12:27:04.823
ME4JF39KGJT021635 2018-08-16 12:26:54.150
ME4JF39KGJT021637 2018-08-16 12:27:22.893
ME4JF39KGJT021639 2018-08-16 14:16:09.507
ME4JF39KGJT021642 2018-08-16 12:45:58.010
ME4JF39KGJT021647 2018-08-16 12:46:04.480
ME4JF39LGJT004941 2018-08-16 13:05:35.230
ME4JF39LGJT004944 2018-08-16 12:48:30.933
ME4JF39LGJT004948 2018-08-16 12:51:26.400
ME4JF39LGJT004955 2018-08-16 12:49:16.207
ME4JF39LGJT004960 2018-08-16 12:51:19.993
ME4JF39LGJT004961 2018-08-16 12:40:53.050
ME4JF39LGJT004962 2018-08-16 12:42:20.123
ME4JF39LGJT004963 2018-08-16 12:41:38.940
ME4JF39LGJT004964 2018-08-16 12:48:26.393
ME4JF39LGJT004965 2018-08-16 12:45:40.180
ME4JF39LGJT004971 2018-08-16 12:42:25.570
ME4JF39LGJT004973 2018-08-16 12:42:00.537
ME4JF39LGJT004975 2018-08-16 12:46:54.520
ME4JF39LGJT004976 2018-08-16 12:41:07.600
ME4JF39LGJT004977 2018-08-16 12:42:53.910
ME4JF39LGJT004978 2018-08-16 12:41:51.473
ME4JF39LGJT004979 2018-08-16 12:42:48.970
ME4JF39LGJT004980 2018-08-16 12:41:26.520
ME4JF39LGJT004981 2018-08-16 12:42:59.330
ME4JF39LGJT004982 2018-08-16 12:41:46.800
ME4JF39LGJT004983 2018-08-16 12:45:04.287
ME4JF39LGJT004984 2018-08-16 12:42:14.223
ME4JF39LGJT004985 2018-08-16 12:28:11.770
ME4JF39LGJT004986 2018-08-16 12:51:31.970
ME4JF39LGJT004987 2018-08-16 12:47:36.343
ME4JF39LGJT004988 2018-08-16 12:42:33.300
ME4JF39LGJT004989 2018-08-16 12:42:06.740
ME4JF39LGJT004990 2018-08-16 12:43:24.860
ME4JF39LGJT004994 2018-08-16 12:42:43.567
ME4JF39LGJT004995 2018-08-16 12:43:12.427
ME4JF39LGJT004996 2018-08-16 12:45:36.163
ME4JF39LGJT004997 2018-08-16 12:46:08.607
ME4JF39LGJT004998 2018-08-16 12:39:02.707
ME4JF39LGJT004999 2018-08-16 12:43:18.433
ME4JF39LGJT005000 2018-08-16 12:48:43.570
ME4JF39LGJT005001 2018-08-16 12:45:20.320
ME4JF39LGJT005003 2018-08-16 12:27:51.207
ME4JF39LGJT005004 2018-08-16 12:45:24.617
ME4JF39LGJT005007 2018-08-16 12:39:13.550
ME4JF39LGJT005009 2018-08-16 12:27:56.553
ME4JF39LGJT005011 2018-08-16 12:38:56.800
ME4JF39LGJT005012 2018-08-16 12:50:53.890
ME4JF39LGJT005013 2018-08-16 12:38:27.473
ME4JF39LGJT005014 2018-08-16 12:38:33.690
ME4JF39LGJT005015 2018-08-16 12:47:04.227
ME4JF39LGJT005016 2018-08-16 12:39:07.973
ME4JF39LGJT005017 2018-08-16 12:39:32.520
ME4JF39LGJT005018 2018-08-16 12:40:06.283
ME4JF39LGJT005019 2018-08-16 12:39:44.737
ME4JF39LGJT005020 2018-08-16 12:39:18.817
ME4JF50AGJT341071 2018-08-16 11:33:59.723
ME4JF50AGJT344724 2018-08-16 13:30:00.280
ME4JF50AGJT344816 2018-08-16 13:28:37.483
ME4JF50AGJT344818 2018-08-16 13:29:28.207
ME4JF50AGJT344824 2018-08-16 13:28:08.810
ME4JF50AGJT344826 2018-08-16 13:29:41.537
ME4JF50AGJT344830 2018-08-16 13:27:57.753
ME4JF50AGJT344873 2018-08-16 13:28:50.010
ME4JF50AGJT344882 2018-08-16 13:27:32.163
ME4JF50AGJT344886 2018-08-16 13:27:43.630
ME4JF50BGJT157862 2018-08-16 13:20:02.167
ME4JF50BGJT157891 2018-08-16 13:22:06.110
ME4JF50BGJT157909 2018-08-16 13:18:54.710
ME4JF50BGJT157910 2018-08-16 13:26:59.917
ME4JF50BGJT157934 2018-08-16 13:23:02.110
ME4JF50BGJT157962 2018-08-16 13:22:22.743
ME4JF50BGJT157965 2018-08-16 13:27:18.143
ME4JF50BGJT157997 2018-08-16 13:22:47.020
ME4JF50BGJT158010 2018-08-16 13:27:08.810
ME4JF50BGJT158014 2018-08-16 13:21:02.943
ME4JF50BGJT158018 2018-08-16 13:20:52.230
ME4JF50BGJT158119 2018-08-16 12:24:18.903
ME4JF50BGJT158138 2018-08-16 12:45:29.070
P1022734-004 2018-08-28 15:23:44.017
I need to print on date 16 count is 300, on 28 count is 2 and rest 1,2...31 it should display 0.
sql
sql
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
edited Nov 12 '18 at 15:16
Caius Jard
10.2k11137
10.2k11137
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
asked Nov 12 '18 at 5:07
Pavitra Sharma
164
164
1
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09
add a comment |
1
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09
1
1
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09
add a comment |
2 Answers
2
active
oldest
votes
1
You can try like below - you've wrote the syntax wrongly - it should be count(case when your condition then Vehicle_Id END)
SELECT count(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN Vehicle_Id END) AS [1],
count(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN Vehicle_Id END) AS [2],
count(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN Vehicle_Id END) AS [3],
.......
FROM HVQ_Vehicle where month(timestamp) = 8;
answered Nov 12 '18 at 5:14
fa06
11.3k2917
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
|
show 1 more comment
0
I didn't have any problems implementing fa06's suggestion, though I swapped the COUNT for SUM and the use of MONTH/DAY with DATEPART. These are relatively non-ops though:
Here is setup:
CREATE TABLE hvq_vehicle
([Vehicle_Id] varchar(17), [timestamp] datetime)
;
INSERT INTO hvq_vehicle
([Vehicle_Id], [timestamp])
VALUES
('8901361307130', '2018-08-28 15:22:29'),
('ME4JF39JGJT011230', '2018-08-16 12:29:10'),
('ME4JF39JGJT011231', '2018-08-16 12:28:34'),
('ME4JF39JGJT011232', '2018-08-16 12:28:44'),
('ME4JF39JGJT011233', '2018-08-16 12:28:51'),
('ME4JF39JGJT011234', '2018-08-16 12:28:59'),
('ME4JF39JGJT011235', '2018-08-16 12:49:56'),
('ME4JF39JGJT011236', '2018-08-16 12:29:20'),
('ME4JF39JGJT011237', '2018-08-16 12:29:15'),
('ME4JF39JGJT011239', '2018-08-16 12:29:30'),
('ME4JF39JGJT011240', '2018-08-16 12:51:02'),
('ME4JF39JGJT011241', '2018-08-16 12:47:25'),
('ME4JF39JGJT011242', '2018-08-16 12:29:25'),
('ME4JF39JGJT011243', '2018-08-16 12:50:39'),
('ME4JF39JGJT011244', '2018-08-16 11:39:32'),
('ME4JF39JGJT011245', '2018-08-16 12:29:34'),
('ME4JF39JGJT011246', '2018-08-16 12:50:48'),
('ME4JF39JGJT011248', '2018-08-16 11:56:47'),
('ME4JF39JGJT011250', '2018-08-16 12:17:24'),
('ME4JF39JGJT011252', '2018-08-16 12:24:01'),
('ME4JF39JGJT011253', '2018-08-16 12:17:20'),
('ME4JF39JGJT011254', '2018-08-16 12:51:15'),
('ME4JF39JGJT011255', '2018-08-16 12:49:31'),
('ME4JF39JGJT011257', '2018-08-16 12:50:34'),
('ME4JF39JGJT011260', '2018-08-16 12:24:07'),
('ME4JF39JGJT011262', '2018-08-16 12:50:23'),
('ME4JF39JGJT011263', '2018-08-16 12:17:32'),
('ME4JF39JGJT011264', '2018-08-16 12:24:12'),
('ME4JF39JGJT011265', '2018-08-16 12:24:25'),
('ME4JF39JGJT011266', '2018-08-16 12:24:50'),
('ME4JF39JGJT011267', '2018-08-16 12:24:31'),
('ME4JF39JGJT011271', '2018-08-16 12:24:34'),
('ME4JF39JGJT011275', '2018-08-16 12:51:10'),
('ME4JF39JGJT011288', '2018-08-16 12:47:55'),
('ME4JF39JGJT011293', '2018-08-16 12:28:04'),
('ME4JF39JGJT011294', '2018-08-16 12:48:00'),
('ME4JF39JGJT011316', '2018-08-16 12:32:49'),
('ME4JF39JGJT011324', '2018-08-16 12:33:28'),
('ME4JF39JGJT011327', '2018-08-16 12:50:00'),
('ME4JF39JGJT011330', '2018-08-16 12:50:04'),
('ME4JF39JGJT011336', '2018-08-16 12:47:12'),
('ME4JF39JGJT011340', '2018-08-16 12:48:04'),
('ME4JF39JGJT011347', '2018-08-16 12:49:22'),
('ME4JF39JGJT011353', '2018-08-16 12:48:10'),
('ME4JF39JGJT011367', '2018-08-16 12:48:57'),
('ME4JF39JGJT011370', '2018-08-16 12:38:07'),
('ME4JF39JGJT011373', '2018-08-16 12:48:22'),
('ME4JF39JGJT011394', '2018-08-16 12:33:12'),
('ME4JF39JGJT011400', '2018-08-16 12:46:59'),
('ME4JF39JGJT011405', '2018-08-16 12:50:14'),
('ME4JF39JGJT011406', '2018-08-16 12:32:26'),
('ME4JF39JGJT011407', '2018-08-16 12:37:41'),
('ME4JF39JGJT011408', '2018-08-16 12:32:15'),
('ME4JF39JGJT011409', '2018-08-16 12:48:34'),
('ME4JF39JGJT011410', '2018-08-16 12:32:43'),
('ME4JF39JGJT011411', '2018-08-16 12:33:01'),
('ME4JF39JGJT011412', '2018-08-16 12:34:34'),
('ME4JF39JGJT011413', '2018-08-16 12:33:18'),
('ME4JF39JGJT011415', '2018-08-16 12:33:50'),
('ME4JF39JGJT011416', '2018-08-16 12:33:07'),
('ME4JF39JGJT011417', '2018-08-16 12:47:51'),
('ME4JF39JGJT011418', '2018-08-16 12:37:09'),
('ME4JF39JGJT011419', '2018-08-16 12:37:15'),
('ME4JF39JGJT011423', '2018-08-16 12:37:25'),
('ME4JF39JGJT011425', '2018-08-16 12:37:37'),
('ME4JF39JGJT011426', '2018-08-16 12:37:31'),
('ME4JF39JGJT011427', '2018-08-16 12:37:49'),
('ME4JF39JGJT011428', '2018-08-16 12:38:00'),
('ME4JF39JGJT011429', '2018-08-16 12:43:04'),
('ME4JF39JGJT011430', '2018-08-16 12:37:55'),
('ME4JF39JGJT011431', '2018-08-16 12:38:22'),
('ME4JF39JGJT011432', '2018-08-16 12:38:18'),
('ME4JF39JGJT011433', '2018-08-16 12:38:12'),
('ME4JF39JGJT011434', '2018-08-16 12:47:29'),
('ME4JF39JGJT011435', '2018-08-16 12:47:42'),
('ME4JF39JGJT011436', '2018-08-16 12:52:20'),
('ME4JF39JGJT011437', '2018-08-16 12:46:50'),
('ME4JF39JGJT011443', '2018-08-16 12:50:58'),
('ME4JF39JGJT011444', '2018-08-16 12:25:07'),
('ME4JF39KGJT021354', '2018-08-16 13:05:40'),
('ME4JF39KGJT021362', '2018-08-16 12:17:38'),
('ME4JF39KGJT021364', '2018-08-16 12:47:18'),
('ME4JF39KGJT021372', '2018-08-16 12:18:08'),
('ME4JF39KGJT021377', '2018-08-16 12:48:39'),
('ME4JF39KGJT021384', '2018-08-16 12:47:47'),
('ME4JF39KGJT021386', '2018-08-16 12:25:34'),
('ME4JF39KGJT021388', '2018-08-16 12:48:52'),
('ME4JF39KGJT021397', '2018-08-16 12:29:57'),
('ME4JF39KGJT021398', '2018-08-16 11:29:34'),
('ME4JF39KGJT021412', '2018-08-16 12:14:36'),
('ME4JF39KGJT021420', '2018-08-16 12:29:39'),
('ME4JF39KGJT021423', '2018-08-16 12:16:35'),
('ME4JF39KGJT021424', '2018-08-16 12:29:43'),
('ME4JF39KGJT021425', '2018-08-16 12:30:01'),
('ME4JF39KGJT021426', '2018-08-16 11:30:13'),
('ME4JF39KGJT021427', '2018-08-16 12:29:48'),
('ME4JF39KGJT021428', '2018-08-16 12:30:19'),
('ME4JF39KGJT021429', '2018-08-16 12:29:52'),
('ME4JF39KGJT021430', '2018-08-16 12:30:10'),
('ME4JF39KGJT021431', '2018-08-16 11:38:44'),
('ME4JF39KGJT021432', '2018-08-16 12:30:33'),
('ME4JF39KGJT021434', '2018-08-16 12:30:26'),
('ME4JF39KGJT021435', '2018-08-16 12:15:58'),
('ME4JF39KGJT021438', '2018-08-16 12:26:03'),
('ME4JF39KGJT021439', '2018-08-16 12:13:01'),
('ME4JF39KGJT021440', '2018-08-16 12:03:35'),
('ME4JF39KGJT021441', '2018-08-16 12:22:33'),
('ME4JF39KGJT021442', '2018-08-16 11:32:37'),
('ME4JF39KGJT021443', '2018-08-16 11:32:15'),
('ME4JF39KGJT021444', '2018-08-16 11:35:11'),
('ME4JF39KGJT021446', '2018-08-16 11:32:52'),
('ME4JF39KGJT021447', '2018-08-16 11:30:48'),
('ME4JF39KGJT021449', '2018-08-16 12:20:59'),
('ME4JF39KGJT021452', '2018-08-16 11:29:48'),
('ME4JF39KGJT021453', '2018-08-16 11:29:41'),
('ME4JF39KGJT021454', '2018-08-16 12:23:17'),
('ME4JF39KGJT021455', '2018-08-16 12:18:21'),
('ME4JF39KGJT021461', '2018-08-16 12:18:00'),
('ME4JF39KGJT021462', '2018-08-16 12:18:27'),
('ME4JF39KGJT021463', '2018-08-16 12:17:45'),
('ME4JF39KGJT021466', '2018-08-16 12:18:15'),
('ME4JF39KGJT021467', '2018-08-16 12:25:48'),
('ME4JF39KGJT021469', '2018-08-16 12:19:27'),
('ME4JF39KGJT021471', '2018-08-16 12:18:32'),
('ME4JF39KGJT021473', '2018-08-16 12:19:19'),
('ME4JF39KGJT021474', '2018-08-16 12:19:06'),
('ME4JF39KGJT021475', '2018-08-16 12:23:28'),
('ME4JF39KGJT021478', '2018-08-16 12:19:34'),
('ME4JF39KGJT021479', '2018-08-16 12:26:38'),
('ME4JF39KGJT021481', '2018-08-16 12:19:50'),
('ME4JF39KGJT021482', '2018-08-16 12:21:28'),
('ME4JF39KGJT021483', '2018-08-16 12:21:05'),
('ME4JF39KGJT021484', '2018-08-16 12:25:43'),
('ME4JF39KGJT021486', '2018-08-16 11:29:14'),
('ME4JF39KGJT021487', '2018-08-16 12:22:09'),
('ME4JF39KGJT021488', '2018-08-16 12:23:56'),
('ME4JF39KGJT021490', '2018-08-16 12:23:49'),
('ME4JF39KGJT021491', '2018-08-16 12:25:27'),
('ME4JF39KGJT021492', '2018-08-16 12:25:58'),
('ME4JF39KGJT021494', '2018-08-16 12:26:22'),
('ME4JF39KGJT021497', '2018-08-16 12:52:16'),
('ME4JF39KGJT021498', '2018-08-16 12:27:33'),
('ME4JF39KGJT021499', '2018-08-16 13:01:57'),
('ME4JF39KGJT021501', '2018-08-16 12:27:39'),
('ME4JF39KGJT021504', '2018-08-16 12:53:01'),
('ME4JF39KGJT021509', '2018-08-16 12:51:56'),
('ME4JF39KGJT021510', '2018-08-16 12:52:11'),
('ME4JF39KGJT021511', '2018-08-16 12:52:01'),
('ME4JF39KGJT021513', '2018-08-16 12:27:44'),
('ME4JF39KGJT021516', '2018-08-16 12:26:08'),
('ME4JF39KGJT021517', '2018-08-16 12:26:42'),
('ME4JF39KGJT021518', '2018-08-16 12:26:28'),
('ME4JF39KGJT021532', '2018-08-16 13:02:29'),
('ME4JF39KGJT021538', '2018-08-16 13:00:47'),
('ME4JF39KGJT021542', '2018-08-16 12:46:38'),
('ME4JF39KGJT021550', '2018-08-16 11:21:27'),
('ME4JF39KGJT021557', '2018-08-16 12:57:43'),
('ME4JF39KGJT021558', '2018-08-16 12:24:56'),
('ME4JF39KGJT021560', '2018-08-16 12:58:39'),
('ME4JF39KGJT021561', '2018-08-16 12:52:51'),
('ME4JF39KGJT021562', '2018-08-16 12:57:28'),
('ME4JF39KGJT021564', '2018-08-16 12:56:58'),
('ME4JF39KGJT021565', '2018-08-16 12:58:04'),
('ME4JF39KGJT021566', '2018-08-16 12:53:29'),
('ME4JF39KGJT021567', '2018-08-16 12:52:32'),
('ME4JF39KGJT021568', '2018-08-16 12:53:24'),
('ME4JF39KGJT021569', '2018-08-16 12:52:27'),
('ME4JF39KGJT021570', '2018-08-16 12:57:20'),
('ME4JF39KGJT021571', '2018-08-16 12:53:45'),
('ME4JF39KGJT021572', '2018-08-16 12:25:21'),
('ME4JF39KGJT021573', '2018-08-16 12:25:01'),
('ME4JF39KGJT021575', '2018-08-16 12:53:20'),
('ME4JF39KGJT021577', '2018-08-16 12:52:35'),
('ME4JF39KGJT021578', '2018-08-16 13:02:25'),
('ME4JF39KGJT021580', '2018-08-16 12:53:11'),
('ME4JF39KGJT021581', '2018-08-16 12:45:48'),
('ME4JF39KGJT021582', '2018-08-16 13:05:21'),
('ME4JF39KGJT021584', '2018-08-16 13:00:41'),
('ME4JF39KGJT021585', '2018-08-16 12:53:34'),
('ME4JF39KGJT021586', '2018-08-16 12:53:40'),
('ME4JF39KGJT021587', '2018-08-16 13:00:54'),
('ME4JF39KGJT021588', '2018-08-16 13:00:21'),
('ME4JF39KGJT021589', '2018-08-16 13:00:28'),
('ME4JF39KGJT021591', '2018-08-16 13:01:06'),
('ME4JF39KGJT021592', '2018-08-16 12:51:41'),
('ME4JF39KGJT021593', '2018-08-16 13:02:31'),
('ME4JF39KGJT021594', '2018-08-16 13:02:34'),
('ME4JF39KGJT021595', '2018-08-16 13:01:11'),
('ME4JF39KGJT021597', '2018-08-16 13:02:38'),
('ME4JF39KGJT021598', '2018-08-16 13:02:36'),
('ME4JF39KGJT021599', '2018-08-16 13:02:27'),
('ME4JF39KGJT021600', '2018-08-16 13:04:37'),
('ME4JF39KGJT021601', '2018-08-16 13:02:42'),
('ME4JF39KGJT021602', '2018-08-16 13:02:40'),
('ME4JF39KGJT021603', '2018-08-16 13:05:20'),
('ME4JF39KGJT021604', '2018-08-16 13:05:09'),
('ME4JF39KGJT021605', '2018-08-16 13:05:17'),
('ME4JF39KGJT021606', '2018-08-16 13:05:11'),
('ME4JF39KGJT021607', '2018-08-16 13:05:14'),
('ME4JF39KGJT021608', '2018-08-16 12:46:42'),
('ME4JF39KGJT021609', '2018-08-16 13:05:25'),
('ME4JF39KGJT021610', '2018-08-16 13:05:37'),
('ME4JF39KGJT021612', '2018-08-16 13:05:29'),
('ME4JF39KGJT021613', '2018-08-16 13:05:26'),
('ME4JF39KGJT021614', '2018-08-16 13:05:31'),
('ME4JF39KGJT021615', '2018-08-16 13:05:33'),
('ME4JF39KGJT021616', '2018-08-16 13:05:39'),
('ME4JF39KGJT021617', '2018-08-16 13:02:22'),
('ME4JF39KGJT021618', '2018-08-16 12:46:00'),
('ME4JF39KGJT021619', '2018-08-16 12:46:46'),
('ME4JF39KGJT021620', '2018-08-16 13:01:59'),
('ME4JF39KGJT021622', '2018-08-16 13:05:44'),
('ME4JF39KGJT021623', '2018-08-16 13:16:06'),
('ME4JF39KGJT021624', '2018-08-16 13:09:34'),
('ME4JF39KGJT021625', '2018-08-16 13:16:19'),
('ME4JF39KGJT021626', '2018-08-16 13:09:02'),
('ME4JF39KGJT021628', '2018-08-16 12:27:12'),
('ME4JF39KGJT021629', '2018-08-16 13:13:53'),
('ME4JF39KGJT021630', '2018-08-16 13:13:40'),
('ME4JF39KGJT021631', '2018-08-16 14:15:12'),
('ME4JF39KGJT021632', '2018-08-16 12:26:49'),
('ME4JF39KGJT021633', '2018-08-16 12:26:59'),
('ME4JF39KGJT021634', '2018-08-16 12:27:04'),
('ME4JF39KGJT021635', '2018-08-16 12:26:54'),
('ME4JF39KGJT021637', '2018-08-16 12:27:22'),
('ME4JF39KGJT021639', '2018-08-16 14:16:09'),
('ME4JF39KGJT021642', '2018-08-16 12:45:58'),
('ME4JF39KGJT021647', '2018-08-16 12:46:04'),
('ME4JF39LGJT004941', '2018-08-16 13:05:35'),
('ME4JF39LGJT004944', '2018-08-16 12:48:30'),
('ME4JF39LGJT004948', '2018-08-16 12:51:26'),
('ME4JF39LGJT004955', '2018-08-16 12:49:16'),
('ME4JF39LGJT004960', '2018-08-16 12:51:19'),
('ME4JF39LGJT004961', '2018-08-16 12:40:53'),
('ME4JF39LGJT004962', '2018-08-16 12:42:20'),
('ME4JF39LGJT004963', '2018-08-16 12:41:38'),
('ME4JF39LGJT004964', '2018-08-16 12:48:26'),
('ME4JF39LGJT004965', '2018-08-16 12:45:40'),
('ME4JF39LGJT004971', '2018-08-16 12:42:25'),
('ME4JF39LGJT004973', '2018-08-16 12:42:00'),
('ME4JF39LGJT004975', '2018-08-16 12:46:54'),
('ME4JF39LGJT004976', '2018-08-16 12:41:07'),
('ME4JF39LGJT004977', '2018-08-16 12:42:53'),
('ME4JF39LGJT004978', '2018-08-16 12:41:51'),
('ME4JF39LGJT004979', '2018-08-16 12:42:48'),
('ME4JF39LGJT004980', '2018-08-16 12:41:26'),
('ME4JF39LGJT004981', '2018-08-16 12:42:59'),
('ME4JF39LGJT004982', '2018-08-16 12:41:46'),
('ME4JF39LGJT004983', '2018-08-16 12:45:04'),
('ME4JF39LGJT004984', '2018-08-16 12:42:14'),
('ME4JF39LGJT004985', '2018-08-16 12:28:11'),
('ME4JF39LGJT004986', '2018-08-16 12:51:31'),
('ME4JF39LGJT004987', '2018-08-16 12:47:36'),
('ME4JF39LGJT004988', '2018-08-16 12:42:33'),
('ME4JF39LGJT004989', '2018-08-16 12:42:06'),
('ME4JF39LGJT004990', '2018-08-16 12:43:24'),
('ME4JF39LGJT004994', '2018-08-16 12:42:43'),
('ME4JF39LGJT004995', '2018-08-16 12:43:12'),
('ME4JF39LGJT004996', '2018-08-16 12:45:36'),
('ME4JF39LGJT004997', '2018-08-16 12:46:08'),
('ME4JF39LGJT004998', '2018-08-16 12:39:02'),
('ME4JF39LGJT004999', '2018-08-16 12:43:18'),
('ME4JF39LGJT005000', '2018-08-16 12:48:43'),
('ME4JF39LGJT005001', '2018-08-16 12:45:20'),
('ME4JF39LGJT005003', '2018-08-16 12:27:51'),
('ME4JF39LGJT005004', '2018-08-16 12:45:24'),
('ME4JF39LGJT005007', '2018-08-16 12:39:13'),
('ME4JF39LGJT005009', '2018-08-16 12:27:56'),
('ME4JF39LGJT005011', '2018-08-16 12:38:56'),
('ME4JF39LGJT005012', '2018-08-16 12:50:53'),
('ME4JF39LGJT005013', '2018-08-16 12:38:27'),
('ME4JF39LGJT005014', '2018-08-16 12:38:33'),
('ME4JF39LGJT005015', '2018-08-16 12:47:04'),
('ME4JF39LGJT005016', '2018-08-16 12:39:07'),
('ME4JF39LGJT005017', '2018-08-16 12:39:32'),
('ME4JF39LGJT005018', '2018-08-16 12:40:06'),
('ME4JF39LGJT005019', '2018-08-16 12:39:44'),
('ME4JF39LGJT005020', '2018-08-16 12:39:18'),
('ME4JF50AGJT341071', '2018-08-16 11:33:59'),
('ME4JF50AGJT344724', '2018-08-16 13:30:00'),
('ME4JF50AGJT344816', '2018-08-16 13:28:37'),
('ME4JF50AGJT344818', '2018-08-16 13:29:28'),
('ME4JF50AGJT344824', '2018-08-16 13:28:08'),
('ME4JF50AGJT344826', '2018-08-16 13:29:41'),
('ME4JF50AGJT344830', '2018-08-16 13:27:57'),
('ME4JF50AGJT344873', '2018-08-16 13:28:50'),
('ME4JF50AGJT344882', '2018-08-16 13:27:32'),
('ME4JF50AGJT344886', '2018-08-16 13:27:43'),
('ME4JF50BGJT157862', '2018-08-16 13:20:02'),
('ME4JF50BGJT157891', '2018-08-16 13:22:06'),
('ME4JF50BGJT157909', '2018-08-16 13:18:54'),
('ME4JF50BGJT157910', '2018-08-16 13:26:59'),
('ME4JF50BGJT157934', '2018-08-16 13:23:02'),
('ME4JF50BGJT157962', '2018-08-16 13:22:22'),
('ME4JF50BGJT157965', '2018-08-16 13:27:18'),
('ME4JF50BGJT157997', '2018-08-16 13:22:47'),
('ME4JF50BGJT158010', '2018-08-16 13:27:08'),
('ME4JF50BGJT158014', '2018-08-16 13:21:02'),
('ME4JF50BGJT158018', '2018-08-16 13:20:52'),
('ME4JF50BGJT158119', '2018-08-16 12:24:18'),
('ME4JF50BGJT158138', '2018-08-16 12:45:29'),
('P1022734-004', '2018-08-28 15:23:44')
;
The query doing the work:
SELECT
SUM(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 14 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 31 THEN 1 ELSE 0 END) AS [31]
FROM HVQ_Vehicle
WHERE DATEPART(MONTH, timestamp) = 8;
And the result requested:
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256222%2fcolumn-hvq-vehicle-timestamp-is-invalid-in-the-select-list-because-it-is-not-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can try like below - you've wrote the syntax wrongly - it should be count(case when your condition then Vehicle_Id END)
SELECT count(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN Vehicle_Id END) AS [1],
count(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN Vehicle_Id END) AS [2],
count(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN Vehicle_Id END) AS [3],
.......
FROM HVQ_Vehicle where month(timestamp) = 8;
answered Nov 12 '18 at 5:14
fa06
11.3k2917
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
|
show 1 more comment
1
You can try like below - you've wrote the syntax wrongly - it should be count(case when your condition then Vehicle_Id END)
SELECT count(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN Vehicle_Id END) AS [1],
count(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN Vehicle_Id END) AS [2],
count(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN Vehicle_Id END) AS [3],
.......
FROM HVQ_Vehicle where month(timestamp) = 8;
answered Nov 12 '18 at 5:14
fa06
11.3k2917
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
|
show 1 more comment
1
1
1
You can try like below - you've wrote the syntax wrongly - it should be count(case when your condition then Vehicle_Id END)
SELECT count(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN Vehicle_Id END) AS [1],
count(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN Vehicle_Id END) AS [2],
count(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN Vehicle_Id END) AS [3],
.......
FROM HVQ_Vehicle where month(timestamp) = 8;
answered Nov 12 '18 at 5:14
fa06
11.3k2917
You can try like below - you've wrote the syntax wrongly - it should be count(case when your condition then Vehicle_Id END)
SELECT count(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN Vehicle_Id END) AS [1],
count(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN Vehicle_Id END) AS [2],
count(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN Vehicle_Id END) AS [3],
.......
FROM HVQ_Vehicle where month(timestamp) = 8;
answered Nov 12 '18 at 5:14
fa06
11.3k2917
edited Nov 12 '18 at 9:13
answered Nov 12 '18 at 5:14
fa06
11.3k2917
answered Nov 12 '18 at 5:14
fa06
11.3k2917
answered Nov 12 '18 at 5:14
fa06
11.3k2917
11.3k2917
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
|
show 1 more comment
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
DATEPART() and MONTH() return INT
– Caius Jard
Nov 12 '18 at 6:20
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
Thanks, But it is giving the count of vehicle ids for whole month. Actually the requirement is to get the count of vehicle ids per day in a particular month.
– Pavitra Sharma
Nov 12 '18 at 8:55
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
@PavitraSharma, edited my answer - you can check now
– fa06
Nov 12 '18 at 9:14
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
Sorry sir, It,s giving the count of vehicle ids for whole month without group by clause also. I need to count vehicle ids for particular day, if there are no vehicle then answer should be 0. Please help me out.
– Pavitra Sharma
Nov 12 '18 at 9:25
1
1
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
fa06's query works fine - see dbfiddle.uk/…
– Caius Jard
Nov 12 '18 at 15:24
|
show 1 more comment
0
I didn't have any problems implementing fa06's suggestion, though I swapped the COUNT for SUM and the use of MONTH/DAY with DATEPART. These are relatively non-ops though:
Here is setup:
CREATE TABLE hvq_vehicle
([Vehicle_Id] varchar(17), [timestamp] datetime)
;
INSERT INTO hvq_vehicle
([Vehicle_Id], [timestamp])
VALUES
('8901361307130', '2018-08-28 15:22:29'),
('ME4JF39JGJT011230', '2018-08-16 12:29:10'),
('ME4JF39JGJT011231', '2018-08-16 12:28:34'),
('ME4JF39JGJT011232', '2018-08-16 12:28:44'),
('ME4JF39JGJT011233', '2018-08-16 12:28:51'),
('ME4JF39JGJT011234', '2018-08-16 12:28:59'),
('ME4JF39JGJT011235', '2018-08-16 12:49:56'),
('ME4JF39JGJT011236', '2018-08-16 12:29:20'),
('ME4JF39JGJT011237', '2018-08-16 12:29:15'),
('ME4JF39JGJT011239', '2018-08-16 12:29:30'),
('ME4JF39JGJT011240', '2018-08-16 12:51:02'),
('ME4JF39JGJT011241', '2018-08-16 12:47:25'),
('ME4JF39JGJT011242', '2018-08-16 12:29:25'),
('ME4JF39JGJT011243', '2018-08-16 12:50:39'),
('ME4JF39JGJT011244', '2018-08-16 11:39:32'),
('ME4JF39JGJT011245', '2018-08-16 12:29:34'),
('ME4JF39JGJT011246', '2018-08-16 12:50:48'),
('ME4JF39JGJT011248', '2018-08-16 11:56:47'),
('ME4JF39JGJT011250', '2018-08-16 12:17:24'),
('ME4JF39JGJT011252', '2018-08-16 12:24:01'),
('ME4JF39JGJT011253', '2018-08-16 12:17:20'),
('ME4JF39JGJT011254', '2018-08-16 12:51:15'),
('ME4JF39JGJT011255', '2018-08-16 12:49:31'),
('ME4JF39JGJT011257', '2018-08-16 12:50:34'),
('ME4JF39JGJT011260', '2018-08-16 12:24:07'),
('ME4JF39JGJT011262', '2018-08-16 12:50:23'),
('ME4JF39JGJT011263', '2018-08-16 12:17:32'),
('ME4JF39JGJT011264', '2018-08-16 12:24:12'),
('ME4JF39JGJT011265', '2018-08-16 12:24:25'),
('ME4JF39JGJT011266', '2018-08-16 12:24:50'),
('ME4JF39JGJT011267', '2018-08-16 12:24:31'),
('ME4JF39JGJT011271', '2018-08-16 12:24:34'),
('ME4JF39JGJT011275', '2018-08-16 12:51:10'),
('ME4JF39JGJT011288', '2018-08-16 12:47:55'),
('ME4JF39JGJT011293', '2018-08-16 12:28:04'),
('ME4JF39JGJT011294', '2018-08-16 12:48:00'),
('ME4JF39JGJT011316', '2018-08-16 12:32:49'),
('ME4JF39JGJT011324', '2018-08-16 12:33:28'),
('ME4JF39JGJT011327', '2018-08-16 12:50:00'),
('ME4JF39JGJT011330', '2018-08-16 12:50:04'),
('ME4JF39JGJT011336', '2018-08-16 12:47:12'),
('ME4JF39JGJT011340', '2018-08-16 12:48:04'),
('ME4JF39JGJT011347', '2018-08-16 12:49:22'),
('ME4JF39JGJT011353', '2018-08-16 12:48:10'),
('ME4JF39JGJT011367', '2018-08-16 12:48:57'),
('ME4JF39JGJT011370', '2018-08-16 12:38:07'),
('ME4JF39JGJT011373', '2018-08-16 12:48:22'),
('ME4JF39JGJT011394', '2018-08-16 12:33:12'),
('ME4JF39JGJT011400', '2018-08-16 12:46:59'),
('ME4JF39JGJT011405', '2018-08-16 12:50:14'),
('ME4JF39JGJT011406', '2018-08-16 12:32:26'),
('ME4JF39JGJT011407', '2018-08-16 12:37:41'),
('ME4JF39JGJT011408', '2018-08-16 12:32:15'),
('ME4JF39JGJT011409', '2018-08-16 12:48:34'),
('ME4JF39JGJT011410', '2018-08-16 12:32:43'),
('ME4JF39JGJT011411', '2018-08-16 12:33:01'),
('ME4JF39JGJT011412', '2018-08-16 12:34:34'),
('ME4JF39JGJT011413', '2018-08-16 12:33:18'),
('ME4JF39JGJT011415', '2018-08-16 12:33:50'),
('ME4JF39JGJT011416', '2018-08-16 12:33:07'),
('ME4JF39JGJT011417', '2018-08-16 12:47:51'),
('ME4JF39JGJT011418', '2018-08-16 12:37:09'),
('ME4JF39JGJT011419', '2018-08-16 12:37:15'),
('ME4JF39JGJT011423', '2018-08-16 12:37:25'),
('ME4JF39JGJT011425', '2018-08-16 12:37:37'),
('ME4JF39JGJT011426', '2018-08-16 12:37:31'),
('ME4JF39JGJT011427', '2018-08-16 12:37:49'),
('ME4JF39JGJT011428', '2018-08-16 12:38:00'),
('ME4JF39JGJT011429', '2018-08-16 12:43:04'),
('ME4JF39JGJT011430', '2018-08-16 12:37:55'),
('ME4JF39JGJT011431', '2018-08-16 12:38:22'),
('ME4JF39JGJT011432', '2018-08-16 12:38:18'),
('ME4JF39JGJT011433', '2018-08-16 12:38:12'),
('ME4JF39JGJT011434', '2018-08-16 12:47:29'),
('ME4JF39JGJT011435', '2018-08-16 12:47:42'),
('ME4JF39JGJT011436', '2018-08-16 12:52:20'),
('ME4JF39JGJT011437', '2018-08-16 12:46:50'),
('ME4JF39JGJT011443', '2018-08-16 12:50:58'),
('ME4JF39JGJT011444', '2018-08-16 12:25:07'),
('ME4JF39KGJT021354', '2018-08-16 13:05:40'),
('ME4JF39KGJT021362', '2018-08-16 12:17:38'),
('ME4JF39KGJT021364', '2018-08-16 12:47:18'),
('ME4JF39KGJT021372', '2018-08-16 12:18:08'),
('ME4JF39KGJT021377', '2018-08-16 12:48:39'),
('ME4JF39KGJT021384', '2018-08-16 12:47:47'),
('ME4JF39KGJT021386', '2018-08-16 12:25:34'),
('ME4JF39KGJT021388', '2018-08-16 12:48:52'),
('ME4JF39KGJT021397', '2018-08-16 12:29:57'),
('ME4JF39KGJT021398', '2018-08-16 11:29:34'),
('ME4JF39KGJT021412', '2018-08-16 12:14:36'),
('ME4JF39KGJT021420', '2018-08-16 12:29:39'),
('ME4JF39KGJT021423', '2018-08-16 12:16:35'),
('ME4JF39KGJT021424', '2018-08-16 12:29:43'),
('ME4JF39KGJT021425', '2018-08-16 12:30:01'),
('ME4JF39KGJT021426', '2018-08-16 11:30:13'),
('ME4JF39KGJT021427', '2018-08-16 12:29:48'),
('ME4JF39KGJT021428', '2018-08-16 12:30:19'),
('ME4JF39KGJT021429', '2018-08-16 12:29:52'),
('ME4JF39KGJT021430', '2018-08-16 12:30:10'),
('ME4JF39KGJT021431', '2018-08-16 11:38:44'),
('ME4JF39KGJT021432', '2018-08-16 12:30:33'),
('ME4JF39KGJT021434', '2018-08-16 12:30:26'),
('ME4JF39KGJT021435', '2018-08-16 12:15:58'),
('ME4JF39KGJT021438', '2018-08-16 12:26:03'),
('ME4JF39KGJT021439', '2018-08-16 12:13:01'),
('ME4JF39KGJT021440', '2018-08-16 12:03:35'),
('ME4JF39KGJT021441', '2018-08-16 12:22:33'),
('ME4JF39KGJT021442', '2018-08-16 11:32:37'),
('ME4JF39KGJT021443', '2018-08-16 11:32:15'),
('ME4JF39KGJT021444', '2018-08-16 11:35:11'),
('ME4JF39KGJT021446', '2018-08-16 11:32:52'),
('ME4JF39KGJT021447', '2018-08-16 11:30:48'),
('ME4JF39KGJT021449', '2018-08-16 12:20:59'),
('ME4JF39KGJT021452', '2018-08-16 11:29:48'),
('ME4JF39KGJT021453', '2018-08-16 11:29:41'),
('ME4JF39KGJT021454', '2018-08-16 12:23:17'),
('ME4JF39KGJT021455', '2018-08-16 12:18:21'),
('ME4JF39KGJT021461', '2018-08-16 12:18:00'),
('ME4JF39KGJT021462', '2018-08-16 12:18:27'),
('ME4JF39KGJT021463', '2018-08-16 12:17:45'),
('ME4JF39KGJT021466', '2018-08-16 12:18:15'),
('ME4JF39KGJT021467', '2018-08-16 12:25:48'),
('ME4JF39KGJT021469', '2018-08-16 12:19:27'),
('ME4JF39KGJT021471', '2018-08-16 12:18:32'),
('ME4JF39KGJT021473', '2018-08-16 12:19:19'),
('ME4JF39KGJT021474', '2018-08-16 12:19:06'),
('ME4JF39KGJT021475', '2018-08-16 12:23:28'),
('ME4JF39KGJT021478', '2018-08-16 12:19:34'),
('ME4JF39KGJT021479', '2018-08-16 12:26:38'),
('ME4JF39KGJT021481', '2018-08-16 12:19:50'),
('ME4JF39KGJT021482', '2018-08-16 12:21:28'),
('ME4JF39KGJT021483', '2018-08-16 12:21:05'),
('ME4JF39KGJT021484', '2018-08-16 12:25:43'),
('ME4JF39KGJT021486', '2018-08-16 11:29:14'),
('ME4JF39KGJT021487', '2018-08-16 12:22:09'),
('ME4JF39KGJT021488', '2018-08-16 12:23:56'),
('ME4JF39KGJT021490', '2018-08-16 12:23:49'),
('ME4JF39KGJT021491', '2018-08-16 12:25:27'),
('ME4JF39KGJT021492', '2018-08-16 12:25:58'),
('ME4JF39KGJT021494', '2018-08-16 12:26:22'),
('ME4JF39KGJT021497', '2018-08-16 12:52:16'),
('ME4JF39KGJT021498', '2018-08-16 12:27:33'),
('ME4JF39KGJT021499', '2018-08-16 13:01:57'),
('ME4JF39KGJT021501', '2018-08-16 12:27:39'),
('ME4JF39KGJT021504', '2018-08-16 12:53:01'),
('ME4JF39KGJT021509', '2018-08-16 12:51:56'),
('ME4JF39KGJT021510', '2018-08-16 12:52:11'),
('ME4JF39KGJT021511', '2018-08-16 12:52:01'),
('ME4JF39KGJT021513', '2018-08-16 12:27:44'),
('ME4JF39KGJT021516', '2018-08-16 12:26:08'),
('ME4JF39KGJT021517', '2018-08-16 12:26:42'),
('ME4JF39KGJT021518', '2018-08-16 12:26:28'),
('ME4JF39KGJT021532', '2018-08-16 13:02:29'),
('ME4JF39KGJT021538', '2018-08-16 13:00:47'),
('ME4JF39KGJT021542', '2018-08-16 12:46:38'),
('ME4JF39KGJT021550', '2018-08-16 11:21:27'),
('ME4JF39KGJT021557', '2018-08-16 12:57:43'),
('ME4JF39KGJT021558', '2018-08-16 12:24:56'),
('ME4JF39KGJT021560', '2018-08-16 12:58:39'),
('ME4JF39KGJT021561', '2018-08-16 12:52:51'),
('ME4JF39KGJT021562', '2018-08-16 12:57:28'),
('ME4JF39KGJT021564', '2018-08-16 12:56:58'),
('ME4JF39KGJT021565', '2018-08-16 12:58:04'),
('ME4JF39KGJT021566', '2018-08-16 12:53:29'),
('ME4JF39KGJT021567', '2018-08-16 12:52:32'),
('ME4JF39KGJT021568', '2018-08-16 12:53:24'),
('ME4JF39KGJT021569', '2018-08-16 12:52:27'),
('ME4JF39KGJT021570', '2018-08-16 12:57:20'),
('ME4JF39KGJT021571', '2018-08-16 12:53:45'),
('ME4JF39KGJT021572', '2018-08-16 12:25:21'),
('ME4JF39KGJT021573', '2018-08-16 12:25:01'),
('ME4JF39KGJT021575', '2018-08-16 12:53:20'),
('ME4JF39KGJT021577', '2018-08-16 12:52:35'),
('ME4JF39KGJT021578', '2018-08-16 13:02:25'),
('ME4JF39KGJT021580', '2018-08-16 12:53:11'),
('ME4JF39KGJT021581', '2018-08-16 12:45:48'),
('ME4JF39KGJT021582', '2018-08-16 13:05:21'),
('ME4JF39KGJT021584', '2018-08-16 13:00:41'),
('ME4JF39KGJT021585', '2018-08-16 12:53:34'),
('ME4JF39KGJT021586', '2018-08-16 12:53:40'),
('ME4JF39KGJT021587', '2018-08-16 13:00:54'),
('ME4JF39KGJT021588', '2018-08-16 13:00:21'),
('ME4JF39KGJT021589', '2018-08-16 13:00:28'),
('ME4JF39KGJT021591', '2018-08-16 13:01:06'),
('ME4JF39KGJT021592', '2018-08-16 12:51:41'),
('ME4JF39KGJT021593', '2018-08-16 13:02:31'),
('ME4JF39KGJT021594', '2018-08-16 13:02:34'),
('ME4JF39KGJT021595', '2018-08-16 13:01:11'),
('ME4JF39KGJT021597', '2018-08-16 13:02:38'),
('ME4JF39KGJT021598', '2018-08-16 13:02:36'),
('ME4JF39KGJT021599', '2018-08-16 13:02:27'),
('ME4JF39KGJT021600', '2018-08-16 13:04:37'),
('ME4JF39KGJT021601', '2018-08-16 13:02:42'),
('ME4JF39KGJT021602', '2018-08-16 13:02:40'),
('ME4JF39KGJT021603', '2018-08-16 13:05:20'),
('ME4JF39KGJT021604', '2018-08-16 13:05:09'),
('ME4JF39KGJT021605', '2018-08-16 13:05:17'),
('ME4JF39KGJT021606', '2018-08-16 13:05:11'),
('ME4JF39KGJT021607', '2018-08-16 13:05:14'),
('ME4JF39KGJT021608', '2018-08-16 12:46:42'),
('ME4JF39KGJT021609', '2018-08-16 13:05:25'),
('ME4JF39KGJT021610', '2018-08-16 13:05:37'),
('ME4JF39KGJT021612', '2018-08-16 13:05:29'),
('ME4JF39KGJT021613', '2018-08-16 13:05:26'),
('ME4JF39KGJT021614', '2018-08-16 13:05:31'),
('ME4JF39KGJT021615', '2018-08-16 13:05:33'),
('ME4JF39KGJT021616', '2018-08-16 13:05:39'),
('ME4JF39KGJT021617', '2018-08-16 13:02:22'),
('ME4JF39KGJT021618', '2018-08-16 12:46:00'),
('ME4JF39KGJT021619', '2018-08-16 12:46:46'),
('ME4JF39KGJT021620', '2018-08-16 13:01:59'),
('ME4JF39KGJT021622', '2018-08-16 13:05:44'),
('ME4JF39KGJT021623', '2018-08-16 13:16:06'),
('ME4JF39KGJT021624', '2018-08-16 13:09:34'),
('ME4JF39KGJT021625', '2018-08-16 13:16:19'),
('ME4JF39KGJT021626', '2018-08-16 13:09:02'),
('ME4JF39KGJT021628', '2018-08-16 12:27:12'),
('ME4JF39KGJT021629', '2018-08-16 13:13:53'),
('ME4JF39KGJT021630', '2018-08-16 13:13:40'),
('ME4JF39KGJT021631', '2018-08-16 14:15:12'),
('ME4JF39KGJT021632', '2018-08-16 12:26:49'),
('ME4JF39KGJT021633', '2018-08-16 12:26:59'),
('ME4JF39KGJT021634', '2018-08-16 12:27:04'),
('ME4JF39KGJT021635', '2018-08-16 12:26:54'),
('ME4JF39KGJT021637', '2018-08-16 12:27:22'),
('ME4JF39KGJT021639', '2018-08-16 14:16:09'),
('ME4JF39KGJT021642', '2018-08-16 12:45:58'),
('ME4JF39KGJT021647', '2018-08-16 12:46:04'),
('ME4JF39LGJT004941', '2018-08-16 13:05:35'),
('ME4JF39LGJT004944', '2018-08-16 12:48:30'),
('ME4JF39LGJT004948', '2018-08-16 12:51:26'),
('ME4JF39LGJT004955', '2018-08-16 12:49:16'),
('ME4JF39LGJT004960', '2018-08-16 12:51:19'),
('ME4JF39LGJT004961', '2018-08-16 12:40:53'),
('ME4JF39LGJT004962', '2018-08-16 12:42:20'),
('ME4JF39LGJT004963', '2018-08-16 12:41:38'),
('ME4JF39LGJT004964', '2018-08-16 12:48:26'),
('ME4JF39LGJT004965', '2018-08-16 12:45:40'),
('ME4JF39LGJT004971', '2018-08-16 12:42:25'),
('ME4JF39LGJT004973', '2018-08-16 12:42:00'),
('ME4JF39LGJT004975', '2018-08-16 12:46:54'),
('ME4JF39LGJT004976', '2018-08-16 12:41:07'),
('ME4JF39LGJT004977', '2018-08-16 12:42:53'),
('ME4JF39LGJT004978', '2018-08-16 12:41:51'),
('ME4JF39LGJT004979', '2018-08-16 12:42:48'),
('ME4JF39LGJT004980', '2018-08-16 12:41:26'),
('ME4JF39LGJT004981', '2018-08-16 12:42:59'),
('ME4JF39LGJT004982', '2018-08-16 12:41:46'),
('ME4JF39LGJT004983', '2018-08-16 12:45:04'),
('ME4JF39LGJT004984', '2018-08-16 12:42:14'),
('ME4JF39LGJT004985', '2018-08-16 12:28:11'),
('ME4JF39LGJT004986', '2018-08-16 12:51:31'),
('ME4JF39LGJT004987', '2018-08-16 12:47:36'),
('ME4JF39LGJT004988', '2018-08-16 12:42:33'),
('ME4JF39LGJT004989', '2018-08-16 12:42:06'),
('ME4JF39LGJT004990', '2018-08-16 12:43:24'),
('ME4JF39LGJT004994', '2018-08-16 12:42:43'),
('ME4JF39LGJT004995', '2018-08-16 12:43:12'),
('ME4JF39LGJT004996', '2018-08-16 12:45:36'),
('ME4JF39LGJT004997', '2018-08-16 12:46:08'),
('ME4JF39LGJT004998', '2018-08-16 12:39:02'),
('ME4JF39LGJT004999', '2018-08-16 12:43:18'),
('ME4JF39LGJT005000', '2018-08-16 12:48:43'),
('ME4JF39LGJT005001', '2018-08-16 12:45:20'),
('ME4JF39LGJT005003', '2018-08-16 12:27:51'),
('ME4JF39LGJT005004', '2018-08-16 12:45:24'),
('ME4JF39LGJT005007', '2018-08-16 12:39:13'),
('ME4JF39LGJT005009', '2018-08-16 12:27:56'),
('ME4JF39LGJT005011', '2018-08-16 12:38:56'),
('ME4JF39LGJT005012', '2018-08-16 12:50:53'),
('ME4JF39LGJT005013', '2018-08-16 12:38:27'),
('ME4JF39LGJT005014', '2018-08-16 12:38:33'),
('ME4JF39LGJT005015', '2018-08-16 12:47:04'),
('ME4JF39LGJT005016', '2018-08-16 12:39:07'),
('ME4JF39LGJT005017', '2018-08-16 12:39:32'),
('ME4JF39LGJT005018', '2018-08-16 12:40:06'),
('ME4JF39LGJT005019', '2018-08-16 12:39:44'),
('ME4JF39LGJT005020', '2018-08-16 12:39:18'),
('ME4JF50AGJT341071', '2018-08-16 11:33:59'),
('ME4JF50AGJT344724', '2018-08-16 13:30:00'),
('ME4JF50AGJT344816', '2018-08-16 13:28:37'),
('ME4JF50AGJT344818', '2018-08-16 13:29:28'),
('ME4JF50AGJT344824', '2018-08-16 13:28:08'),
('ME4JF50AGJT344826', '2018-08-16 13:29:41'),
('ME4JF50AGJT344830', '2018-08-16 13:27:57'),
('ME4JF50AGJT344873', '2018-08-16 13:28:50'),
('ME4JF50AGJT344882', '2018-08-16 13:27:32'),
('ME4JF50AGJT344886', '2018-08-16 13:27:43'),
('ME4JF50BGJT157862', '2018-08-16 13:20:02'),
('ME4JF50BGJT157891', '2018-08-16 13:22:06'),
('ME4JF50BGJT157909', '2018-08-16 13:18:54'),
('ME4JF50BGJT157910', '2018-08-16 13:26:59'),
('ME4JF50BGJT157934', '2018-08-16 13:23:02'),
('ME4JF50BGJT157962', '2018-08-16 13:22:22'),
('ME4JF50BGJT157965', '2018-08-16 13:27:18'),
('ME4JF50BGJT157997', '2018-08-16 13:22:47'),
('ME4JF50BGJT158010', '2018-08-16 13:27:08'),
('ME4JF50BGJT158014', '2018-08-16 13:21:02'),
('ME4JF50BGJT158018', '2018-08-16 13:20:52'),
('ME4JF50BGJT158119', '2018-08-16 12:24:18'),
('ME4JF50BGJT158138', '2018-08-16 12:45:29'),
('P1022734-004', '2018-08-28 15:23:44')
;
The query doing the work:
SELECT
SUM(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 14 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 31 THEN 1 ELSE 0 END) AS [31]
FROM HVQ_Vehicle
WHERE DATEPART(MONTH, timestamp) = 8;
And the result requested:
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
add a comment |
0
I didn't have any problems implementing fa06's suggestion, though I swapped the COUNT for SUM and the use of MONTH/DAY with DATEPART. These are relatively non-ops though:
Here is setup:
CREATE TABLE hvq_vehicle
([Vehicle_Id] varchar(17), [timestamp] datetime)
;
INSERT INTO hvq_vehicle
([Vehicle_Id], [timestamp])
VALUES
('8901361307130', '2018-08-28 15:22:29'),
('ME4JF39JGJT011230', '2018-08-16 12:29:10'),
('ME4JF39JGJT011231', '2018-08-16 12:28:34'),
('ME4JF39JGJT011232', '2018-08-16 12:28:44'),
('ME4JF39JGJT011233', '2018-08-16 12:28:51'),
('ME4JF39JGJT011234', '2018-08-16 12:28:59'),
('ME4JF39JGJT011235', '2018-08-16 12:49:56'),
('ME4JF39JGJT011236', '2018-08-16 12:29:20'),
('ME4JF39JGJT011237', '2018-08-16 12:29:15'),
('ME4JF39JGJT011239', '2018-08-16 12:29:30'),
('ME4JF39JGJT011240', '2018-08-16 12:51:02'),
('ME4JF39JGJT011241', '2018-08-16 12:47:25'),
('ME4JF39JGJT011242', '2018-08-16 12:29:25'),
('ME4JF39JGJT011243', '2018-08-16 12:50:39'),
('ME4JF39JGJT011244', '2018-08-16 11:39:32'),
('ME4JF39JGJT011245', '2018-08-16 12:29:34'),
('ME4JF39JGJT011246', '2018-08-16 12:50:48'),
('ME4JF39JGJT011248', '2018-08-16 11:56:47'),
('ME4JF39JGJT011250', '2018-08-16 12:17:24'),
('ME4JF39JGJT011252', '2018-08-16 12:24:01'),
('ME4JF39JGJT011253', '2018-08-16 12:17:20'),
('ME4JF39JGJT011254', '2018-08-16 12:51:15'),
('ME4JF39JGJT011255', '2018-08-16 12:49:31'),
('ME4JF39JGJT011257', '2018-08-16 12:50:34'),
('ME4JF39JGJT011260', '2018-08-16 12:24:07'),
('ME4JF39JGJT011262', '2018-08-16 12:50:23'),
('ME4JF39JGJT011263', '2018-08-16 12:17:32'),
('ME4JF39JGJT011264', '2018-08-16 12:24:12'),
('ME4JF39JGJT011265', '2018-08-16 12:24:25'),
('ME4JF39JGJT011266', '2018-08-16 12:24:50'),
('ME4JF39JGJT011267', '2018-08-16 12:24:31'),
('ME4JF39JGJT011271', '2018-08-16 12:24:34'),
('ME4JF39JGJT011275', '2018-08-16 12:51:10'),
('ME4JF39JGJT011288', '2018-08-16 12:47:55'),
('ME4JF39JGJT011293', '2018-08-16 12:28:04'),
('ME4JF39JGJT011294', '2018-08-16 12:48:00'),
('ME4JF39JGJT011316', '2018-08-16 12:32:49'),
('ME4JF39JGJT011324', '2018-08-16 12:33:28'),
('ME4JF39JGJT011327', '2018-08-16 12:50:00'),
('ME4JF39JGJT011330', '2018-08-16 12:50:04'),
('ME4JF39JGJT011336', '2018-08-16 12:47:12'),
('ME4JF39JGJT011340', '2018-08-16 12:48:04'),
('ME4JF39JGJT011347', '2018-08-16 12:49:22'),
('ME4JF39JGJT011353', '2018-08-16 12:48:10'),
('ME4JF39JGJT011367', '2018-08-16 12:48:57'),
('ME4JF39JGJT011370', '2018-08-16 12:38:07'),
('ME4JF39JGJT011373', '2018-08-16 12:48:22'),
('ME4JF39JGJT011394', '2018-08-16 12:33:12'),
('ME4JF39JGJT011400', '2018-08-16 12:46:59'),
('ME4JF39JGJT011405', '2018-08-16 12:50:14'),
('ME4JF39JGJT011406', '2018-08-16 12:32:26'),
('ME4JF39JGJT011407', '2018-08-16 12:37:41'),
('ME4JF39JGJT011408', '2018-08-16 12:32:15'),
('ME4JF39JGJT011409', '2018-08-16 12:48:34'),
('ME4JF39JGJT011410', '2018-08-16 12:32:43'),
('ME4JF39JGJT011411', '2018-08-16 12:33:01'),
('ME4JF39JGJT011412', '2018-08-16 12:34:34'),
('ME4JF39JGJT011413', '2018-08-16 12:33:18'),
('ME4JF39JGJT011415', '2018-08-16 12:33:50'),
('ME4JF39JGJT011416', '2018-08-16 12:33:07'),
('ME4JF39JGJT011417', '2018-08-16 12:47:51'),
('ME4JF39JGJT011418', '2018-08-16 12:37:09'),
('ME4JF39JGJT011419', '2018-08-16 12:37:15'),
('ME4JF39JGJT011423', '2018-08-16 12:37:25'),
('ME4JF39JGJT011425', '2018-08-16 12:37:37'),
('ME4JF39JGJT011426', '2018-08-16 12:37:31'),
('ME4JF39JGJT011427', '2018-08-16 12:37:49'),
('ME4JF39JGJT011428', '2018-08-16 12:38:00'),
('ME4JF39JGJT011429', '2018-08-16 12:43:04'),
('ME4JF39JGJT011430', '2018-08-16 12:37:55'),
('ME4JF39JGJT011431', '2018-08-16 12:38:22'),
('ME4JF39JGJT011432', '2018-08-16 12:38:18'),
('ME4JF39JGJT011433', '2018-08-16 12:38:12'),
('ME4JF39JGJT011434', '2018-08-16 12:47:29'),
('ME4JF39JGJT011435', '2018-08-16 12:47:42'),
('ME4JF39JGJT011436', '2018-08-16 12:52:20'),
('ME4JF39JGJT011437', '2018-08-16 12:46:50'),
('ME4JF39JGJT011443', '2018-08-16 12:50:58'),
('ME4JF39JGJT011444', '2018-08-16 12:25:07'),
('ME4JF39KGJT021354', '2018-08-16 13:05:40'),
('ME4JF39KGJT021362', '2018-08-16 12:17:38'),
('ME4JF39KGJT021364', '2018-08-16 12:47:18'),
('ME4JF39KGJT021372', '2018-08-16 12:18:08'),
('ME4JF39KGJT021377', '2018-08-16 12:48:39'),
('ME4JF39KGJT021384', '2018-08-16 12:47:47'),
('ME4JF39KGJT021386', '2018-08-16 12:25:34'),
('ME4JF39KGJT021388', '2018-08-16 12:48:52'),
('ME4JF39KGJT021397', '2018-08-16 12:29:57'),
('ME4JF39KGJT021398', '2018-08-16 11:29:34'),
('ME4JF39KGJT021412', '2018-08-16 12:14:36'),
('ME4JF39KGJT021420', '2018-08-16 12:29:39'),
('ME4JF39KGJT021423', '2018-08-16 12:16:35'),
('ME4JF39KGJT021424', '2018-08-16 12:29:43'),
('ME4JF39KGJT021425', '2018-08-16 12:30:01'),
('ME4JF39KGJT021426', '2018-08-16 11:30:13'),
('ME4JF39KGJT021427', '2018-08-16 12:29:48'),
('ME4JF39KGJT021428', '2018-08-16 12:30:19'),
('ME4JF39KGJT021429', '2018-08-16 12:29:52'),
('ME4JF39KGJT021430', '2018-08-16 12:30:10'),
('ME4JF39KGJT021431', '2018-08-16 11:38:44'),
('ME4JF39KGJT021432', '2018-08-16 12:30:33'),
('ME4JF39KGJT021434', '2018-08-16 12:30:26'),
('ME4JF39KGJT021435', '2018-08-16 12:15:58'),
('ME4JF39KGJT021438', '2018-08-16 12:26:03'),
('ME4JF39KGJT021439', '2018-08-16 12:13:01'),
('ME4JF39KGJT021440', '2018-08-16 12:03:35'),
('ME4JF39KGJT021441', '2018-08-16 12:22:33'),
('ME4JF39KGJT021442', '2018-08-16 11:32:37'),
('ME4JF39KGJT021443', '2018-08-16 11:32:15'),
('ME4JF39KGJT021444', '2018-08-16 11:35:11'),
('ME4JF39KGJT021446', '2018-08-16 11:32:52'),
('ME4JF39KGJT021447', '2018-08-16 11:30:48'),
('ME4JF39KGJT021449', '2018-08-16 12:20:59'),
('ME4JF39KGJT021452', '2018-08-16 11:29:48'),
('ME4JF39KGJT021453', '2018-08-16 11:29:41'),
('ME4JF39KGJT021454', '2018-08-16 12:23:17'),
('ME4JF39KGJT021455', '2018-08-16 12:18:21'),
('ME4JF39KGJT021461', '2018-08-16 12:18:00'),
('ME4JF39KGJT021462', '2018-08-16 12:18:27'),
('ME4JF39KGJT021463', '2018-08-16 12:17:45'),
('ME4JF39KGJT021466', '2018-08-16 12:18:15'),
('ME4JF39KGJT021467', '2018-08-16 12:25:48'),
('ME4JF39KGJT021469', '2018-08-16 12:19:27'),
('ME4JF39KGJT021471', '2018-08-16 12:18:32'),
('ME4JF39KGJT021473', '2018-08-16 12:19:19'),
('ME4JF39KGJT021474', '2018-08-16 12:19:06'),
('ME4JF39KGJT021475', '2018-08-16 12:23:28'),
('ME4JF39KGJT021478', '2018-08-16 12:19:34'),
('ME4JF39KGJT021479', '2018-08-16 12:26:38'),
('ME4JF39KGJT021481', '2018-08-16 12:19:50'),
('ME4JF39KGJT021482', '2018-08-16 12:21:28'),
('ME4JF39KGJT021483', '2018-08-16 12:21:05'),
('ME4JF39KGJT021484', '2018-08-16 12:25:43'),
('ME4JF39KGJT021486', '2018-08-16 11:29:14'),
('ME4JF39KGJT021487', '2018-08-16 12:22:09'),
('ME4JF39KGJT021488', '2018-08-16 12:23:56'),
('ME4JF39KGJT021490', '2018-08-16 12:23:49'),
('ME4JF39KGJT021491', '2018-08-16 12:25:27'),
('ME4JF39KGJT021492', '2018-08-16 12:25:58'),
('ME4JF39KGJT021494', '2018-08-16 12:26:22'),
('ME4JF39KGJT021497', '2018-08-16 12:52:16'),
('ME4JF39KGJT021498', '2018-08-16 12:27:33'),
('ME4JF39KGJT021499', '2018-08-16 13:01:57'),
('ME4JF39KGJT021501', '2018-08-16 12:27:39'),
('ME4JF39KGJT021504', '2018-08-16 12:53:01'),
('ME4JF39KGJT021509', '2018-08-16 12:51:56'),
('ME4JF39KGJT021510', '2018-08-16 12:52:11'),
('ME4JF39KGJT021511', '2018-08-16 12:52:01'),
('ME4JF39KGJT021513', '2018-08-16 12:27:44'),
('ME4JF39KGJT021516', '2018-08-16 12:26:08'),
('ME4JF39KGJT021517', '2018-08-16 12:26:42'),
('ME4JF39KGJT021518', '2018-08-16 12:26:28'),
('ME4JF39KGJT021532', '2018-08-16 13:02:29'),
('ME4JF39KGJT021538', '2018-08-16 13:00:47'),
('ME4JF39KGJT021542', '2018-08-16 12:46:38'),
('ME4JF39KGJT021550', '2018-08-16 11:21:27'),
('ME4JF39KGJT021557', '2018-08-16 12:57:43'),
('ME4JF39KGJT021558', '2018-08-16 12:24:56'),
('ME4JF39KGJT021560', '2018-08-16 12:58:39'),
('ME4JF39KGJT021561', '2018-08-16 12:52:51'),
('ME4JF39KGJT021562', '2018-08-16 12:57:28'),
('ME4JF39KGJT021564', '2018-08-16 12:56:58'),
('ME4JF39KGJT021565', '2018-08-16 12:58:04'),
('ME4JF39KGJT021566', '2018-08-16 12:53:29'),
('ME4JF39KGJT021567', '2018-08-16 12:52:32'),
('ME4JF39KGJT021568', '2018-08-16 12:53:24'),
('ME4JF39KGJT021569', '2018-08-16 12:52:27'),
('ME4JF39KGJT021570', '2018-08-16 12:57:20'),
('ME4JF39KGJT021571', '2018-08-16 12:53:45'),
('ME4JF39KGJT021572', '2018-08-16 12:25:21'),
('ME4JF39KGJT021573', '2018-08-16 12:25:01'),
('ME4JF39KGJT021575', '2018-08-16 12:53:20'),
('ME4JF39KGJT021577', '2018-08-16 12:52:35'),
('ME4JF39KGJT021578', '2018-08-16 13:02:25'),
('ME4JF39KGJT021580', '2018-08-16 12:53:11'),
('ME4JF39KGJT021581', '2018-08-16 12:45:48'),
('ME4JF39KGJT021582', '2018-08-16 13:05:21'),
('ME4JF39KGJT021584', '2018-08-16 13:00:41'),
('ME4JF39KGJT021585', '2018-08-16 12:53:34'),
('ME4JF39KGJT021586', '2018-08-16 12:53:40'),
('ME4JF39KGJT021587', '2018-08-16 13:00:54'),
('ME4JF39KGJT021588', '2018-08-16 13:00:21'),
('ME4JF39KGJT021589', '2018-08-16 13:00:28'),
('ME4JF39KGJT021591', '2018-08-16 13:01:06'),
('ME4JF39KGJT021592', '2018-08-16 12:51:41'),
('ME4JF39KGJT021593', '2018-08-16 13:02:31'),
('ME4JF39KGJT021594', '2018-08-16 13:02:34'),
('ME4JF39KGJT021595', '2018-08-16 13:01:11'),
('ME4JF39KGJT021597', '2018-08-16 13:02:38'),
('ME4JF39KGJT021598', '2018-08-16 13:02:36'),
('ME4JF39KGJT021599', '2018-08-16 13:02:27'),
('ME4JF39KGJT021600', '2018-08-16 13:04:37'),
('ME4JF39KGJT021601', '2018-08-16 13:02:42'),
('ME4JF39KGJT021602', '2018-08-16 13:02:40'),
('ME4JF39KGJT021603', '2018-08-16 13:05:20'),
('ME4JF39KGJT021604', '2018-08-16 13:05:09'),
('ME4JF39KGJT021605', '2018-08-16 13:05:17'),
('ME4JF39KGJT021606', '2018-08-16 13:05:11'),
('ME4JF39KGJT021607', '2018-08-16 13:05:14'),
('ME4JF39KGJT021608', '2018-08-16 12:46:42'),
('ME4JF39KGJT021609', '2018-08-16 13:05:25'),
('ME4JF39KGJT021610', '2018-08-16 13:05:37'),
('ME4JF39KGJT021612', '2018-08-16 13:05:29'),
('ME4JF39KGJT021613', '2018-08-16 13:05:26'),
('ME4JF39KGJT021614', '2018-08-16 13:05:31'),
('ME4JF39KGJT021615', '2018-08-16 13:05:33'),
('ME4JF39KGJT021616', '2018-08-16 13:05:39'),
('ME4JF39KGJT021617', '2018-08-16 13:02:22'),
('ME4JF39KGJT021618', '2018-08-16 12:46:00'),
('ME4JF39KGJT021619', '2018-08-16 12:46:46'),
('ME4JF39KGJT021620', '2018-08-16 13:01:59'),
('ME4JF39KGJT021622', '2018-08-16 13:05:44'),
('ME4JF39KGJT021623', '2018-08-16 13:16:06'),
('ME4JF39KGJT021624', '2018-08-16 13:09:34'),
('ME4JF39KGJT021625', '2018-08-16 13:16:19'),
('ME4JF39KGJT021626', '2018-08-16 13:09:02'),
('ME4JF39KGJT021628', '2018-08-16 12:27:12'),
('ME4JF39KGJT021629', '2018-08-16 13:13:53'),
('ME4JF39KGJT021630', '2018-08-16 13:13:40'),
('ME4JF39KGJT021631', '2018-08-16 14:15:12'),
('ME4JF39KGJT021632', '2018-08-16 12:26:49'),
('ME4JF39KGJT021633', '2018-08-16 12:26:59'),
('ME4JF39KGJT021634', '2018-08-16 12:27:04'),
('ME4JF39KGJT021635', '2018-08-16 12:26:54'),
('ME4JF39KGJT021637', '2018-08-16 12:27:22'),
('ME4JF39KGJT021639', '2018-08-16 14:16:09'),
('ME4JF39KGJT021642', '2018-08-16 12:45:58'),
('ME4JF39KGJT021647', '2018-08-16 12:46:04'),
('ME4JF39LGJT004941', '2018-08-16 13:05:35'),
('ME4JF39LGJT004944', '2018-08-16 12:48:30'),
('ME4JF39LGJT004948', '2018-08-16 12:51:26'),
('ME4JF39LGJT004955', '2018-08-16 12:49:16'),
('ME4JF39LGJT004960', '2018-08-16 12:51:19'),
('ME4JF39LGJT004961', '2018-08-16 12:40:53'),
('ME4JF39LGJT004962', '2018-08-16 12:42:20'),
('ME4JF39LGJT004963', '2018-08-16 12:41:38'),
('ME4JF39LGJT004964', '2018-08-16 12:48:26'),
('ME4JF39LGJT004965', '2018-08-16 12:45:40'),
('ME4JF39LGJT004971', '2018-08-16 12:42:25'),
('ME4JF39LGJT004973', '2018-08-16 12:42:00'),
('ME4JF39LGJT004975', '2018-08-16 12:46:54'),
('ME4JF39LGJT004976', '2018-08-16 12:41:07'),
('ME4JF39LGJT004977', '2018-08-16 12:42:53'),
('ME4JF39LGJT004978', '2018-08-16 12:41:51'),
('ME4JF39LGJT004979', '2018-08-16 12:42:48'),
('ME4JF39LGJT004980', '2018-08-16 12:41:26'),
('ME4JF39LGJT004981', '2018-08-16 12:42:59'),
('ME4JF39LGJT004982', '2018-08-16 12:41:46'),
('ME4JF39LGJT004983', '2018-08-16 12:45:04'),
('ME4JF39LGJT004984', '2018-08-16 12:42:14'),
('ME4JF39LGJT004985', '2018-08-16 12:28:11'),
('ME4JF39LGJT004986', '2018-08-16 12:51:31'),
('ME4JF39LGJT004987', '2018-08-16 12:47:36'),
('ME4JF39LGJT004988', '2018-08-16 12:42:33'),
('ME4JF39LGJT004989', '2018-08-16 12:42:06'),
('ME4JF39LGJT004990', '2018-08-16 12:43:24'),
('ME4JF39LGJT004994', '2018-08-16 12:42:43'),
('ME4JF39LGJT004995', '2018-08-16 12:43:12'),
('ME4JF39LGJT004996', '2018-08-16 12:45:36'),
('ME4JF39LGJT004997', '2018-08-16 12:46:08'),
('ME4JF39LGJT004998', '2018-08-16 12:39:02'),
('ME4JF39LGJT004999', '2018-08-16 12:43:18'),
('ME4JF39LGJT005000', '2018-08-16 12:48:43'),
('ME4JF39LGJT005001', '2018-08-16 12:45:20'),
('ME4JF39LGJT005003', '2018-08-16 12:27:51'),
('ME4JF39LGJT005004', '2018-08-16 12:45:24'),
('ME4JF39LGJT005007', '2018-08-16 12:39:13'),
('ME4JF39LGJT005009', '2018-08-16 12:27:56'),
('ME4JF39LGJT005011', '2018-08-16 12:38:56'),
('ME4JF39LGJT005012', '2018-08-16 12:50:53'),
('ME4JF39LGJT005013', '2018-08-16 12:38:27'),
('ME4JF39LGJT005014', '2018-08-16 12:38:33'),
('ME4JF39LGJT005015', '2018-08-16 12:47:04'),
('ME4JF39LGJT005016', '2018-08-16 12:39:07'),
('ME4JF39LGJT005017', '2018-08-16 12:39:32'),
('ME4JF39LGJT005018', '2018-08-16 12:40:06'),
('ME4JF39LGJT005019', '2018-08-16 12:39:44'),
('ME4JF39LGJT005020', '2018-08-16 12:39:18'),
('ME4JF50AGJT341071', '2018-08-16 11:33:59'),
('ME4JF50AGJT344724', '2018-08-16 13:30:00'),
('ME4JF50AGJT344816', '2018-08-16 13:28:37'),
('ME4JF50AGJT344818', '2018-08-16 13:29:28'),
('ME4JF50AGJT344824', '2018-08-16 13:28:08'),
('ME4JF50AGJT344826', '2018-08-16 13:29:41'),
('ME4JF50AGJT344830', '2018-08-16 13:27:57'),
('ME4JF50AGJT344873', '2018-08-16 13:28:50'),
('ME4JF50AGJT344882', '2018-08-16 13:27:32'),
('ME4JF50AGJT344886', '2018-08-16 13:27:43'),
('ME4JF50BGJT157862', '2018-08-16 13:20:02'),
('ME4JF50BGJT157891', '2018-08-16 13:22:06'),
('ME4JF50BGJT157909', '2018-08-16 13:18:54'),
('ME4JF50BGJT157910', '2018-08-16 13:26:59'),
('ME4JF50BGJT157934', '2018-08-16 13:23:02'),
('ME4JF50BGJT157962', '2018-08-16 13:22:22'),
('ME4JF50BGJT157965', '2018-08-16 13:27:18'),
('ME4JF50BGJT157997', '2018-08-16 13:22:47'),
('ME4JF50BGJT158010', '2018-08-16 13:27:08'),
('ME4JF50BGJT158014', '2018-08-16 13:21:02'),
('ME4JF50BGJT158018', '2018-08-16 13:20:52'),
('ME4JF50BGJT158119', '2018-08-16 12:24:18'),
('ME4JF50BGJT158138', '2018-08-16 12:45:29'),
('P1022734-004', '2018-08-28 15:23:44')
;
The query doing the work:
SELECT
SUM(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 14 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 31 THEN 1 ELSE 0 END) AS [31]
FROM HVQ_Vehicle
WHERE DATEPART(MONTH, timestamp) = 8;
And the result requested:
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
add a comment |
0
0
0
I didn't have any problems implementing fa06's suggestion, though I swapped the COUNT for SUM and the use of MONTH/DAY with DATEPART. These are relatively non-ops though:
Here is setup:
CREATE TABLE hvq_vehicle
([Vehicle_Id] varchar(17), [timestamp] datetime)
;
INSERT INTO hvq_vehicle
([Vehicle_Id], [timestamp])
VALUES
('8901361307130', '2018-08-28 15:22:29'),
('ME4JF39JGJT011230', '2018-08-16 12:29:10'),
('ME4JF39JGJT011231', '2018-08-16 12:28:34'),
('ME4JF39JGJT011232', '2018-08-16 12:28:44'),
('ME4JF39JGJT011233', '2018-08-16 12:28:51'),
('ME4JF39JGJT011234', '2018-08-16 12:28:59'),
('ME4JF39JGJT011235', '2018-08-16 12:49:56'),
('ME4JF39JGJT011236', '2018-08-16 12:29:20'),
('ME4JF39JGJT011237', '2018-08-16 12:29:15'),
('ME4JF39JGJT011239', '2018-08-16 12:29:30'),
('ME4JF39JGJT011240', '2018-08-16 12:51:02'),
('ME4JF39JGJT011241', '2018-08-16 12:47:25'),
('ME4JF39JGJT011242', '2018-08-16 12:29:25'),
('ME4JF39JGJT011243', '2018-08-16 12:50:39'),
('ME4JF39JGJT011244', '2018-08-16 11:39:32'),
('ME4JF39JGJT011245', '2018-08-16 12:29:34'),
('ME4JF39JGJT011246', '2018-08-16 12:50:48'),
('ME4JF39JGJT011248', '2018-08-16 11:56:47'),
('ME4JF39JGJT011250', '2018-08-16 12:17:24'),
('ME4JF39JGJT011252', '2018-08-16 12:24:01'),
('ME4JF39JGJT011253', '2018-08-16 12:17:20'),
('ME4JF39JGJT011254', '2018-08-16 12:51:15'),
('ME4JF39JGJT011255', '2018-08-16 12:49:31'),
('ME4JF39JGJT011257', '2018-08-16 12:50:34'),
('ME4JF39JGJT011260', '2018-08-16 12:24:07'),
('ME4JF39JGJT011262', '2018-08-16 12:50:23'),
('ME4JF39JGJT011263', '2018-08-16 12:17:32'),
('ME4JF39JGJT011264', '2018-08-16 12:24:12'),
('ME4JF39JGJT011265', '2018-08-16 12:24:25'),
('ME4JF39JGJT011266', '2018-08-16 12:24:50'),
('ME4JF39JGJT011267', '2018-08-16 12:24:31'),
('ME4JF39JGJT011271', '2018-08-16 12:24:34'),
('ME4JF39JGJT011275', '2018-08-16 12:51:10'),
('ME4JF39JGJT011288', '2018-08-16 12:47:55'),
('ME4JF39JGJT011293', '2018-08-16 12:28:04'),
('ME4JF39JGJT011294', '2018-08-16 12:48:00'),
('ME4JF39JGJT011316', '2018-08-16 12:32:49'),
('ME4JF39JGJT011324', '2018-08-16 12:33:28'),
('ME4JF39JGJT011327', '2018-08-16 12:50:00'),
('ME4JF39JGJT011330', '2018-08-16 12:50:04'),
('ME4JF39JGJT011336', '2018-08-16 12:47:12'),
('ME4JF39JGJT011340', '2018-08-16 12:48:04'),
('ME4JF39JGJT011347', '2018-08-16 12:49:22'),
('ME4JF39JGJT011353', '2018-08-16 12:48:10'),
('ME4JF39JGJT011367', '2018-08-16 12:48:57'),
('ME4JF39JGJT011370', '2018-08-16 12:38:07'),
('ME4JF39JGJT011373', '2018-08-16 12:48:22'),
('ME4JF39JGJT011394', '2018-08-16 12:33:12'),
('ME4JF39JGJT011400', '2018-08-16 12:46:59'),
('ME4JF39JGJT011405', '2018-08-16 12:50:14'),
('ME4JF39JGJT011406', '2018-08-16 12:32:26'),
('ME4JF39JGJT011407', '2018-08-16 12:37:41'),
('ME4JF39JGJT011408', '2018-08-16 12:32:15'),
('ME4JF39JGJT011409', '2018-08-16 12:48:34'),
('ME4JF39JGJT011410', '2018-08-16 12:32:43'),
('ME4JF39JGJT011411', '2018-08-16 12:33:01'),
('ME4JF39JGJT011412', '2018-08-16 12:34:34'),
('ME4JF39JGJT011413', '2018-08-16 12:33:18'),
('ME4JF39JGJT011415', '2018-08-16 12:33:50'),
('ME4JF39JGJT011416', '2018-08-16 12:33:07'),
('ME4JF39JGJT011417', '2018-08-16 12:47:51'),
('ME4JF39JGJT011418', '2018-08-16 12:37:09'),
('ME4JF39JGJT011419', '2018-08-16 12:37:15'),
('ME4JF39JGJT011423', '2018-08-16 12:37:25'),
('ME4JF39JGJT011425', '2018-08-16 12:37:37'),
('ME4JF39JGJT011426', '2018-08-16 12:37:31'),
('ME4JF39JGJT011427', '2018-08-16 12:37:49'),
('ME4JF39JGJT011428', '2018-08-16 12:38:00'),
('ME4JF39JGJT011429', '2018-08-16 12:43:04'),
('ME4JF39JGJT011430', '2018-08-16 12:37:55'),
('ME4JF39JGJT011431', '2018-08-16 12:38:22'),
('ME4JF39JGJT011432', '2018-08-16 12:38:18'),
('ME4JF39JGJT011433', '2018-08-16 12:38:12'),
('ME4JF39JGJT011434', '2018-08-16 12:47:29'),
('ME4JF39JGJT011435', '2018-08-16 12:47:42'),
('ME4JF39JGJT011436', '2018-08-16 12:52:20'),
('ME4JF39JGJT011437', '2018-08-16 12:46:50'),
('ME4JF39JGJT011443', '2018-08-16 12:50:58'),
('ME4JF39JGJT011444', '2018-08-16 12:25:07'),
('ME4JF39KGJT021354', '2018-08-16 13:05:40'),
('ME4JF39KGJT021362', '2018-08-16 12:17:38'),
('ME4JF39KGJT021364', '2018-08-16 12:47:18'),
('ME4JF39KGJT021372', '2018-08-16 12:18:08'),
('ME4JF39KGJT021377', '2018-08-16 12:48:39'),
('ME4JF39KGJT021384', '2018-08-16 12:47:47'),
('ME4JF39KGJT021386', '2018-08-16 12:25:34'),
('ME4JF39KGJT021388', '2018-08-16 12:48:52'),
('ME4JF39KGJT021397', '2018-08-16 12:29:57'),
('ME4JF39KGJT021398', '2018-08-16 11:29:34'),
('ME4JF39KGJT021412', '2018-08-16 12:14:36'),
('ME4JF39KGJT021420', '2018-08-16 12:29:39'),
('ME4JF39KGJT021423', '2018-08-16 12:16:35'),
('ME4JF39KGJT021424', '2018-08-16 12:29:43'),
('ME4JF39KGJT021425', '2018-08-16 12:30:01'),
('ME4JF39KGJT021426', '2018-08-16 11:30:13'),
('ME4JF39KGJT021427', '2018-08-16 12:29:48'),
('ME4JF39KGJT021428', '2018-08-16 12:30:19'),
('ME4JF39KGJT021429', '2018-08-16 12:29:52'),
('ME4JF39KGJT021430', '2018-08-16 12:30:10'),
('ME4JF39KGJT021431', '2018-08-16 11:38:44'),
('ME4JF39KGJT021432', '2018-08-16 12:30:33'),
('ME4JF39KGJT021434', '2018-08-16 12:30:26'),
('ME4JF39KGJT021435', '2018-08-16 12:15:58'),
('ME4JF39KGJT021438', '2018-08-16 12:26:03'),
('ME4JF39KGJT021439', '2018-08-16 12:13:01'),
('ME4JF39KGJT021440', '2018-08-16 12:03:35'),
('ME4JF39KGJT021441', '2018-08-16 12:22:33'),
('ME4JF39KGJT021442', '2018-08-16 11:32:37'),
('ME4JF39KGJT021443', '2018-08-16 11:32:15'),
('ME4JF39KGJT021444', '2018-08-16 11:35:11'),
('ME4JF39KGJT021446', '2018-08-16 11:32:52'),
('ME4JF39KGJT021447', '2018-08-16 11:30:48'),
('ME4JF39KGJT021449', '2018-08-16 12:20:59'),
('ME4JF39KGJT021452', '2018-08-16 11:29:48'),
('ME4JF39KGJT021453', '2018-08-16 11:29:41'),
('ME4JF39KGJT021454', '2018-08-16 12:23:17'),
('ME4JF39KGJT021455', '2018-08-16 12:18:21'),
('ME4JF39KGJT021461', '2018-08-16 12:18:00'),
('ME4JF39KGJT021462', '2018-08-16 12:18:27'),
('ME4JF39KGJT021463', '2018-08-16 12:17:45'),
('ME4JF39KGJT021466', '2018-08-16 12:18:15'),
('ME4JF39KGJT021467', '2018-08-16 12:25:48'),
('ME4JF39KGJT021469', '2018-08-16 12:19:27'),
('ME4JF39KGJT021471', '2018-08-16 12:18:32'),
('ME4JF39KGJT021473', '2018-08-16 12:19:19'),
('ME4JF39KGJT021474', '2018-08-16 12:19:06'),
('ME4JF39KGJT021475', '2018-08-16 12:23:28'),
('ME4JF39KGJT021478', '2018-08-16 12:19:34'),
('ME4JF39KGJT021479', '2018-08-16 12:26:38'),
('ME4JF39KGJT021481', '2018-08-16 12:19:50'),
('ME4JF39KGJT021482', '2018-08-16 12:21:28'),
('ME4JF39KGJT021483', '2018-08-16 12:21:05'),
('ME4JF39KGJT021484', '2018-08-16 12:25:43'),
('ME4JF39KGJT021486', '2018-08-16 11:29:14'),
('ME4JF39KGJT021487', '2018-08-16 12:22:09'),
('ME4JF39KGJT021488', '2018-08-16 12:23:56'),
('ME4JF39KGJT021490', '2018-08-16 12:23:49'),
('ME4JF39KGJT021491', '2018-08-16 12:25:27'),
('ME4JF39KGJT021492', '2018-08-16 12:25:58'),
('ME4JF39KGJT021494', '2018-08-16 12:26:22'),
('ME4JF39KGJT021497', '2018-08-16 12:52:16'),
('ME4JF39KGJT021498', '2018-08-16 12:27:33'),
('ME4JF39KGJT021499', '2018-08-16 13:01:57'),
('ME4JF39KGJT021501', '2018-08-16 12:27:39'),
('ME4JF39KGJT021504', '2018-08-16 12:53:01'),
('ME4JF39KGJT021509', '2018-08-16 12:51:56'),
('ME4JF39KGJT021510', '2018-08-16 12:52:11'),
('ME4JF39KGJT021511', '2018-08-16 12:52:01'),
('ME4JF39KGJT021513', '2018-08-16 12:27:44'),
('ME4JF39KGJT021516', '2018-08-16 12:26:08'),
('ME4JF39KGJT021517', '2018-08-16 12:26:42'),
('ME4JF39KGJT021518', '2018-08-16 12:26:28'),
('ME4JF39KGJT021532', '2018-08-16 13:02:29'),
('ME4JF39KGJT021538', '2018-08-16 13:00:47'),
('ME4JF39KGJT021542', '2018-08-16 12:46:38'),
('ME4JF39KGJT021550', '2018-08-16 11:21:27'),
('ME4JF39KGJT021557', '2018-08-16 12:57:43'),
('ME4JF39KGJT021558', '2018-08-16 12:24:56'),
('ME4JF39KGJT021560', '2018-08-16 12:58:39'),
('ME4JF39KGJT021561', '2018-08-16 12:52:51'),
('ME4JF39KGJT021562', '2018-08-16 12:57:28'),
('ME4JF39KGJT021564', '2018-08-16 12:56:58'),
('ME4JF39KGJT021565', '2018-08-16 12:58:04'),
('ME4JF39KGJT021566', '2018-08-16 12:53:29'),
('ME4JF39KGJT021567', '2018-08-16 12:52:32'),
('ME4JF39KGJT021568', '2018-08-16 12:53:24'),
('ME4JF39KGJT021569', '2018-08-16 12:52:27'),
('ME4JF39KGJT021570', '2018-08-16 12:57:20'),
('ME4JF39KGJT021571', '2018-08-16 12:53:45'),
('ME4JF39KGJT021572', '2018-08-16 12:25:21'),
('ME4JF39KGJT021573', '2018-08-16 12:25:01'),
('ME4JF39KGJT021575', '2018-08-16 12:53:20'),
('ME4JF39KGJT021577', '2018-08-16 12:52:35'),
('ME4JF39KGJT021578', '2018-08-16 13:02:25'),
('ME4JF39KGJT021580', '2018-08-16 12:53:11'),
('ME4JF39KGJT021581', '2018-08-16 12:45:48'),
('ME4JF39KGJT021582', '2018-08-16 13:05:21'),
('ME4JF39KGJT021584', '2018-08-16 13:00:41'),
('ME4JF39KGJT021585', '2018-08-16 12:53:34'),
('ME4JF39KGJT021586', '2018-08-16 12:53:40'),
('ME4JF39KGJT021587', '2018-08-16 13:00:54'),
('ME4JF39KGJT021588', '2018-08-16 13:00:21'),
('ME4JF39KGJT021589', '2018-08-16 13:00:28'),
('ME4JF39KGJT021591', '2018-08-16 13:01:06'),
('ME4JF39KGJT021592', '2018-08-16 12:51:41'),
('ME4JF39KGJT021593', '2018-08-16 13:02:31'),
('ME4JF39KGJT021594', '2018-08-16 13:02:34'),
('ME4JF39KGJT021595', '2018-08-16 13:01:11'),
('ME4JF39KGJT021597', '2018-08-16 13:02:38'),
('ME4JF39KGJT021598', '2018-08-16 13:02:36'),
('ME4JF39KGJT021599', '2018-08-16 13:02:27'),
('ME4JF39KGJT021600', '2018-08-16 13:04:37'),
('ME4JF39KGJT021601', '2018-08-16 13:02:42'),
('ME4JF39KGJT021602', '2018-08-16 13:02:40'),
('ME4JF39KGJT021603', '2018-08-16 13:05:20'),
('ME4JF39KGJT021604', '2018-08-16 13:05:09'),
('ME4JF39KGJT021605', '2018-08-16 13:05:17'),
('ME4JF39KGJT021606', '2018-08-16 13:05:11'),
('ME4JF39KGJT021607', '2018-08-16 13:05:14'),
('ME4JF39KGJT021608', '2018-08-16 12:46:42'),
('ME4JF39KGJT021609', '2018-08-16 13:05:25'),
('ME4JF39KGJT021610', '2018-08-16 13:05:37'),
('ME4JF39KGJT021612', '2018-08-16 13:05:29'),
('ME4JF39KGJT021613', '2018-08-16 13:05:26'),
('ME4JF39KGJT021614', '2018-08-16 13:05:31'),
('ME4JF39KGJT021615', '2018-08-16 13:05:33'),
('ME4JF39KGJT021616', '2018-08-16 13:05:39'),
('ME4JF39KGJT021617', '2018-08-16 13:02:22'),
('ME4JF39KGJT021618', '2018-08-16 12:46:00'),
('ME4JF39KGJT021619', '2018-08-16 12:46:46'),
('ME4JF39KGJT021620', '2018-08-16 13:01:59'),
('ME4JF39KGJT021622', '2018-08-16 13:05:44'),
('ME4JF39KGJT021623', '2018-08-16 13:16:06'),
('ME4JF39KGJT021624', '2018-08-16 13:09:34'),
('ME4JF39KGJT021625', '2018-08-16 13:16:19'),
('ME4JF39KGJT021626', '2018-08-16 13:09:02'),
('ME4JF39KGJT021628', '2018-08-16 12:27:12'),
('ME4JF39KGJT021629', '2018-08-16 13:13:53'),
('ME4JF39KGJT021630', '2018-08-16 13:13:40'),
('ME4JF39KGJT021631', '2018-08-16 14:15:12'),
('ME4JF39KGJT021632', '2018-08-16 12:26:49'),
('ME4JF39KGJT021633', '2018-08-16 12:26:59'),
('ME4JF39KGJT021634', '2018-08-16 12:27:04'),
('ME4JF39KGJT021635', '2018-08-16 12:26:54'),
('ME4JF39KGJT021637', '2018-08-16 12:27:22'),
('ME4JF39KGJT021639', '2018-08-16 14:16:09'),
('ME4JF39KGJT021642', '2018-08-16 12:45:58'),
('ME4JF39KGJT021647', '2018-08-16 12:46:04'),
('ME4JF39LGJT004941', '2018-08-16 13:05:35'),
('ME4JF39LGJT004944', '2018-08-16 12:48:30'),
('ME4JF39LGJT004948', '2018-08-16 12:51:26'),
('ME4JF39LGJT004955', '2018-08-16 12:49:16'),
('ME4JF39LGJT004960', '2018-08-16 12:51:19'),
('ME4JF39LGJT004961', '2018-08-16 12:40:53'),
('ME4JF39LGJT004962', '2018-08-16 12:42:20'),
('ME4JF39LGJT004963', '2018-08-16 12:41:38'),
('ME4JF39LGJT004964', '2018-08-16 12:48:26'),
('ME4JF39LGJT004965', '2018-08-16 12:45:40'),
('ME4JF39LGJT004971', '2018-08-16 12:42:25'),
('ME4JF39LGJT004973', '2018-08-16 12:42:00'),
('ME4JF39LGJT004975', '2018-08-16 12:46:54'),
('ME4JF39LGJT004976', '2018-08-16 12:41:07'),
('ME4JF39LGJT004977', '2018-08-16 12:42:53'),
('ME4JF39LGJT004978', '2018-08-16 12:41:51'),
('ME4JF39LGJT004979', '2018-08-16 12:42:48'),
('ME4JF39LGJT004980', '2018-08-16 12:41:26'),
('ME4JF39LGJT004981', '2018-08-16 12:42:59'),
('ME4JF39LGJT004982', '2018-08-16 12:41:46'),
('ME4JF39LGJT004983', '2018-08-16 12:45:04'),
('ME4JF39LGJT004984', '2018-08-16 12:42:14'),
('ME4JF39LGJT004985', '2018-08-16 12:28:11'),
('ME4JF39LGJT004986', '2018-08-16 12:51:31'),
('ME4JF39LGJT004987', '2018-08-16 12:47:36'),
('ME4JF39LGJT004988', '2018-08-16 12:42:33'),
('ME4JF39LGJT004989', '2018-08-16 12:42:06'),
('ME4JF39LGJT004990', '2018-08-16 12:43:24'),
('ME4JF39LGJT004994', '2018-08-16 12:42:43'),
('ME4JF39LGJT004995', '2018-08-16 12:43:12'),
('ME4JF39LGJT004996', '2018-08-16 12:45:36'),
('ME4JF39LGJT004997', '2018-08-16 12:46:08'),
('ME4JF39LGJT004998', '2018-08-16 12:39:02'),
('ME4JF39LGJT004999', '2018-08-16 12:43:18'),
('ME4JF39LGJT005000', '2018-08-16 12:48:43'),
('ME4JF39LGJT005001', '2018-08-16 12:45:20'),
('ME4JF39LGJT005003', '2018-08-16 12:27:51'),
('ME4JF39LGJT005004', '2018-08-16 12:45:24'),
('ME4JF39LGJT005007', '2018-08-16 12:39:13'),
('ME4JF39LGJT005009', '2018-08-16 12:27:56'),
('ME4JF39LGJT005011', '2018-08-16 12:38:56'),
('ME4JF39LGJT005012', '2018-08-16 12:50:53'),
('ME4JF39LGJT005013', '2018-08-16 12:38:27'),
('ME4JF39LGJT005014', '2018-08-16 12:38:33'),
('ME4JF39LGJT005015', '2018-08-16 12:47:04'),
('ME4JF39LGJT005016', '2018-08-16 12:39:07'),
('ME4JF39LGJT005017', '2018-08-16 12:39:32'),
('ME4JF39LGJT005018', '2018-08-16 12:40:06'),
('ME4JF39LGJT005019', '2018-08-16 12:39:44'),
('ME4JF39LGJT005020', '2018-08-16 12:39:18'),
('ME4JF50AGJT341071', '2018-08-16 11:33:59'),
('ME4JF50AGJT344724', '2018-08-16 13:30:00'),
('ME4JF50AGJT344816', '2018-08-16 13:28:37'),
('ME4JF50AGJT344818', '2018-08-16 13:29:28'),
('ME4JF50AGJT344824', '2018-08-16 13:28:08'),
('ME4JF50AGJT344826', '2018-08-16 13:29:41'),
('ME4JF50AGJT344830', '2018-08-16 13:27:57'),
('ME4JF50AGJT344873', '2018-08-16 13:28:50'),
('ME4JF50AGJT344882', '2018-08-16 13:27:32'),
('ME4JF50AGJT344886', '2018-08-16 13:27:43'),
('ME4JF50BGJT157862', '2018-08-16 13:20:02'),
('ME4JF50BGJT157891', '2018-08-16 13:22:06'),
('ME4JF50BGJT157909', '2018-08-16 13:18:54'),
('ME4JF50BGJT157910', '2018-08-16 13:26:59'),
('ME4JF50BGJT157934', '2018-08-16 13:23:02'),
('ME4JF50BGJT157962', '2018-08-16 13:22:22'),
('ME4JF50BGJT157965', '2018-08-16 13:27:18'),
('ME4JF50BGJT157997', '2018-08-16 13:22:47'),
('ME4JF50BGJT158010', '2018-08-16 13:27:08'),
('ME4JF50BGJT158014', '2018-08-16 13:21:02'),
('ME4JF50BGJT158018', '2018-08-16 13:20:52'),
('ME4JF50BGJT158119', '2018-08-16 12:24:18'),
('ME4JF50BGJT158138', '2018-08-16 12:45:29'),
('P1022734-004', '2018-08-28 15:23:44')
;
The query doing the work:
SELECT
SUM(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 14 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 31 THEN 1 ELSE 0 END) AS [31]
FROM HVQ_Vehicle
WHERE DATEPART(MONTH, timestamp) = 8;
And the result requested:
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
I didn't have any problems implementing fa06's suggestion, though I swapped the COUNT for SUM and the use of MONTH/DAY with DATEPART. These are relatively non-ops though:
Here is setup:
CREATE TABLE hvq_vehicle
([Vehicle_Id] varchar(17), [timestamp] datetime)
;
INSERT INTO hvq_vehicle
([Vehicle_Id], [timestamp])
VALUES
('8901361307130', '2018-08-28 15:22:29'),
('ME4JF39JGJT011230', '2018-08-16 12:29:10'),
('ME4JF39JGJT011231', '2018-08-16 12:28:34'),
('ME4JF39JGJT011232', '2018-08-16 12:28:44'),
('ME4JF39JGJT011233', '2018-08-16 12:28:51'),
('ME4JF39JGJT011234', '2018-08-16 12:28:59'),
('ME4JF39JGJT011235', '2018-08-16 12:49:56'),
('ME4JF39JGJT011236', '2018-08-16 12:29:20'),
('ME4JF39JGJT011237', '2018-08-16 12:29:15'),
('ME4JF39JGJT011239', '2018-08-16 12:29:30'),
('ME4JF39JGJT011240', '2018-08-16 12:51:02'),
('ME4JF39JGJT011241', '2018-08-16 12:47:25'),
('ME4JF39JGJT011242', '2018-08-16 12:29:25'),
('ME4JF39JGJT011243', '2018-08-16 12:50:39'),
('ME4JF39JGJT011244', '2018-08-16 11:39:32'),
('ME4JF39JGJT011245', '2018-08-16 12:29:34'),
('ME4JF39JGJT011246', '2018-08-16 12:50:48'),
('ME4JF39JGJT011248', '2018-08-16 11:56:47'),
('ME4JF39JGJT011250', '2018-08-16 12:17:24'),
('ME4JF39JGJT011252', '2018-08-16 12:24:01'),
('ME4JF39JGJT011253', '2018-08-16 12:17:20'),
('ME4JF39JGJT011254', '2018-08-16 12:51:15'),
('ME4JF39JGJT011255', '2018-08-16 12:49:31'),
('ME4JF39JGJT011257', '2018-08-16 12:50:34'),
('ME4JF39JGJT011260', '2018-08-16 12:24:07'),
('ME4JF39JGJT011262', '2018-08-16 12:50:23'),
('ME4JF39JGJT011263', '2018-08-16 12:17:32'),
('ME4JF39JGJT011264', '2018-08-16 12:24:12'),
('ME4JF39JGJT011265', '2018-08-16 12:24:25'),
('ME4JF39JGJT011266', '2018-08-16 12:24:50'),
('ME4JF39JGJT011267', '2018-08-16 12:24:31'),
('ME4JF39JGJT011271', '2018-08-16 12:24:34'),
('ME4JF39JGJT011275', '2018-08-16 12:51:10'),
('ME4JF39JGJT011288', '2018-08-16 12:47:55'),
('ME4JF39JGJT011293', '2018-08-16 12:28:04'),
('ME4JF39JGJT011294', '2018-08-16 12:48:00'),
('ME4JF39JGJT011316', '2018-08-16 12:32:49'),
('ME4JF39JGJT011324', '2018-08-16 12:33:28'),
('ME4JF39JGJT011327', '2018-08-16 12:50:00'),
('ME4JF39JGJT011330', '2018-08-16 12:50:04'),
('ME4JF39JGJT011336', '2018-08-16 12:47:12'),
('ME4JF39JGJT011340', '2018-08-16 12:48:04'),
('ME4JF39JGJT011347', '2018-08-16 12:49:22'),
('ME4JF39JGJT011353', '2018-08-16 12:48:10'),
('ME4JF39JGJT011367', '2018-08-16 12:48:57'),
('ME4JF39JGJT011370', '2018-08-16 12:38:07'),
('ME4JF39JGJT011373', '2018-08-16 12:48:22'),
('ME4JF39JGJT011394', '2018-08-16 12:33:12'),
('ME4JF39JGJT011400', '2018-08-16 12:46:59'),
('ME4JF39JGJT011405', '2018-08-16 12:50:14'),
('ME4JF39JGJT011406', '2018-08-16 12:32:26'),
('ME4JF39JGJT011407', '2018-08-16 12:37:41'),
('ME4JF39JGJT011408', '2018-08-16 12:32:15'),
('ME4JF39JGJT011409', '2018-08-16 12:48:34'),
('ME4JF39JGJT011410', '2018-08-16 12:32:43'),
('ME4JF39JGJT011411', '2018-08-16 12:33:01'),
('ME4JF39JGJT011412', '2018-08-16 12:34:34'),
('ME4JF39JGJT011413', '2018-08-16 12:33:18'),
('ME4JF39JGJT011415', '2018-08-16 12:33:50'),
('ME4JF39JGJT011416', '2018-08-16 12:33:07'),
('ME4JF39JGJT011417', '2018-08-16 12:47:51'),
('ME4JF39JGJT011418', '2018-08-16 12:37:09'),
('ME4JF39JGJT011419', '2018-08-16 12:37:15'),
('ME4JF39JGJT011423', '2018-08-16 12:37:25'),
('ME4JF39JGJT011425', '2018-08-16 12:37:37'),
('ME4JF39JGJT011426', '2018-08-16 12:37:31'),
('ME4JF39JGJT011427', '2018-08-16 12:37:49'),
('ME4JF39JGJT011428', '2018-08-16 12:38:00'),
('ME4JF39JGJT011429', '2018-08-16 12:43:04'),
('ME4JF39JGJT011430', '2018-08-16 12:37:55'),
('ME4JF39JGJT011431', '2018-08-16 12:38:22'),
('ME4JF39JGJT011432', '2018-08-16 12:38:18'),
('ME4JF39JGJT011433', '2018-08-16 12:38:12'),
('ME4JF39JGJT011434', '2018-08-16 12:47:29'),
('ME4JF39JGJT011435', '2018-08-16 12:47:42'),
('ME4JF39JGJT011436', '2018-08-16 12:52:20'),
('ME4JF39JGJT011437', '2018-08-16 12:46:50'),
('ME4JF39JGJT011443', '2018-08-16 12:50:58'),
('ME4JF39JGJT011444', '2018-08-16 12:25:07'),
('ME4JF39KGJT021354', '2018-08-16 13:05:40'),
('ME4JF39KGJT021362', '2018-08-16 12:17:38'),
('ME4JF39KGJT021364', '2018-08-16 12:47:18'),
('ME4JF39KGJT021372', '2018-08-16 12:18:08'),
('ME4JF39KGJT021377', '2018-08-16 12:48:39'),
('ME4JF39KGJT021384', '2018-08-16 12:47:47'),
('ME4JF39KGJT021386', '2018-08-16 12:25:34'),
('ME4JF39KGJT021388', '2018-08-16 12:48:52'),
('ME4JF39KGJT021397', '2018-08-16 12:29:57'),
('ME4JF39KGJT021398', '2018-08-16 11:29:34'),
('ME4JF39KGJT021412', '2018-08-16 12:14:36'),
('ME4JF39KGJT021420', '2018-08-16 12:29:39'),
('ME4JF39KGJT021423', '2018-08-16 12:16:35'),
('ME4JF39KGJT021424', '2018-08-16 12:29:43'),
('ME4JF39KGJT021425', '2018-08-16 12:30:01'),
('ME4JF39KGJT021426', '2018-08-16 11:30:13'),
('ME4JF39KGJT021427', '2018-08-16 12:29:48'),
('ME4JF39KGJT021428', '2018-08-16 12:30:19'),
('ME4JF39KGJT021429', '2018-08-16 12:29:52'),
('ME4JF39KGJT021430', '2018-08-16 12:30:10'),
('ME4JF39KGJT021431', '2018-08-16 11:38:44'),
('ME4JF39KGJT021432', '2018-08-16 12:30:33'),
('ME4JF39KGJT021434', '2018-08-16 12:30:26'),
('ME4JF39KGJT021435', '2018-08-16 12:15:58'),
('ME4JF39KGJT021438', '2018-08-16 12:26:03'),
('ME4JF39KGJT021439', '2018-08-16 12:13:01'),
('ME4JF39KGJT021440', '2018-08-16 12:03:35'),
('ME4JF39KGJT021441', '2018-08-16 12:22:33'),
('ME4JF39KGJT021442', '2018-08-16 11:32:37'),
('ME4JF39KGJT021443', '2018-08-16 11:32:15'),
('ME4JF39KGJT021444', '2018-08-16 11:35:11'),
('ME4JF39KGJT021446', '2018-08-16 11:32:52'),
('ME4JF39KGJT021447', '2018-08-16 11:30:48'),
('ME4JF39KGJT021449', '2018-08-16 12:20:59'),
('ME4JF39KGJT021452', '2018-08-16 11:29:48'),
('ME4JF39KGJT021453', '2018-08-16 11:29:41'),
('ME4JF39KGJT021454', '2018-08-16 12:23:17'),
('ME4JF39KGJT021455', '2018-08-16 12:18:21'),
('ME4JF39KGJT021461', '2018-08-16 12:18:00'),
('ME4JF39KGJT021462', '2018-08-16 12:18:27'),
('ME4JF39KGJT021463', '2018-08-16 12:17:45'),
('ME4JF39KGJT021466', '2018-08-16 12:18:15'),
('ME4JF39KGJT021467', '2018-08-16 12:25:48'),
('ME4JF39KGJT021469', '2018-08-16 12:19:27'),
('ME4JF39KGJT021471', '2018-08-16 12:18:32'),
('ME4JF39KGJT021473', '2018-08-16 12:19:19'),
('ME4JF39KGJT021474', '2018-08-16 12:19:06'),
('ME4JF39KGJT021475', '2018-08-16 12:23:28'),
('ME4JF39KGJT021478', '2018-08-16 12:19:34'),
('ME4JF39KGJT021479', '2018-08-16 12:26:38'),
('ME4JF39KGJT021481', '2018-08-16 12:19:50'),
('ME4JF39KGJT021482', '2018-08-16 12:21:28'),
('ME4JF39KGJT021483', '2018-08-16 12:21:05'),
('ME4JF39KGJT021484', '2018-08-16 12:25:43'),
('ME4JF39KGJT021486', '2018-08-16 11:29:14'),
('ME4JF39KGJT021487', '2018-08-16 12:22:09'),
('ME4JF39KGJT021488', '2018-08-16 12:23:56'),
('ME4JF39KGJT021490', '2018-08-16 12:23:49'),
('ME4JF39KGJT021491', '2018-08-16 12:25:27'),
('ME4JF39KGJT021492', '2018-08-16 12:25:58'),
('ME4JF39KGJT021494', '2018-08-16 12:26:22'),
('ME4JF39KGJT021497', '2018-08-16 12:52:16'),
('ME4JF39KGJT021498', '2018-08-16 12:27:33'),
('ME4JF39KGJT021499', '2018-08-16 13:01:57'),
('ME4JF39KGJT021501', '2018-08-16 12:27:39'),
('ME4JF39KGJT021504', '2018-08-16 12:53:01'),
('ME4JF39KGJT021509', '2018-08-16 12:51:56'),
('ME4JF39KGJT021510', '2018-08-16 12:52:11'),
('ME4JF39KGJT021511', '2018-08-16 12:52:01'),
('ME4JF39KGJT021513', '2018-08-16 12:27:44'),
('ME4JF39KGJT021516', '2018-08-16 12:26:08'),
('ME4JF39KGJT021517', '2018-08-16 12:26:42'),
('ME4JF39KGJT021518', '2018-08-16 12:26:28'),
('ME4JF39KGJT021532', '2018-08-16 13:02:29'),
('ME4JF39KGJT021538', '2018-08-16 13:00:47'),
('ME4JF39KGJT021542', '2018-08-16 12:46:38'),
('ME4JF39KGJT021550', '2018-08-16 11:21:27'),
('ME4JF39KGJT021557', '2018-08-16 12:57:43'),
('ME4JF39KGJT021558', '2018-08-16 12:24:56'),
('ME4JF39KGJT021560', '2018-08-16 12:58:39'),
('ME4JF39KGJT021561', '2018-08-16 12:52:51'),
('ME4JF39KGJT021562', '2018-08-16 12:57:28'),
('ME4JF39KGJT021564', '2018-08-16 12:56:58'),
('ME4JF39KGJT021565', '2018-08-16 12:58:04'),
('ME4JF39KGJT021566', '2018-08-16 12:53:29'),
('ME4JF39KGJT021567', '2018-08-16 12:52:32'),
('ME4JF39KGJT021568', '2018-08-16 12:53:24'),
('ME4JF39KGJT021569', '2018-08-16 12:52:27'),
('ME4JF39KGJT021570', '2018-08-16 12:57:20'),
('ME4JF39KGJT021571', '2018-08-16 12:53:45'),
('ME4JF39KGJT021572', '2018-08-16 12:25:21'),
('ME4JF39KGJT021573', '2018-08-16 12:25:01'),
('ME4JF39KGJT021575', '2018-08-16 12:53:20'),
('ME4JF39KGJT021577', '2018-08-16 12:52:35'),
('ME4JF39KGJT021578', '2018-08-16 13:02:25'),
('ME4JF39KGJT021580', '2018-08-16 12:53:11'),
('ME4JF39KGJT021581', '2018-08-16 12:45:48'),
('ME4JF39KGJT021582', '2018-08-16 13:05:21'),
('ME4JF39KGJT021584', '2018-08-16 13:00:41'),
('ME4JF39KGJT021585', '2018-08-16 12:53:34'),
('ME4JF39KGJT021586', '2018-08-16 12:53:40'),
('ME4JF39KGJT021587', '2018-08-16 13:00:54'),
('ME4JF39KGJT021588', '2018-08-16 13:00:21'),
('ME4JF39KGJT021589', '2018-08-16 13:00:28'),
('ME4JF39KGJT021591', '2018-08-16 13:01:06'),
('ME4JF39KGJT021592', '2018-08-16 12:51:41'),
('ME4JF39KGJT021593', '2018-08-16 13:02:31'),
('ME4JF39KGJT021594', '2018-08-16 13:02:34'),
('ME4JF39KGJT021595', '2018-08-16 13:01:11'),
('ME4JF39KGJT021597', '2018-08-16 13:02:38'),
('ME4JF39KGJT021598', '2018-08-16 13:02:36'),
('ME4JF39KGJT021599', '2018-08-16 13:02:27'),
('ME4JF39KGJT021600', '2018-08-16 13:04:37'),
('ME4JF39KGJT021601', '2018-08-16 13:02:42'),
('ME4JF39KGJT021602', '2018-08-16 13:02:40'),
('ME4JF39KGJT021603', '2018-08-16 13:05:20'),
('ME4JF39KGJT021604', '2018-08-16 13:05:09'),
('ME4JF39KGJT021605', '2018-08-16 13:05:17'),
('ME4JF39KGJT021606', '2018-08-16 13:05:11'),
('ME4JF39KGJT021607', '2018-08-16 13:05:14'),
('ME4JF39KGJT021608', '2018-08-16 12:46:42'),
('ME4JF39KGJT021609', '2018-08-16 13:05:25'),
('ME4JF39KGJT021610', '2018-08-16 13:05:37'),
('ME4JF39KGJT021612', '2018-08-16 13:05:29'),
('ME4JF39KGJT021613', '2018-08-16 13:05:26'),
('ME4JF39KGJT021614', '2018-08-16 13:05:31'),
('ME4JF39KGJT021615', '2018-08-16 13:05:33'),
('ME4JF39KGJT021616', '2018-08-16 13:05:39'),
('ME4JF39KGJT021617', '2018-08-16 13:02:22'),
('ME4JF39KGJT021618', '2018-08-16 12:46:00'),
('ME4JF39KGJT021619', '2018-08-16 12:46:46'),
('ME4JF39KGJT021620', '2018-08-16 13:01:59'),
('ME4JF39KGJT021622', '2018-08-16 13:05:44'),
('ME4JF39KGJT021623', '2018-08-16 13:16:06'),
('ME4JF39KGJT021624', '2018-08-16 13:09:34'),
('ME4JF39KGJT021625', '2018-08-16 13:16:19'),
('ME4JF39KGJT021626', '2018-08-16 13:09:02'),
('ME4JF39KGJT021628', '2018-08-16 12:27:12'),
('ME4JF39KGJT021629', '2018-08-16 13:13:53'),
('ME4JF39KGJT021630', '2018-08-16 13:13:40'),
('ME4JF39KGJT021631', '2018-08-16 14:15:12'),
('ME4JF39KGJT021632', '2018-08-16 12:26:49'),
('ME4JF39KGJT021633', '2018-08-16 12:26:59'),
('ME4JF39KGJT021634', '2018-08-16 12:27:04'),
('ME4JF39KGJT021635', '2018-08-16 12:26:54'),
('ME4JF39KGJT021637', '2018-08-16 12:27:22'),
('ME4JF39KGJT021639', '2018-08-16 14:16:09'),
('ME4JF39KGJT021642', '2018-08-16 12:45:58'),
('ME4JF39KGJT021647', '2018-08-16 12:46:04'),
('ME4JF39LGJT004941', '2018-08-16 13:05:35'),
('ME4JF39LGJT004944', '2018-08-16 12:48:30'),
('ME4JF39LGJT004948', '2018-08-16 12:51:26'),
('ME4JF39LGJT004955', '2018-08-16 12:49:16'),
('ME4JF39LGJT004960', '2018-08-16 12:51:19'),
('ME4JF39LGJT004961', '2018-08-16 12:40:53'),
('ME4JF39LGJT004962', '2018-08-16 12:42:20'),
('ME4JF39LGJT004963', '2018-08-16 12:41:38'),
('ME4JF39LGJT004964', '2018-08-16 12:48:26'),
('ME4JF39LGJT004965', '2018-08-16 12:45:40'),
('ME4JF39LGJT004971', '2018-08-16 12:42:25'),
('ME4JF39LGJT004973', '2018-08-16 12:42:00'),
('ME4JF39LGJT004975', '2018-08-16 12:46:54'),
('ME4JF39LGJT004976', '2018-08-16 12:41:07'),
('ME4JF39LGJT004977', '2018-08-16 12:42:53'),
('ME4JF39LGJT004978', '2018-08-16 12:41:51'),
('ME4JF39LGJT004979', '2018-08-16 12:42:48'),
('ME4JF39LGJT004980', '2018-08-16 12:41:26'),
('ME4JF39LGJT004981', '2018-08-16 12:42:59'),
('ME4JF39LGJT004982', '2018-08-16 12:41:46'),
('ME4JF39LGJT004983', '2018-08-16 12:45:04'),
('ME4JF39LGJT004984', '2018-08-16 12:42:14'),
('ME4JF39LGJT004985', '2018-08-16 12:28:11'),
('ME4JF39LGJT004986', '2018-08-16 12:51:31'),
('ME4JF39LGJT004987', '2018-08-16 12:47:36'),
('ME4JF39LGJT004988', '2018-08-16 12:42:33'),
('ME4JF39LGJT004989', '2018-08-16 12:42:06'),
('ME4JF39LGJT004990', '2018-08-16 12:43:24'),
('ME4JF39LGJT004994', '2018-08-16 12:42:43'),
('ME4JF39LGJT004995', '2018-08-16 12:43:12'),
('ME4JF39LGJT004996', '2018-08-16 12:45:36'),
('ME4JF39LGJT004997', '2018-08-16 12:46:08'),
('ME4JF39LGJT004998', '2018-08-16 12:39:02'),
('ME4JF39LGJT004999', '2018-08-16 12:43:18'),
('ME4JF39LGJT005000', '2018-08-16 12:48:43'),
('ME4JF39LGJT005001', '2018-08-16 12:45:20'),
('ME4JF39LGJT005003', '2018-08-16 12:27:51'),
('ME4JF39LGJT005004', '2018-08-16 12:45:24'),
('ME4JF39LGJT005007', '2018-08-16 12:39:13'),
('ME4JF39LGJT005009', '2018-08-16 12:27:56'),
('ME4JF39LGJT005011', '2018-08-16 12:38:56'),
('ME4JF39LGJT005012', '2018-08-16 12:50:53'),
('ME4JF39LGJT005013', '2018-08-16 12:38:27'),
('ME4JF39LGJT005014', '2018-08-16 12:38:33'),
('ME4JF39LGJT005015', '2018-08-16 12:47:04'),
('ME4JF39LGJT005016', '2018-08-16 12:39:07'),
('ME4JF39LGJT005017', '2018-08-16 12:39:32'),
('ME4JF39LGJT005018', '2018-08-16 12:40:06'),
('ME4JF39LGJT005019', '2018-08-16 12:39:44'),
('ME4JF39LGJT005020', '2018-08-16 12:39:18'),
('ME4JF50AGJT341071', '2018-08-16 11:33:59'),
('ME4JF50AGJT344724', '2018-08-16 13:30:00'),
('ME4JF50AGJT344816', '2018-08-16 13:28:37'),
('ME4JF50AGJT344818', '2018-08-16 13:29:28'),
('ME4JF50AGJT344824', '2018-08-16 13:28:08'),
('ME4JF50AGJT344826', '2018-08-16 13:29:41'),
('ME4JF50AGJT344830', '2018-08-16 13:27:57'),
('ME4JF50AGJT344873', '2018-08-16 13:28:50'),
('ME4JF50AGJT344882', '2018-08-16 13:27:32'),
('ME4JF50AGJT344886', '2018-08-16 13:27:43'),
('ME4JF50BGJT157862', '2018-08-16 13:20:02'),
('ME4JF50BGJT157891', '2018-08-16 13:22:06'),
('ME4JF50BGJT157909', '2018-08-16 13:18:54'),
('ME4JF50BGJT157910', '2018-08-16 13:26:59'),
('ME4JF50BGJT157934', '2018-08-16 13:23:02'),
('ME4JF50BGJT157962', '2018-08-16 13:22:22'),
('ME4JF50BGJT157965', '2018-08-16 13:27:18'),
('ME4JF50BGJT157997', '2018-08-16 13:22:47'),
('ME4JF50BGJT158010', '2018-08-16 13:27:08'),
('ME4JF50BGJT158014', '2018-08-16 13:21:02'),
('ME4JF50BGJT158018', '2018-08-16 13:20:52'),
('ME4JF50BGJT158119', '2018-08-16 12:24:18'),
('ME4JF50BGJT158138', '2018-08-16 12:45:29'),
('P1022734-004', '2018-08-28 15:23:44')
;
The query doing the work:
SELECT
SUM(CASE WHEN DATEPART(DAY, timestamp) = 1 THEN 1 ELSE 0 END) AS [1],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 2 THEN 1 ELSE 0 END) AS [2],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 3 THEN 1 ELSE 0 END) AS [3],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 4 THEN 1 ELSE 0 END) AS [4],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 5 THEN 1 ELSE 0 END) AS [5],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 6 THEN 1 ELSE 0 END) AS [6],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 7 THEN 1 ELSE 0 END) AS [7],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 8 THEN 1 ELSE 0 END) AS [8],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 9 THEN 1 ELSE 0 END) AS [9],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 10 THEN 1 ELSE 0 END) AS [10],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 11 THEN 1 ELSE 0 END) AS [11],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 12 THEN 1 ELSE 0 END) AS [12],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 13 THEN 1 ELSE 0 END) AS [13],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 14 THEN 1 ELSE 0 END) AS [14],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 15 THEN 1 ELSE 0 END) AS [15],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 16 THEN 1 ELSE 0 END) AS [16],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 17 THEN 1 ELSE 0 END) AS [17],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 18 THEN 1 ELSE 0 END) AS [18],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 19 THEN 1 ELSE 0 END) AS [19],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 20 THEN 1 ELSE 0 END) AS [20],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 21 THEN 1 ELSE 0 END) AS [21],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 22 THEN 1 ELSE 0 END) AS [22],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 23 THEN 1 ELSE 0 END) AS [23],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 24 THEN 1 ELSE 0 END) AS [24],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 25 THEN 1 ELSE 0 END) AS [25],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 26 THEN 1 ELSE 0 END) AS [26],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 27 THEN 1 ELSE 0 END) AS [27],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 28 THEN 1 ELSE 0 END) AS [28],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 29 THEN 1 ELSE 0 END) AS [29],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 30 THEN 1 ELSE 0 END) AS [30],
SUM(CASE WHEN DATEPART(DAY, timestamp) = 31 THEN 1 ELSE 0 END) AS [31]
FROM HVQ_Vehicle
WHERE DATEPART(MONTH, timestamp) = 8;
And the result requested:
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
answered Nov 12 '18 at 15:27
Caius Jard
10.2k11137
10.2k11137
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53256222%2fcolumn-hvq-vehicle-timestamp-is-invalid-in-the-select-list-because-it-is-not-c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
The error message is clear, but it can't easily be correlated to your actual code, because your code is not even remotely readable. Please take a few minutes and format your question.
– Tim Biegeleisen
Nov 12 '18 at 5:09