CODEWITHSUNDEEP
sql-server
DarkMoon
DarkMoon

Reputation: 1339

Get column value with COUNT of 0 in filtered result to display with count of 0

I have a simple SQL table that is tracking local backup status across a number of sites. I've got a view that tracks each site and backup run time, and whether it was overall successful or not. It looks something like this:

+--------+------------------+---------+
| SiteID | BackupTime       | Status  |
+--------+------------------+---------+
|   001  | 01/01/2020 04:00 | Success |
|   002  | 01/01/2020 04:00 | Success |
|   003  | 01/01/2020 04:00 | Success |
|   001  | 01/01/2020 08:00 | Success |
|   002  | 01/01/2020 08:00 | Unknown |
|   003  | 01/01/2020 08:00 | Success |
|   001  | 01/01/2020 12:00 | Unknown |
|   002  | 01/01/2020 12:00 | Success |
|   003  | 01/01/2020 12:00 | Success |
+--------+------------------+---------+

For reporting, I've been asked to get the overall status for the last 24 hours, with each slice marked with a percentage. So I have this query to get me the last 24 hours (offset by 4 hours because of frequency of backups and timezones involved):

SELECT t1.Status,
       COUNT(*) AS Total,
       ROUND((CAST(COUNT(*) AS FLOAT) / CAST(t2.TotalCount AS FLOAT)) * 100.00, 2) AS TotalPct
FROM [Backup].[dbo].[BackupStatus] AS t1,
  (SELECT CAST(COUNT(*) AS FLOAT) AS TotalCount
   FROM [Backup].[dbo].[BackupStatus]
   WHERE BackupTime > DATEADD(HH, -28, GETDATE())
     AND BackupTime < DATEADD(HH, -4, GETDATE())) AS t2
WHERE t1.BackupTime > DATEADD(HH, -28, GETDATE())
  AND t1.BackupTime < DATEADD(HH, -4, GETDATE())
GROUP BY Status, TotalCount
ORDER BY Status

The one issue I have with this is that if there were no problems during that time span, I'd only get a single row back for a Status of "Success". As this dataset is then being bound to a pie chart (using PowerShell), it results in an error when there's no second row, so I need that second row ("Unknown", 0, 0.00%) so it can bind the second slice and put the info in the legend.

I've tried creating a temp table with both of those values, each with a count of 0, and using a union to add them to the result, but that just created them as additional rows to my previous results.

Am I going about this wrong? Should I be looking into a different way of accomplishing this? Or have I missed something about the way I've tried already?

Upvotes: 1

Views: 54

Answers (1)

PSK
PSK

Reputation: 17943

You can try like following by adding a 'Unknown' row using UNION and exclude it if it already exists.

;WITH cte
AS (
    SELECT t1.STATUS
        ,COUNT(*) AS Total
        ,ROUND((CAST(COUNT(*) AS FLOAT) / CAST(t2.TotalCount AS FLOAT)) * 100.00, 2) AS TotalPct
    FROM [Backup].[dbo].[BackupStatus] AS t1
        ,(
            SELECT CAST(COUNT(*) AS FLOAT) AS TotalCount
            FROM [Backup].[dbo].[BackupStatus]
            WHERE BackupTime > DATEADD(HH, - 28, GETDATE())
                AND BackupTime < DATEADD(HH, - 4, GETDATE())
            ) AS t2
    WHERE t1.BackupTime > DATEADD(HH, - 28, GETDATE())
        AND t1.BackupTime < DATEADD(HH, - 4, GETDATE())
    GROUP BY STATUS
        ,TotalCount

    UNION ALL

    SELECT 'Unknown'
        ,0
        ,0
    )
,cte2 AS (
SELECT *
    ,row_number() OVER (PARTITION BY STATUS ORDER BY TotalPct DESC) rn
FROM cte
)
SELECT *
FROM cte2
WHERE rn = 1

Upvotes: 1

Related Questions