?
Solved

UNION operator for TSQL code

Posted on 2012-03-13
5
Medium Priority
?
321 Views
Last Modified: 2012-03-13
Hi All,

Please guide me how to combine below two scripts using UNION operator.

Script 1:
=========
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 7
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'D'
       AND K.Backup_Start_Date >= getdate()-7
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC

Script 2:
===========
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 2
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'I'
       AND K.Backup_Start_Date >= getdate()-2
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
0
Comment
Question by:sg05121983
5 Comments
 
LVL 13

Assisted Solution

by:Philip Pinnell
Philip Pinnell earned 375 total points
ID: 37713813
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 7
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END,
              K.[Type]
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'D'
       AND K.Backup_Start_Date >= getdate()-7
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
union all
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 2
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END,
              K.[Type]
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'I'
       AND K.Backup_Start_Date >= getdate()-2
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
0
 
LVL 8

Expert Comment

by:rushShah
ID: 37713949
you dont need union operator for this, try this,


SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                               WHEN max(k.backup_start_date) < GETDATE() - 7
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     WHEN max(k.backup_start_date) < GETDATE() - 2
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And ((K.[Type] = 'I'
       AND K.Backup_Start_Date >= getdate()-2) OR (K.[Type] = 'D'
       AND K.Backup_Start_Date >= getdate()-7))
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 37713966
My post should have been

SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 7
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END,
              K.[Type]
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'D'
       AND K.Backup_Start_Date >= getdate()-7
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
union
SELECT
(SELECT RTRIM(CONVERT(char(20), SERVERPROPERTY('machinename'))) + '_' + CONVERT(char(20), SERVERPROPERTY('instancename'))) as Instance_Name,
              M.Name As Database_Name,
              K.Backup_Start_Date,
              K.Backup_Finish_Date,
              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential'
              END,
              Note = CASE
                     WHEN max(k.backup_start_date) < GETDATE() - 2
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/      
                     WHEN ISNULL(max(k.backup_start_date),0) = 0
                     THEN 'ALERT'
                     ELSE '-----'
              END,
              K.[Type]
FROM Sys.SysDatabases M (NOLOCK)
       INNER JOIN Sys.SysAltFiles SF (NOLOCK)
       ON M.[DBId] = SF.[DBId]
       LEFT OUTER JOIN MSDB..BackupSET K (NOLOCK)
       ON M.Name = K.Database_Name And K.[Type] = 'I'
       AND K.Backup_Start_Date >= getdate()-2
WHERE M.[Dbid] <> 2
GROUP BY M.Name, M.crdate, K.Backup_Start_Date, K.Backup_Finish_Date, K.[Type], K.User_Name
ORDER BY K.[Type] DESC
0
 
LVL 25

Accepted Solution

by:
jogos earned 375 total points
ID: 37714282
Im all for the non-union sollution  (when you start changing then you don't have to change things twice) so you only must identify cleart the things that are different.

So in addition to that sollution

See that there are 2 problems in the case-when
- in that sollution the check for type was 'forgotten' for 7 or 2 days
- already in original ' WHEN ISNULL(max(k.backup_start_date),0) = 0 ' when it is not null then you are comaparing a date to 0 (an int)


              Backup_Type = CASE Upper(K.[Type])
                     WHEN 'D' THEN 'Full Backup'
                     WHEN 'I' THEN 'Differential' 
              END,
              Note = CASE
                               WHEN max(k.backup_start_date) <  GETDATE() - 7 AND 
                                                            Upper(K.[Type]) = 'D'
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 7 days*/ 
                     WHEN max(k.backup_start_date) < GETDATE() - 2 AND 
                                                            Upper(K.[Type]) = 'I'
                     THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 2 days*/
                     --WHEN max(k.backup_start_date) < DATEADD(HH, - 168, GETDATE()) THEN 'ALERT' /*raise an ALERT, if a backup hadn't occurred in last 168 hours*/       
                     WHEN max(k.backup_start_date)  is null 
                     THEN 'ALERT'
                     ELSE '-----'
              END

Open in new window

Didn't check the whole sql for the rest
0
 

Author Closing Comment

by:sg05121983
ID: 37718416
--
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question