DISTINCT WITH UNION AND COUNT

Question

I’m performing a query in the database to return 6 results from a table Agenda which has several schedules available per day (ex: 08:10:00:000 | 08:30:00:000) and for periods (ex: T (representing 'tardes' or M representing 'mornings').

I’m recovering now, but I have a problem I can’t seem to solve. I can only have one schedule for each period of the day.

Ex: I can have the day 19/12/2015 a time in the morning and a time in the afternoon, but never I may have the day 19/12/2015 two times in the morning or afternoon.

My query is like this:

SELECT * FROM (
    SELECT TOP(3) agendaidentificador,agendadata, 'M' AS periodo
    FROM AGENDA
    WHERE 
        agendaconsumolocktempo IS NULL 
    AND 
        agendaconsumoidentificador IS NULL 
    AND 
        agendadata > GETDATE() 
    GROUP BY 
        agendaidentificador, 
        agendadata
    HAVING 
        CAST(DATEPART(HOUR,agendadata) AS INT) < 12 
    ORDER BY 
        NEWID(),
        agendadata asc
) A
UNION
SELECT * FROM (
    SELECT TOP(3) agendaidentificador,agendadata, 'T' AS periodo  
    FROM AGENDA
    WHERE 
        agendaconsumolocktempo IS NULL 
    AND 
        agendaconsumoidentificador IS NULL 
    AND 
        agendadata > GETDATE()
    GROUP BY 
        agendaidentificador,
        agendadata
    HAVING 
        CAST(DATEPART(HOUR,agendadata) AS INT) >= 12 
    AND 
        COUNT(CAST(agendadata AS DATE)) = 1 
    ORDER BY 
        NEWID(),
        agendadata asc
) B
GROUP BY 
    agendaidentificador, 
    agendadata,
    periodo
HAVING 
    COUNT(CAST(agendadata as DATE)) = 1
ORDER BY agendadata 

and the result is:

linha |agendaIdentificador |             agendaData  | periodo
------|--------------------|-------------------------|---------
  1   |             173352 | 2015-01-12 12:50:00.000 |  T
  2   |             173363 | 2015-01-12 14:40:00.000 |  T
  3   |             175255 | 2015-01-19 11:30:00.000 |  M
  4   |             175520 | 2015-01-26 14:50:00.000 |  T
  5   |             125074 | 2015-02-25 08:20:00.000 |  M
  6   |             125076 | 2015-02-25 08:40:00.000 |  M

And it can’t happen like it did on the lines 1 and 2.

And I’m stuck on it and I haven’t been able to find a solution yet.

Answer 1

In the SOEN (Link to the reply) solved my problem (I needed some modifications to meet me, but it was bullet.

Using two CTE - Common Table Expression and OVER PARTITION BY

WITH CTE_AGENDA AS
(
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY CAST(agendaData AS DATE), periodo ORDER BY agendaData) RN
    FROM AGENDA
)
,CTE_AGENDA2 AS
(
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY CAST(agendaData AS DATE), CASE WHEN DATEPART(HOUR,AGENDADATA) < 12 THEN 'M' ELSE 'T' END  ORDER BY newid(),agendaData desc) RN2
    FROM AGENDA
)

And the consultation was:

SELECT * FROM (
    SELECT TOP(3) agendaidentificador,agendadata, 'M' AS periodo
    FROM AGENDA
    WHERE 
        agendaconsumolocktempo IS NULL 
    AND 
        agendaconsumoidentificador IS NULL 
    AND 
        agendadata > GETDATE() 
    GROUP BY 
        agendaidentificador, 
        agendadata
    HAVING 
        CAST(DATEPART(HOUR,agendadata) AS INT) < 12 
    ORDER BY 
        NEWID(),
        agendadata asc
) A
UNION
SELECT * FROM (
    SELECT TOP(3) agendaidentificador,agendadata, 'T' AS periodo  
    FROM AGENDA
    WHERE     
        agendaconsumolocktempo IS NULL 
    AND 
        agendaconsumoidentificador IS NULL 
    AND 
        agendadata > GETDATE()
        and RN2 = 1
    GROUP BY 
        agendaidentificador,
        agendadata
    HAVING 
        CAST(DATEPART(HOUR,agendadata) AS INT) >= 12 
    AND 
        COUNT(CAST(agendadata AS DATE)) = 1 
    ORDER BY 
        NEWID(),
        agendadata asc
) B
GROUP BY 
    agendaidentificador, 
    agendadata,
    periodo
HAVING 
    COUNT(CAST(agendadata as DATE)) = 1
ORDER BY agendadata