USE msdb;
GO
 
WITH BackupData AS (
    SELECT 
        database_name,
        CAST(backup_start_date AS DATE) AS backup_date,
        DAY(backup_start_date) AS backup_day,
        CASE type
            WHEN 'D' THEN 'Full Backup'
            WHEN 'I' THEN 'Differential Backup'
            WHEN 'L' THEN 'Transaction Log Backup'
        END AS backup_type,
        DATEDIFF(MINUTE, backup_start_date, backup_finish_date) AS duration_minutes,
        cast((compressed_backup_size / 1024 / 1024/1024) as numeric(18,2)) AS size_gb
    FROM dbo.backupset
    WHERE 
        backup_start_date >= '2025-06-01'
        AND backup_start_date < '2025-07-01'
)
SELECT 
    database_name,
    backup_type,
    COUNT(CASE WHEN backup_day = 1 THEN 1 END) AS [Day 1],
    SUM(CASE WHEN backup_day = 1 THEN duration_minutes ELSE 0 END) AS [Duration 1],
    SUM(CASE WHEN backup_day = 1 THEN size_gb ELSE 0 END) AS [Size GB 1],
    COUNT(CASE WHEN backup_day = 2 THEN 1 END) AS [Day 2],
    SUM(CASE WHEN backup_day = 2 THEN duration_minutes ELSE 0 END) AS [Duration 2],
    SUM(CASE WHEN backup_day = 2 THEN size_gb ELSE 0 END) AS [Size GB 2],
    COUNT(CASE WHEN backup_day = 3 THEN 1 END) AS [Day 3],
    SUM(CASE WHEN backup_day = 3 THEN duration_minutes ELSE 0 END) AS [Duration 3],
    SUM(CASE WHEN backup_day = 3 THEN size_gb ELSE 0 END) AS [Size GB 3],
    COUNT(CASE WHEN backup_day = 4 THEN 1 END) AS [Day 4],
    SUM(CASE WHEN backup_day = 4 THEN duration_minutes ELSE 0 END) AS [Duration 4],
    SUM(CASE WHEN backup_day = 4 THEN size_gb ELSE 0 END) AS [Size GB 4],
    COUNT(CASE WHEN backup_day = 5 THEN 1 END) AS [Day 5],
    SUM(CASE WHEN backup_day = 5 THEN duration_minutes ELSE 0 END) AS [Duration 5],
    SUM(CASE WHEN backup_day = 5 THEN size_gb ELSE 0 END) AS [Size GB 5],
    COUNT(CASE WHEN backup_day = 6 THEN 1 END) AS [Day 6],
    SUM(CASE WHEN backup_day = 6 THEN duration_minutes ELSE 0 END) AS [Duration 6],
    SUM(CASE WHEN backup_day = 6 THEN size_gb ELSE 0 END) AS [Size GB 6],
    COUNT(CASE WHEN backup_day = 7 THEN 1 END) AS [Day 7],
    SUM(CASE WHEN backup_day = 7 THEN duration_minutes ELSE 0 END) AS [Duration 7],
    SUM(CASE WHEN backup_day = 7 THEN size_gb ELSE 0 END) AS [Size GB 7],
    COUNT(CASE WHEN backup_day = 8 THEN 1 END) AS [Day 8],
    SUM(CASE WHEN backup_day = 8 THEN duration_minutes ELSE 0 END) AS [Duration 8],
    SUM(CASE WHEN backup_day = 8 THEN size_gb ELSE 0 END) AS [Size GB 8],
    COUNT(CASE WHEN backup_day = 9 THEN 1 END) AS [Day 9],
    SUM(CASE WHEN backup_day = 9 THEN duration_minutes ELSE 0 END) AS [Duration 9],
    SUM(CASE WHEN backup_day = 9 THEN size_gb ELSE 0 END) AS [Size GB 9],
    COUNT(CASE WHEN backup_day = 10 THEN 1 END) AS [Day 10],
    SUM(CASE WHEN backup_day = 10 THEN duration_minutes ELSE 0 END) AS [Duration 10],
    SUM(CASE WHEN backup_day = 10 THEN size_gb ELSE 0 END) AS [Size GB 10],
    COUNT(CASE WHEN backup_day = 11 THEN 1 END) AS [Day 11],
    SUM(CASE WHEN backup_day = 11 THEN duration_minutes ELSE 0 END) AS [Duration 11],
    SUM(CASE WHEN backup_day = 11 THEN size_gb ELSE 0 END) AS [Size GB 11],
    COUNT(CASE WHEN backup_day = 12 THEN 1 END) AS [Day 12],
    SUM(CASE WHEN backup_day = 12 THEN duration_minutes ELSE 0 END) AS [Duration 12],
    SUM(CASE WHEN backup_day = 12 THEN size_gb ELSE 0 END) AS [Size GB 12],
    COUNT(CASE WHEN backup_day = 13 THEN 1 END) AS [Day 13],
    SUM(CASE WHEN backup_day = 13 THEN duration_minutes ELSE 0 END) AS [Duration 13],
    SUM(CASE WHEN backup_day = 13 THEN size_gb ELSE 0 END) AS [Size GB 13],
    COUNT(CASE WHEN backup_day = 14 THEN 1 END) AS [Day 14],
    SUM(CASE WHEN backup_day = 14 THEN duration_minutes ELSE 0 END) AS [Duration 14],
    SUM(CASE WHEN backup_day = 14 THEN size_gb ELSE 0 END) AS [Size GB 14],
    COUNT(CASE WHEN backup_day = 15 THEN 1 END) AS [Day 15],
    SUM(CASE WHEN backup_day = 15 THEN duration_minutes ELSE 0 END) AS [Duration 15],
    SUM(CASE WHEN backup_day = 15 THEN size_gb ELSE 0 END) AS [Size GB 15],
    COUNT(CASE WHEN backup_day = 16 THEN 1 END) AS [Day 16],
    SUM(CASE WHEN backup_day = 16 THEN duration_minutes ELSE 0 END) AS [Duration 16],
    SUM(CASE WHEN backup_day = 16 THEN size_gb ELSE 0 END) AS [Size GB 16],
    COUNT(CASE WHEN backup_day = 17 THEN 1 END) AS [Day 17],
    SUM(CASE WHEN backup_day = 17 THEN duration_minutes ELSE 0 END) AS [Duration 17],
    SUM(CASE WHEN backup_day = 17 THEN size_gb ELSE 0 END) AS [Size GB 17],
    COUNT(CASE WHEN backup_day = 18 THEN 1 END) AS [Day 18],
    SUM(CASE WHEN backup_day = 18 THEN duration_minutes ELSE 0 END) AS [Duration 18],
    SUM(CASE WHEN backup_day = 18 THEN size_gb ELSE 0 END) AS [Size GB 18],
    COUNT(CASE WHEN backup_day = 19 THEN 1 END) AS [Day 19],
    SUM(CASE WHEN backup_day = 19 THEN duration_minutes ELSE 0 END) AS [Duration 19],
    SUM(CASE WHEN backup_day = 19 THEN size_gb ELSE 0 END) AS [Size GB 19],
    COUNT(CASE WHEN backup_day = 20 THEN 1 END) AS [Day 20],
    SUM(CASE WHEN backup_day = 20 THEN duration_minutes ELSE 0 END) AS [Duration 20],
    SUM(CASE WHEN backup_day = 20 THEN size_gb ELSE 0 END) AS [Size GB 20],
    COUNT(CASE WHEN backup_day = 21 THEN 1 END) AS [Day 21],
    SUM(CASE WHEN backup_day = 21 THEN duration_minutes ELSE 0 END) AS [Duration 21],
    SUM(CASE WHEN backup_day = 21 THEN size_gb ELSE 0 END) AS [Size GB 21],
    COUNT(CASE WHEN backup_day = 22 THEN 1 END) AS [Day 22],
    SUM(CASE WHEN backup_day = 22 THEN duration_minutes ELSE 0 END) AS [Duration 22],
    SUM(CASE WHEN backup_day = 22 THEN size_gb ELSE 0 END) AS [Size GB 22],
    COUNT(CASE WHEN backup_day = 23 THEN 1 END) AS [Day 23],
    SUM(CASE WHEN backup_day = 23 THEN duration_minutes ELSE 0 END) AS [Duration 23],
    SUM(CASE WHEN backup_day = 23 THEN size_gb ELSE 0 END) AS [Size GB 23],
    COUNT(CASE WHEN backup_day = 24 THEN 1 END) AS [Day 24],
    SUM(CASE WHEN backup_day = 24 THEN duration_minutes ELSE 0 END) AS [Duration 24],
    SUM(CASE WHEN backup_day = 24 THEN size_gb ELSE 0 END) AS [Size GB 24],
    COUNT(CASE WHEN backup_day = 25 THEN 1 END) AS [Day 25],
    SUM(CASE WHEN backup_day = 25 THEN duration_minutes ELSE 0 END) AS [Duration 25],
    SUM(CASE WHEN backup_day = 25 THEN size_gb ELSE 0 END) AS [Size GB 25],
    COUNT(CASE WHEN backup_day = 26 THEN 1 END) AS [Day 26],
    SUM(CASE WHEN backup_day = 26 THEN duration_minutes ELSE 0 END) AS [Duration 26],
    SUM(CASE WHEN backup_day = 26 THEN size_gb ELSE 0 END) AS [Size GB 26],
    COUNT(CASE WHEN backup_day = 27 THEN 1 END) AS [Day 27],
    SUM(CASE WHEN backup_day = 27 THEN duration_minutes ELSE 0 END) AS [Duration 27],
    SUM(CASE WHEN backup_day = 27 THEN size_gb ELSE 0 END) AS [Size GB 27],
    COUNT(CASE WHEN backup_day = 28 THEN 1 END) AS [Day 28],
    SUM(CASE WHEN backup_day = 28 THEN duration_minutes ELSE 0 END) AS [Duration 28],
    SUM(CASE WHEN backup_day = 28 THEN size_gb ELSE 0 END) AS [Size GB 28],
    COUNT(CASE WHEN backup_day = 29 THEN 1 END) AS [Day 29],
    SUM(CASE WHEN backup_day = 29 THEN duration_minutes ELSE 0 END) AS [Duration 29],
    SUM(CASE WHEN backup_day = 29 THEN size_gb ELSE 0 END) AS [Size GB 29],
    COUNT(CASE WHEN backup_day = 30 THEN 1 END) AS [Day 30],
    SUM(CASE WHEN backup_day = 30 THEN duration_minutes ELSE 0 END) AS [Duration 30],
    SUM(CASE WHEN backup_day = 30 THEN size_gb ELSE 0 END) AS [Size GB 30],
    COUNT(CASE WHEN backup_day = 31 THEN 1 END) AS [Day 31],
    SUM(CASE WHEN backup_day = 31 THEN duration_minutes ELSE 0 END) AS [Duration 31],
    SUM(CASE WHEN backup_day = 31 THEN size_gb ELSE 0 END) AS [Size GB 31]
FROM BackupData
GROUP BY database_name, backup_type
ORDER BY database_name, backup_type;