Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting NULL output against the column : completion_status


Getting NULL output against the column : completion_status

Author
Message
SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2338 Visits: 3191
--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.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
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
drew.allen
drew.allen
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2687 Visits: 9960
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
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
Elliott Berkihiser
Elliott Berkihiser
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 2014
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
SQL-DBA-01
SQL-DBA-01
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2338 Visits: 3191
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.
Elliott Berkihiser
Elliott Berkihiser
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 2014
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
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7502 Visits: 15159
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search