[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

UNION operator for TSQL code

Posted on 2012-03-13
5
Medium Priority
?
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

649 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