Return to Question

The results from this query will provide the data for a pie chart that will show the breakdown of how many checks were done by each site and I need to show the ones who have no checks done as a zero entry.

I looked on Stack Exchange and use of the NULLIF function seems like it may be helpful but I can't get it to work with this query.

Any help appreciated.

Graham Sivill

I looked on Stack Exchange and use of the NULLIF function seems like it may be helpful but I can't get it to work with this query.

This is the raw data of the AAHOfficerDailyActivityReport table:

AAHOfficerDailyActivityReportID fkUserID    fkSiteID    ShiftType   DateOfReport    MonthOfReport   SearchTypePerson    SearchTypeLocker    SearchTypeSpotRandom    SearchTypePersVehicle   SearchTypeVisitorContractorVehicle  SearchTypeCompanyVehicle    SearchTypeToilet    PatrolExternal  PatrolCarPark   PatrolPerimeter PatrolInternal  VehicleCheckAmbientLine VehicleCheckFridgeLine  VehicleCheckSealChecks  OtherChecksIDCards  OtherChecksIncidentReports  OtherChecksColdStoreChecks  RecordIsDeletedYN
1   1   945 Day 2019-05-18 00:00:00.000 5   3   3   3   3   3   3   3   4   4   4   4   2   2   2   5   5   5   0
2   1   948 Day 2019-05-17 01:30:00.000 5   3   3   3   3   3   3   3   4   4   4   4   2   2   2   5   5   5   0
3   476 945 Day 2019-05-20 00:00:00.000 5   8   0   0   0   0   8   0   0   0   0   0   0   0   0   150 2   0   1
4   476 951 Day 2019-05-31 00:00:00.000 5   0   0   0   0   0   0   0   0   2   0   0   2   0   2   3   0   24  0
5   428 952 Day 2019-06-01 00:00:00.000 6   3   3   0   0   0   0   3   3   3   3   0   0   0   0   0   0   0   0
6   450 951 Night   2019-05-31 00:00:00.000 5   1   0   0   1   0   0   0   0   0   0   0   1   0   0   0   0   24  0
7   479 947 Night   2019-05-31 06:10:06.070 5   0   0   0   7   0   0   0   0   0   0   0   0   0   0   8   0   0   0
8   450 0   Day 2019-06-01 00:00:00.000 6   1   0   0   0   0   0   0   0   0   0   0   0   0   0   3   0   24  0
9   450 951 Day 2019-06-02 04:45:00.000 6   1   0   0   0   0   0   0   0   0   0   0   0   0   0   3   0   24  0
10  459 952 Day 2019-06-02 00:00:00.000 6   0   2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   1
11  459 952 Day 2019-06-02 15:15:00.000 6   56  2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   1
12  459 952 Day 2019-06-02 15:15:00.000 6   56  2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   1
13  459 952 Day 2019-06-02 15:15:00.000 6   0   2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   1
14  459 0   Day 2019-06-02 15:22:43.553 6   0   2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   0
15  459 952 Day 2019-06-02 15:26:00.000 6   0   2   0   0   0   0   2   2   2   2   0   0   0   0   0   0   0   1
16  459 952 Day 2019-06-03 00:00:00.000 6   120 2   0   0   0   0   3   3   0   0   0   0   0   0   18  0   0   0
17  459 952 Day 2019-06-03 14:31:00.000 6   120 2   0   0   0   0   3   3   0   0   0   0   0   0   18  0   0   1
18  535 952 Day 2019-06-03 15:42:12.380 6   55  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
19  541 952 Day 2019-06-03 15:44:34.177 6   55  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
20  459 952 Day 2019-06-03 00:00:00.000 6   120 0   0   0   0   0   0   0   0   0   0   0   0   0   36  0   0   0
21  479 947 Night   2019-06-02 06:11:00.000 6   0   0   0   0   0   0   0   3   1   0   0   0   0   0   0   0   0   0
22  428 952 Night   2019-06-04 00:00:00.000 6   2   4   0   0   0   0   4   4   4   4   0   0   0   0   0   0   0   0
23  479 947 Night   2019-06-03 00:00:00.000 6   0   0   0   7   0   0   0   0   1   0   0   0   0   0   9   0   0   1
24  438 945 Day 2019-06-04 00:00:00.000 6   7   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
25  476 947 Day 2019-06-03 09:27:00.000 6   0   0   0   0   0   10  0   0   0   0   0   0   0   0   4   0   0   1
26  476 947 Day 2019-06-03 09:27:00.000 6   7   0   0   0   0   10  0   0   0   0   0   0   0   0   4   0   0   1
27  535 952 Day 2019-06-04 00:00:00.000 6   70  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
28  541 952 Day 2019-06-04 00:00:00.000 6   70  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
29  438 945 Day 2019-06-04 00:00:00.000 6   0   0   0   7   0   33  1   0   0   0   0   0   10  0   16  0   0   0
30  438 945 Day 2019-06-04 00:00:00.000 6   0   0   0   7   0   33  1   0   0   0   0   0   10  0   16  0   0   0
31  476 947 Day 2019-06-04 00:00:00.000 6   0   0   0   5   0   0   0   0   2   0   0   0   0   0   1   0   0   0
32  428 952 Night   2019-06-05 00:00:00.000 6   25  4   0   0   0   0   4   4   4   4   0   0   0   0   0   0   0   0
33  422 945 Night   2019-06-04 00:00:00.000 6   0   0   0   3   0   2   1   4   4   4   0   0   0   0   4   0   0   0
34  479 947 Night   2019-06-04 00:00:00.000 6   0   0   0   7   0   0   0   0   1   0   0   0   0   0   5   0   0   1
35  364 949 Night   2019-06-05 00:00:00.000 6   5   0   0   3   0   0   0   3   3   3   0   0   0   0   20  0   0   0
36  476 947 Day 2019-06-05 13:52:27.423 6   0   0   0   7   0   0   0   0   2   0   0   0   0   0   6   0   0   0
37  541 952 Day 2019-06-05 00:00:00.000 6   60  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
38  535 952 Day 2019-06-05 00:00:00.000 6   60  3   0   0   0   10  3   3   3   3   0   0   0   0   18  0   0   0
39  364 949 Day 2019-06-05 00:00:00.000 6   0   0   0   6   0   0   0   1   1   1   0   0   0   0   12  0   0   0
40  476 951 Day 2019-06-05 00:00:00.000 6   0   0   0   0   0   0   0   0   0   0   0   3   2   3   7   0   24  0
41  438 945 Day 2019-06-05 00:00:00.000 6   0   0   0   7   0   30  1   0   0   0   0   0   10  0   16  1   0   0