Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Getting NULL output against the column : completion_status Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 4:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 802, Visits: 1,358
--Getting NULL output against the column : completion_status

select a.name, case b.type
when 'D' then 'Full Database Backup'
when 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
END AS Backup_Type,
max(b.backup_finish_date) LastSuccessfulBackup,
cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) as 'IntervalInDays',
case
when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 1 then cast('X' as varchar(10))
when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 7 then cast('Y' as varchar(10))
End as completion_Status

from master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b
ON a.name = b.database_name
where a.name not in ('tempdb')
group by a.name, b.type
order by a.name, b.type




I need to get the records in the completion_status column as follows:

1. If the IntervalInDays is > 1, then I should be able to print "full db completed against the column(completion_Status),
2. If it is for more than 7 days, it should be available as "diff bkp completed"






Thanks.
Post #1349565
Posted Friday, August 24, 2012 4:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,233, Visits: 9,463
You need to handle all eventualities once and once only in your case expression - something like this:

...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval >= 1 AND Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...

John
Post #1349577
Posted Friday, August 24, 2012 7:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
John Mitchell-245523 (8/24/2012)
You need to handle all eventualities once and once only in your case expression - something like this:

...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval >= 1 AND Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...

John


Actually, you're wrong on both counts. You do not need to handle eventualities at all. The CASE expression will just produce a NULL if you do not handle the eventuality. I use this quite a bit, especially when the CASE expression is embedded in a COUNT(). It doesn't make sense to me to code "ELSE NULL" when it's going to do that anyhow.

Second, you do not need to make sure that each eventuality meets only one condition. The CASE expression short circuits the evaluation once a condition is met. That means that you can simplify your conditions, because you don't need to exclude previous conditions, because that condition won't even be evaluated if previous conditions are met. Your code can be simplified as follows:
...
CASE WHEN Interval < 1 THEN 'X'
WHEN Interval < 7 THEN 'Y'
WHEN Interval >= 7 THEN 'Z'
ELSE 'Warning - Interval falls outside expected ranges'
END
...

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Post #1349656
Posted Friday, August 24, 2012 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,233, Visits: 9,463
Point taken, Drew - I should have phrased it better and said "your code would be clearer if" rather than "you need to". You're right that there's no syntactical requirement to do it that way, and that overlapping conditions are allowed. However, the overlapping conditions in the original post meant that the second condition would never have been evaluated be evaluated as True.

Your suggestion of allowing a NULL to be returned isn't an option here since the very reason for this thread is that NULLs are not wanted. Coding an ELSE NULL is indeed pointless, which is why I didn't suggest it.

Perfectly happy with the way you rewrote my code, by the way!

John
Post #1349694
Posted Friday, August 24, 2012 2:03 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:05 PM
Points: 256, Visits: 2,004
A small point, but wouldn't the code by simplified by using the DateDiff function to compare the last backup date to today's date? There's no need to convert from datetime to numeric.

Cheers,
Elliott
Post #1349915
Posted Saturday, August 25, 2012 1:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:08 AM
Points: 802, Visits: 1,358
Hi Guys,

I have wholly modified the script as below. Please do execute this and suggest if this is looking fine now.

select a.name, case b.type
when 'D' then 'Full Database Backup'
when 'I' THEN 'Differential Backup'
WHEN 'L' THEN 'Log Backup'
END AS Backup_Type,
max(b.backup_finish_date) LastSuccessfulBackup,
cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) as 'IntervalInDays',
case
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 1 then cast('Completed' as varchar(10))
--when cast((getdate() - max(b.backup_finish_date)) as numeric(5, 2)) > 7 then cast('Failed' as varchar(10))
when datediff(hh,max(b.backup_finish_date),getdate()) > 1 then cast('Completed Full BKP' as varchar(30))
when datediff(hh,max(b.backup_finish_date),getdate()) < 1 then cast('Failed Diff BKP' as varchar(30))
End as completion_Status,
case
when (max(b.backup_finish_date) is NULL )then 'Backup Failed-no Data Found'
end as backup_status_data_not_found
from master..sysdatabases a
LEFT OUTER JOIN msdb..backupset b
ON a.name = b.database_name
where a.name not in ('tempdb') --and b.type = 'D'
group by a.name, b.type
order by a.name, b.type


Thanks.
Post #1350021
Posted Saturday, August 25, 2012 10:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:05 PM
Points: 256, Visits: 2,004
Hi Sourav,

Glad that lyou are making progress towards your goal.
I don't have SQL Server open in front of me, so these comments are just observations, maybe correct, maybe not.

It looks like you are measuring success if the latest backup time is within one hour of the current time. Is this what you want, and does it hold for all types of backups (full, diff, log)?

Also, the query looks for backup types where the max(backup_finish_date) is null and that condition is called "No data". That would mean that no backup had ever been completed for that database (and type). Since that condition is fairly unusual (in my experience), you may want to add additional code to your solution that defines a failed backup event as conditional based on the type of backup and the length of time between the current time and the last backup of that type (e.g. >24 hours means failed full backup; >1 hour means failed diff backup, etc)

I think you are going in this direction, but perhaps you didn't post your full code.

HTH,
Elliott
Post #1350050
Posted Tuesday, August 28, 2012 2:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 5,233, Visits: 9,463
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
Post #1350734
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse