DISTINCT with UNION and COUNT

Navigation
1

I'm running a query in the database to return 6 results from a Agenda table that has multiple available times per day (eg: 08: 10: 00: 000 | 08: 30: 00: 000 ) and for periods (ex: T (representing 'afternoons' or M representing 'mornings').

I'm able to recover now, but I have a problem that I can not resolve. I can only have one time for each period of the day.

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

My query looks 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 not happen as happened on the lines 1 and 2 .

And I'm stuck on it and I have not been able to find a solution yet.

    
asked by anonymous 26.11.2014 / 14:16

1 answer

1

In the SOEN (Link to the answer) solved my problem (I needed some modifications to meet me, but stayed 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 query stayed: 

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
    
26.11.2014 / 16:24
Strong element returning empty - Htmlagilitypack Use selectpicker in jQuery