1

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?

DarkMoon
  • 1,169
  • 3
  • 9
  • 16

1 Answers1

1

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
PSK
  • 17,547
  • 5
  • 32
  • 43
  • Ah, I think I see. You're adding a row for 'Unknown' with the union select, then partitioning by status and then sorting descending on TotalPct, and then grabbing the first row for each Status. Is that right? Seems to work, but will try to massage it a bit so we get results in the event of all failures. Also, what's the initial semi-colon for? The query runs with or without it, is it a typo, or does it serve a purpose? And thank you so much! – DarkMoon Feb 17 '20 at 05:56
  • You understanding is correct. Semicolon is not typo you can read more about this at https://stackoverflow.com/questions/6938060/common-table-expression-why-semicolon – PSK Feb 17 '20 at 06:02
  • Ah, I never knew SQL had an optional terminator. TIL. Thank you again. :-) – DarkMoon Feb 17 '20 at 06:03
  • For FaIlure case also it will work same, you can add one more row using unionw, rest the ROW_NUMBER() PARTITION will take care. – PSK Feb 17 '20 at 06:03
  • Yeah, I presumed as much; am in the middle of adding the extra union as we speak to check it works correctly. – DarkMoon Feb 17 '20 at 06:05