September 15, 2015 at 12:46 pm
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
WHEN msdb..backupset.type = 'L' THEN 'Log'
WHEN msdb..backupset.type IS NULL THEN 'Differential'
END AS backup_type,
(msdb.dbo.backupset.compressed_backup_size/1000/1000) AS CompressedBackupSize_MB,
(msdb.dbo.backupset.compressed_backup_size/1000/1000/1000) AS CompressedBackupSize_GB
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
September 15, 2015 at 1:05 pm
lmarkum (9/15/2015)
The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'
WHEN msdb..backupset.type = 'L' THEN 'Log'
WHEN msdb..backupset.type IS NULL THEN 'Differential'
END AS backup_type,
(msdb.dbo.backupset.compressed_backup_size/1000/1000) AS CompressedBackupSize_MB,
(msdb.dbo.backupset.compressed_backup_size/1000/1000/1000) AS CompressedBackupSize_GB
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
That is because your case expression does not have an else. There are several other perfectly valid values for backupset.type (I, F, G, P, Q). https://technet.microsoft.com/en-us/library/ms186299.aspx If you have any of those values your case expression will return NULL.
I would highly suggest you start using aliases in your queries. You would have saved yourself well over 100 keystrokes in this small query and it would be way more legible. You can also simplify your math so it is clearer what you are doing. Of course your math isn't correct for the total amount of MB or GB anyway. There 1,024 bytes in a MB so your math is not quite right. Also you should use DATEADD instead doing addition/subtraction on a datetime datatype. The result is the same but it is explicitly clear and if for some reason the default behavior changes your code will still work. I am also not really sure why you needed to convert a datetime to a datetime to compare it to a datetime. 😉
Here is your query with the full case expression for all values in that column. I also changed this up to use aliases. This is cleaner and more importantly is that 3 part naming in the list of columns is deprecated and will not work at some point in the future.
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
b.database_name,
b.backup_start_date,
b.backup_finish_date,
CASE b.type
WHEN 'D' THEN 'Database'
WHEN 'I' then 'Differential Database'
WHEN 'L' THEN 'Log'
WHEN 'F' THEN 'File of filegroup'
when 'G' then 'Differential file'
when 'P' then 'Partial'
when 'Q' then 'Differential partial'
END AS backup_type,
b.compressed_backup_size / 1000000 AS CompressedBackupSize_MB,
b.compressed_backup_size / 1000000000 AS CompressedBackupSize_GB
FROM msdb.dbo.backupmediafamily mf
INNER JOIN msdb.dbo.backupset b ON mf.media_set_id = b.media_set_id
WHERE b.backup_start_date >= dateadd(day, -7, GETDATE())
ORDER BY
b.database_name,
b.backup_finish_date
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 2:10 pm
Not only that, three and four part naming conventions in a SELECT list has been deprecated. Using table alias lets you use two part naming conventions in a SELECT list.
I would also recommend this even in single table queries as you never know when you may add table joins to a query.
September 15, 2015 at 2:21 pm
Lynn Pettis (9/15/2015)
Not only that, three and four part naming conventions in a SELECT list has been deprecated. Using table alias lets you use two part naming conventions in a SELECT list.I would also recommend this even in single table queries as you never know when you may add table joins to a query.
I mentioned 3 part naming but it never hurts to drive it home.
I agree that using an alias even on a single table query is a great idea. One thing I really despise is a query with a bunch of columns and none of them have aliases for the column. It is just awful trying to determine which table each column belongs to. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 2:26 pm
Sean Lange (9/15/2015)
Lynn Pettis (9/15/2015)
Not only that, three and four part naming conventions in a SELECT list has been deprecated. Using table alias lets you use two part naming conventions in a SELECT list.I would also recommend this even in single table queries as you never know when you may add table joins to a query.
I mentioned 3 part naming but it never hurts to drive it home.
I agree that using an alias even on a single table query is a great idea. One thing I really despise is a query with a bunch of columns and none of them have aliases for the column. It is just awful trying to determine which table each column belongs to. :hehe:
Missed the end of that sentence. 😀
September 15, 2015 at 5:37 pm
Lynn Pettis (9/15/2015)
I would also recommend this even in single table queries as you never know when you may add table joins to a query.
Just putting a different point-of-view (its obviously horses-for-course and personal preference).
We have column names that are unique within the database. Each column has a prefix which represents the table. Client table might have columns cli_ID, cli_Name, etc.
Thus the only time we need a table alias is if we self-join / multi-join a table.
its probably much the same as aliasing the table, except that we always have "cli_" as the prefix on a client column, and an alias might be "cli" most of the time, but at others "c" or even "foobar"!
If we have a category table, with columns cat_ID, cat_Name, then the category column on the Client would be cli_cat_ID
If I join the two then my code would be
FROM Client
JOIN Category
on cat_ID = cli_cat_ID
if I accidentally pick the wrong column, or a column from a different table, then I think that will be easier to spot when viewing the code than if I just have table alias and all tables have an "ID" column.
FROM Client
JOIN Category
on cat_ID = cli_foobar_ID
would instantly look wrong to me as the mnemonics are wrongly associated.
Apologies for straying off topic
September 15, 2015 at 6:40 pm
I appreciate the answers for the CASE statement and will test them when I can later tonight. I borrowed this code from the internet and just never cleaned it up to use proper aliases, which I do know about. I did not catch the issues with the math, 1000 versus 1024, as my purpose with the code was not the math but returning the dates for backups. Nonetheless, I will make the other suggested corrections.
September 15, 2015 at 7:40 pm
Kristen-173977 (9/15/2015)
We have column names that are unique within the database. Each column has a prefix which represents the table. Client table might have columns cli_ID, cli_Name, etc.
This is by far my biggest pet peeve. I absolutely can't stand it when column names change across tables. It isn't a cli_Name it is a ClientName and it is always a ClientName. Prefixes or suffixes on columns drive me nuts. Personal preference for sure but it takes a lot more time for anybody new to figure out the naming column names when they jump around like that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 12:49 am
Sean Lange (9/15/2015)
I absolutely can't stand it when column names change across tables. It isn't a cli_Name it is a ClientName and it is always a ClientName.
Sorry, just in case we are on different wavelengths, the cat_Name is the name of the Category, not the Client - I picked a duff example using Name columns twice in my example. cli_FirstName, cli_LastName would better describe what I meant for the Client table 🙂
Or are you saying that cli_Name should be ClientName? What about Age, Gender, etc. Sorry, but although your reply is quite clear I haven't got the grasp of the point you are raising.
September 16, 2015 at 7:15 am
Kristen-173977 (9/16/2015)
Sean Lange (9/15/2015)
I absolutely can't stand it when column names change across tables. It isn't a cli_Name it is a ClientName and it is always a ClientName.Sorry, just in case we are on different wavelengths, the cat_Name is the name of the Category, not the Client - I picked a duff example using Name columns twice in my example. cli_FirstName, cli_LastName would better describe what I meant for the Client table 🙂
Or are you saying that cli_Name should be ClientName? What about Age, Gender, etc. Sorry, but although your reply is quite clear I haven't got the grasp of the point you are raising.
Hehe no worries. You said "Each column has a prefix which represents the table". I personally can't stand this because it is detracting from the name of a column. FirstName should always be FirstName. It isn't a cli_FirstName. When you have these kinds of prefixes you have columns whose names change between tables. I just find that very distasteful. I know there are people who disagree with me and that is totally fine. As you said, and I agree, this is a personal preference thing.
Did I do a better job of conveying what I meant this time?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 8:03 am
Sean Lange (9/16/2015)
Kristen-173977 (9/16/2015)
Did I do a better job of conveying what I meant this time?
Yup, got it! Totally missed it the first time around though, sorry.
So ID should be [ID] in all tables that have one.
My view is that I might join the ID for the wrong table / alias with that naming style. Sure, I can still do that with "cli_ID", but the prefix is part of what one reads when seeing such a join condition, or the logic in a where clause.
It probably harkens back to when I was programming in C, and early days of Windows programming, when Hungarian Notation was all the rage. The "Defensive Programming" thinking was that if you wrote "intFoo = floatBar" then inherently there was something wrong - if you did mean to do that then you should explicitly cast it, so that it was clear that was your intent. I take the same view with
WHERE cli_cat_ID = cat_ID
and think that it helps me spot bugs, or just write-it-right first time.
I'm always interested in hearing other people's views though, so I'm not evangelising but rather welcoming debate; I've adopted lots of coding-style-methods (and tricks) over the years, not many are my own work :). If a Newbie reads the discussion and chooses their method based on the Pros / Cons debated, then I regard that as a success - much better than the Newbie just doing X because they never realised there were alternatives.
When you have these kinds of prefixes you have columns whose names change between tables
Hmmm ... not seen it in that light before.
A customer ID is nothing like a supplier ID to me. Sure, they are (let's say) both INTs, IDENTITY, both the PKey for the table, and so on. Identical in all those respects. But for me they are different and, most importantly, at all costs I must avoid accidentally joining the wrong one. Its purely about Defensive Programming.
We do have things that are the same, in the way that Customer and Supplier IDs are. We very consciously use the same naming convention for them, so I do obviously see them as being from the same stable! xxx_Code we use for an alpha numeric natural key (under our control) and xxx_Ref for one assigned by some external system (and thus prone to having leading zeros, a ridiculously large set of permitted characters, and enough length to individually label all the atoms in the universe!) and other such names that we use very consistently. Just that I, personally, add a prefix, sorry about that!
September 16, 2015 at 8:20 am
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
Kristen-173977 (9/16/2015)
Did I do a better job of conveying what I meant this time?
Yup, got it! Totally missed it the first time around though, sorry.
So ID should be [ID] in all tables that have one.
No the name ID shouldn't ever be used. That is entirely too ambiguous. Much like you shouldn't have a column named Active or Date because they just don't relay what they mean. You can make a good guess but things like IsActive or DateCreated are much more clear.
ClientID is a great name. It can be named ClientID in every table and you shouldn't end up with a name collision.
I'm always interested in hearing other people's views though, so I'm not evangelising but rather welcoming debate; I've adopted lots of coding-style-methods (and tricks) over the years, not many are my own work :). If a Newbie reads the discussion and chooses their method based on the Pros / Cons debated, then I regard that as a success - much better than the Newbie just doing X because they never realised there were alternatives.
Me too and I love a good discussion/debate. I used to like having prefixes on stuff, probably because I started my IT career in the late 90s when hungarian notation was popular. VB6 standards pretty much demanded it....ugh!!! I have found over the years that imbedding system information into names is generally a bad thing, especially datatypes. Thinking back to VB6 I remember seeing ridiculous things like iOrderTotal when the actual datatype was a double. That isn't a real example but certainly illustrates the point.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 8:57 am
Sean Lange (9/16/2015)
ClientID is a great name. It can be named ClientID in every table and you shouldn't end up with a name collision.
I'm never sure where that approach starts-and-ends. ClientID I get - so in the Order Header table I can expect to find a ClientID column, that will join to Customer/Client table.
What about other columns in Customer/Client table that have "common" names, which will also be found in other tables e.g. FirstName (lets assume that exists in Customer, Staff, maybe Supplier)
Or is it just PKey Columns?
Sean Lange (9/16/2015)
I love a good discussion/debate.
Discussion for another thread, but I'm in the process of changing coding-style from having commas at right-end of line to left-end. Definitely got a love-hate relationship with that, and still debating if it is a good choice or not ...
September 16, 2015 at 9:07 am
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
ClientID is a great name. It can be named ClientID in every table and you shouldn't end up with a name collision.I'm never sure where that approach starts-and-ends. ClientID I get - so in the Order Header table I can expect to find a ClientID column, that will join to Customer/Client table.
What about other columns in Customer/Client table that have "common" names, which will also be found in other tables e.g. FirstName (lets assume that exists in Customer, Staff, maybe Supplier)
Or is it just PKey Columns?
I would say that things like FirstName are perfectly fine. This is where aliases become really useful. c.FirstName is obviously the Customer table but s.FirstName indicates the Staff table.
Sean Lange (9/16/2015)
I love a good discussion/debate.Discussion for another thread, but I'm in the process of changing coding-style from having commas at right-end of line to left-end. Definitely got a love-hate relationship with that, and still debating if it is a good choice or not ...
This one has been discussed around here before. I used to be a "right-ender" but am now a "left-sider". I find it easier most of the time to develop on the left side because I can just comment out an entire line and it removes that one from the return. Even easier if you stick some silly constant as a first column while developing so you know you always have a first column. I have never heard any compelling "arguments" that either is actually a better approach, this one really boils down to preference, and maybe shop standards.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 9:31 am
Sean Lange (9/16/2015)
Kristen-173977 (9/16/2015)
Sean Lange (9/16/2015)
ClientID is a great name. It can be named ClientID in every table and you shouldn't end up with a name collision.I'm never sure where that approach starts-and-ends. ClientID I get - so in the Order Header table I can expect to find a ClientID column, that will join to Customer/Client table.
What about other columns in Customer/Client table that have "common" names, which will also be found in other tables e.g. FirstName (lets assume that exists in Customer, Staff, maybe Supplier)
Or is it just PKey Columns?
I would say that things like FirstName are perfectly fine. This is where aliases become really useful. c.FirstName is obviously the Customer table but s.FirstName indicates the Staff table.
Sean Lange (9/16/2015)
I love a good discussion/debate.Discussion for another thread, but I'm in the process of changing coding-style from having commas at right-end of line to left-end. Definitely got a love-hate relationship with that, and still debating if it is a good choice or not ...
This one has been discussed around here before. I used to be a "right-ender" but am now a "left-sider". I find it easier most of the time to develop on the left side because I can just comment out an entire line and it removes that one from the return. Even easier if you stick some silly constant as a first column while developing so you know you always have a first column. I have never heard any compelling "arguments" that either is actually a better approach, this one really boils down to preference, and maybe shop standards.
Just as long as people use the semicolon (;) as a statement terminator not a begininator. That is my pet peeve.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply