How can i do a conditional where clause in SQL, for missed back ups?

Question

I have a pretty standard query for a back up aging report. What I want / need to do is have it report on missed back ups. I will need to see the last full database back up older than 2 days. I will also need to see any differential back ups older than the last day and any log back ups older than 30 mins. I'm not sure how to do the conditional where clause.

here's my basic query

SELECT 
   A.[Server],
   A.database_name, 
   A.last_db_backup_date,
   B.backup_type, 
   B.backup_start_date, 
   B.expiration_date,
   B.backup_size, 
   B.logical_device_name, 
   B.physical_device_name,  
   B.backupset_name,
   B.description
FROM
   (
   SELECT  
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name, 
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily 
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

   GROUP BY
       msdb.dbo.backupset.database_name 
   ) AS A

   LEFT JOIN 

   (
   SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full Database'  
       WHEN 'L' THEN 'Log'  
       WHEN 'I' THEN 'Differential'  
       WHEN 'F' THEN 'File Level'  
       WHEN 'G' THEN 'File Level Differential'  
       WHEN 'P' THEN 'Partial'  
       WHEN 'Q' THEN 'Differential partial'        
   END AS backup_type
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name

Answer 1

Use a CASE statement. I'll paste an example from my own work:

SELECT
    cplnt.complainant_type AS complainant_type,
    cpt.date_received AS complaint_received,
    CASE WHEN cpt.date_closed IS NULL
        THEN (CURRENT_DATE - cpt.date_received)
        ELSE (cpt.date_closed - cpt.date_received)
    END AS complaint_age,
    cpt.id AS complaint_id,
    cs.date_case_received AS case_received,
    cs.case_status AS case_stage,
    stg.stage AS complaint_stage,
    tm.name AS team,
    trg.date_completed AS triage_date,
    trg.method AS triage_method,
    cpt.date_closed AS resolution_date,
    cpt.status AS closure_type,
    cpt.resolution_type AS resolution_type,
    cpt.estimated_time_spent AS CLO_time_investment
FROM
    complaints_complaint AS cpt
INNER JOIN
    complaints_complainant AS cplnt
    ON
        cpt.complainant_id = cplnt.id
LEFT OUTER JOIN
    complaints_case AS cs
    ON
        cpt.case_id = cs.id
INNER JOIN
    complaints_stage AS stg
    ON
        cpt.stage_id = stg.id
INNER JOIN
    community_team AS tm
    ON
        cpt.team_id = tm.id
LEFT OUTER JOIN
    schemes_scheme AS sc
    ON
        cs.scheme_id = sc.id
LEFT OUTER JOIN
    (SELECT
        t1.*,
        MIN(date_completed) OVER(PARTITION BY complaint_id) AS seq
        FROM
            complaints_triage AS t1) AS trg
    ON
        trg.complaint_id = cpt.id AND trg.date_completed = trg.seq;