Solved

UNION operator for TSQL code

Posted on 2012-03-13
5
304 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 125 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 125 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now