UNION operator for TSQL code

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
sg05121983Asked:
Who is Participating?
 
jogosConnect With a Mentor Commented:
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
 
Atdhe NuhiuConnect With a Mentor Commented:
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
 
rushShahCommented:
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
 
Atdhe NuhiuCommented:
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
 
sg05121983Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.