How do I merge days of the week using the UNION operator?

Question

I am using this WITH method so that I can combine two SELECT statements.

The problem is that When I join the results using the UNION, it is duplicating the days and combining the time column. I need it the other way around.... for example: I am trying to select results on all Mondays that are equal to 14:00 and then another set of results that equal 16:00 and have those two display in separate columns next to each other.

An example of what this is currently giving me is below this SQL query.

USE Trading;
GO

WITH 
  ctetwo (CTEday, CTEtime) AS
  (
    SELECT DATENAME(WEEKDAY, M.[DateTime]) as Day,
        COUNT(M.[High] - M.[Low]) as Two  
    FROM dbo.MicroES M
        WHERE CONVERT(time, M.[DateTime]) = '14:00'
        AND DATEPART(WEEKDAY,M.[Datetime]) BETWEEN 2 AND 6
        GROUP BY DATENAME(WEEKDAY, M.[DateTime])
  ),
    cteSix (CTEday, CTEtime) AS
  (
    SELECT DATENAME(WEEKDAY, M.[DateTime]) as Day,
        COUNT(M.[High] - M.[Low]) as Two  
    FROM dbo.MicroES M
        WHERE CONVERT(time, M.[DateTime]) = '18:00'
        AND DATEPART(WEEKDAY,M.[Datetime]) BETWEEN 2 AND 6
        GROUP BY DATENAME(WEEKDAY, M.[DateTime])
  )

    select 
  --'Monday' AS 'Day',
    CTEday AS 'Day',
    CTEtime as TimeFour
   from ctetwo

   UNION ALL 

     select 
  --'Tuesday' AS 'Day',
    CTEday AS 'Day',
    CTEtime as TimeSix
   from cteSix

   ORDER BY Day

Day TimeFour

Friday 15

Friday 17

Monday 13

Monday 19

Thursday 15

Thursday 18

Tuesday 16

Wednesday 18

Aside: ( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1 will always return an integer from 1 to 7 with 1 corresponding to Sunday regardless of the setting of DateFirst or Language. — HABO

Unknown Unknown · Accepted Answer · 2019-12-26T00:21:38

Can't you do that using a single select?

SELECT 
    DATENAME(WEEKDAY, M.[DateTime]) as [Day],
    COUNT(M.[High] - M.[Low]) as Two  
FROM dbo.MicroES M
WHERE CONVERT(time, M.[DateTime]) in ('14:00','18:00')
    AND DATEPART(WEEKDAY,M.[Datetime]) BETWEEN 2 AND 6
GROUP BY DATENAME(WEEKDAY, M.[DateTime])
ORDER BY [Day],Two

How do I merge days of the week using the UNION operator?

1 Answers