I've tried to change it so that it picks up the backups over the weekend but I've failed miserably!
Could you advise what I need to do to modify this so that the report shows every day in the drop down, rather than just Monday-Friday. Thanks very much in advance
You'll need to change the insert dt_dim logic. This is where the query takes into account your backup windows. I'm using Monday through Thursday midnight through the 6 AM the next day 30 hours and on Friday a 78 hours. These lines need to be changed:
WHEN DATEPART(dw,SeqDate) BETWEEN 2 AND 5 THEN DATEADD(hh,30,SeqDate)
WHEN DATEPART(dw,SeqDate) = 6 THEN DATEADD(hh,78,SeqDate)
2 through 5 is Monday through Thursday
6 is Saturday
Does this make sense? If you want, post your backup window and schedule and I'll tweak the query and post it here.