Sourav

I'm not quite sure what you're doing with your completion_Status column. You're returning 'Completed Full BKP' if the backup (of any type) is more than an hour old, and 'Failed Diff BKP' if it's less than an hour old. Is this really what you intend? You don't handle the case where the DATEDIFF function returns 1 - is that intentional? Finally, you should consider rewriting your code so that the date arithmetic only neeeds to be carried out once (instead of once per row) and so that any indexes on the columns can be used. The example below doesn't do exactly the same thing as yours (it's more precise since DATEADD deals in precise dates rather than number of boundaries crossed) so you'd want to test that it works for you.

...

when dateadd(hh,-1,getdate()) > backup_finish_date 1 then cast('Completed Full BKP' as varchar(30))

...

John