Nrthugu

Question

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.

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. — Nov 12 '18 at 5:09

score 1 · Accepted Answer · 2018-11-12 09:13:43Z

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;

edited Nov 12 '18 at 9:13

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

Caius Jard 10.2k11137 · Accepted Answer · 2018-11-12 15:27:23Z

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:

enter image description here

score 1 · Accepted Answer · 2018-11-12 09:13:43Z

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;

edited Nov 12 '18 at 9:13

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

Caius Jard 10.2k11137 · Accepted Answer · 2018-11-12 15:27:23Z

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:

enter image description here

搜尋此網誌

Nrthugu

Column 'HVQ_Vehicle.timestamp' is invalid in the select list because it is not contained in either an...

2 Answers
2

Your Answer

Post as a guest

2 Answers
2

2 Answers
2

Post as a guest

Popular posts from this blog

Full-time equivalent

さくらももこ

13 indicted, 8 arrested in Calif. drug cartel investigation

Column 'HVQ_Vehicle.timestamp' is invalid in the select list because it is not contained in either an...

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Post as a guest

2 Answers 2

2 Answers 2

Sign up or log in

Post as a guest

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Sign up or log in

Post as a guest

Popular posts from this blog

Full-time equivalent

さくらももこ

13 indicted, 8 arrested in Calif. drug cartel investigation

2 Answers
2

2 Answers
2

2 Answers
2