title: To UNION or Not to UNION
body: <p>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:</p>
  <pre><code>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
  </code></pre>