To UNION or Not to UNION

I am currently running into an issue with my UNION clause. I am trying to figure out how many orders I am import via each day. The problem is, I have two tables that these orders are stored in OLDORDERHEADER and ORDERHEADER. After an order is completed, it moves from the ORDERHEADER table into the OLD. Both have the same exact columns, so I figured I would use a UNION clause and be fine. Well once I run my query, it prints two rows, identical dates and different qtys’. Which is to be expected, I just want to combine them into one row and a sum of the qty’s for each hour. Here is what I am working with:

SELECT CONVERT(varchar(8),recvtime,1) AS 'Day', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 0 THEN 1 ELSE 0 END) AS '12AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 1 THEN 1 ELSE 0 END) AS '1AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 2 THEN 1 ELSE 0 END) AS '2AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 3 THEN 1 ELSE 0 END) AS '3AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 4 THEN 1 ELSE 0 END) AS '4AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 5 THEN 1 ELSE 0 END) AS '5AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 6 THEN 1 ELSE 0 END) AS '6AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 7 THEN 1 ELSE 0 END) AS '7AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 8 THEN 1 ELSE 0 END) AS '8AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 9 THEN 1 ELSE 0 END) AS '9AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 10 THEN 1 ELSE 0 END) AS '10AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 11 THEN 1 ELSE 0 END) AS '11AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 12 THEN 1 ELSE 0 END) AS '12PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 13 THEN 1 ELSE 0 END) AS '1PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 14 THEN 1 ELSE 0 END) AS '2PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 15 THEN 1 ELSE 0 END) AS '3PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 16 THEN 1 ELSE 0 END) AS '4PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 17 THEN 1 ELSE 0 END) AS '5PM',
SUM(CASE WHEN DATEPART(hour,recvtime) = 18 THEN 1 ELSE 0 END) AS '6PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 19 THEN 1 ELSE 0 END) AS '7PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 20 THEN 1 ELSE 0 END) AS '8PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 21 THEN 1 ELSE 0 END) AS '9PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 22 THEN 1 ELSE 0 END) AS '10PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 23 THEN 1 ELSE 0 END) AS '11PM'

FROM MCK_HVS.OLDORDERHEADER WITH(NOLOCK)

WHERE CAST(recvtime as DATE) = CAST(GETDATE() as DATE)

GROUP BY CONVERT(varchar(8),recvtime,1)

UNION

SELECT CONVERT(varchar(8),recvtime,1) AS 'Day', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 0 THEN 1 ELSE 0 END) AS '12AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 1 THEN 1 ELSE 0 END) AS '1AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 2 THEN 1 ELSE 0 END) AS '2AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 3 THEN 1 ELSE 0 END) AS '3AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 4 THEN 1 ELSE 0 END) AS '4AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 5 THEN 1 ELSE 0 END) AS '5AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 6 THEN 1 ELSE 0 END) AS '6AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 7 THEN 1 ELSE 0 END) AS '7AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 8 THEN 1 ELSE 0 END) AS '8AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 9 THEN 1 ELSE 0 END) AS '9AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 10 THEN 1 ELSE 0 END) AS '10AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 11 THEN 1 ELSE 0 END) AS '11AM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 12 THEN 1 ELSE 0 END) AS '12PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 13 THEN 1 ELSE 0 END) AS '1PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 14 THEN 1 ELSE 0 END) AS '2PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 15 THEN 1 ELSE 0 END) AS '3PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 16 THEN 1 ELSE 0 END) AS '4PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 17 THEN 1 ELSE 0 END) AS '5PM',
SUM(CASE WHEN DATEPART(hour,recvtime) = 18 THEN 1 ELSE 0 END) AS '6PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 19 THEN 1 ELSE 0 END) AS '7PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 20 THEN 1 ELSE 0 END) AS '8PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 21 THEN 1 ELSE 0 END) AS '9PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 22 THEN 1 ELSE 0 END) AS '10PM', 
SUM(CASE WHEN DATEPART(hour,recvtime) = 23 THEN 1 ELSE 0 END) AS '11PM'

FROM MCK_HVS.ORDERHEADER WITH(NOLOCK)

WHERE CAST(recvtime as DATE) = CAST(GETDATE() as DATE)

GROUP BY CONVERT(varchar(8),recvtime,1)

ORDER BY CONVERT(varchar(8),recvtime,1)desc

Answer

You might also consider PIVOT for a more concise syntax.

I find the use of UNION dubious and am assuming that you actually need UNION ALL too.

WITH CombinedOrderHeader
     AS (SELECT recvtime
         FROM   ORDERHEADER
         UNION ALL
         SELECT recvtime
         FROM   oldORDERHEADER),
     PvtSource(htt, Day)
     AS (SELECT FORMAT(recvtime, 'htt'),
                CONVERT(VARCHAR(8), recvtime, 1)
         FROM   CombinedOrderHeader
         WHERE  recvtime >= CAST(GETDATE() AS DATE)
                AND recvtime < DATEADD(DAY, 1, CAST(GETDATE() AS DATE)))
SELECT *
FROM   PvtSource PIVOT (COUNT(htt) FOR htt IN ([12AM], [1AM], [2AM], [3AM], [4AM], [5AM], 
                                               [6AM], [7AM], [8AM], [9AM], [10AM], [11AM], 
                                               [12PM], [1PM], [2PM], [3PM], [4PM], [5PM], 
                                               [6PM], [7PM], [8PM], [9PM], [10PM], [11PM] 
                                             )) P