IF OBJECT_ID('tempdb..#bkps') IS NOT NULL
BEGIN
    DROP TABLE #bkps;
END;

SELECT DISTINCT
	CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER,
    bs.[database_name],
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.backup_size,
    bs.[name] AS backupset_name,
    bs.[description],
    CASE
            WHEN bs.TYPE = 'D' THEN 'Full'
            WHEN bs.TYPE = 'I' THEN 'Differential'
            WHEN bs.TYPE = 'L' THEN 'Log'
            WHEN bs.TYPE = 'F' THEN 'File or filegroup'
            WHEN bs.TYPE = 'G' THEN 'Differential file'
            WHEN bs.TYPE = 'P' THEN 'Partial'
            WHEN bs.TYPE = 'Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
	,ROW_NUMBER()OVER(PARTITION BY bs.[database_name], bs.TYPE ORDER BY bs.backup_start_date) AS rid
  INTO      #bkps
  FROM      msdb.[dbo].backupset bs
 WHERE      bs.backup_start_date >= '20221210'
 AND bs.[backup_start_date] <= '20221212'
   AND bs.[name] LIKE '%commvault%'
   AND bs.[database_name] NOT IN ('model','msdb','master','tempdb','HCITools')
   AND bs.[type] = 'L'
;
IF @@ROWCOUNT = 0 
BEGIN
    SELECT 'no log backup' as msg
END