Como posso combinar valores de múltiplos dias em um mês em SQL?

votos
0

Eu tenho muitos dispositivos em vários estados (essencialmente operacional ou para baixo, mas existem outros estados). Um instantâneo é tomada no final de cada dia para gravar o que afirmam um dispositivo está em. Eu estou rolando a contagem de dispositivos a uma operação geral ou para baixo (por exemplo, 726 CCTVs são operacional na 01-01- 18 e 4 CFTVs são para baixo em 01-01-18, 728 CFTVs são de funcionamento em 01-02-18 e 2 CFTVs são para baixo em 01-02-18, etc).

Este é o meu código atual:

SELECT oper.failurecode AS 'Device Type', 
       COUNT(hist.status) AS 'Count', 
       hist.status AS 'Status', 
       CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)) AS 'Date'
FROM locations loc
        INNER JOIN locoper oper ON oper.location = loc.location
        INNER JOIN serclochistory hist ON hist.location = loc.location
WHERE hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber') 
  AND CONVERT(TIME, hist.datadatetime) > '23:00:00'
GROUP BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status
ORDER BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status;

Isso me dá um resultado como abaixo:

CCTV   6    DOWN       2018-01-01
CCTV   763  OPERATING  2018-01-01
CCTV   18   DOWN       2018-01-02
CCTV   748  OPERATING  2018-01-02

O que eu quero fazer é combinar todos os dispositivos para o mês que eu possa ver um resultado como abaixo:

CCTV   24   DOWN       2018-01
CCTV   1511 OPERATING  2018-01

Posso encontrar exemplos de como combinar itens para uma única linha, mas não adicionar valores desta maneira.

Publicado 27/11/2018 em 18:10
fonte usuário
Em outras línguas...                            


2 respostas

votos
0

Eu estaria tentado a ir com uma expressão de tabela comum (CTE) para a captura de dados inicial. Desta forma, você só precisa converter os valores de data uma vez. É também um pouco mais fácil de ler o seu agrupamento e ordenação.

Eu sou claro quanto à necessidade de tomar 30 minutos fora do valor de data, mas acho que a intenção é capturar registros de estado de 30 minutos de cada lado da meia-noite. Sua consulta conforme publicado não fazer isso porque o filtro de tempo não é em um valor ajustado.

De qualquer forma - dar a este um tiro. Espero que ajude.

;WITH statusFilteredRecords ([Device Type], [Status], [Date])
-- Set of records filtered by status, with the time adjustment of -30 minutes applied to the date.
AS
(
    SELECT      oper.failurecode AS 'Device Type', 
                hist.status AS 'Status', 
                DateAdd(MINUTE, -30, hist.dateadatetime) [Date] -- This normalises "End of Day" as being midnight +/- 30 minutes
    FROM        locations loc
    INNER JOIN  locoper oper 
            ON  oper.location = loc.location
    INNER JOIN  serclochistory hist 
            ON  hist.location = loc.location
    WHERE       hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber')       
)
, eodFilteredRecords ([Device Type], [Status], [Date])
-- Set of records filtered by Time, so that only EOD records are included, with the date normalized to the first of the month for grouping
AS  
(
    SELECT  [Device Type],
            [Status],
            CAST(DateAdd(dd, -(day([Date])-1), [Date]) as Date) AS [Date] -- Force the date to the first of the month.
    FROM    statusFilteredRecords
    WHERE   CONVERT(TIME, [Date]) > '23:00:00' -- Because we're operating on the normalized datetime value, this is really > 23:30 or < 00:30
)
SELECT      [Device Type], 
            count([Status]) as [Count],
            [Status], 
            [Date]
FROM        eodFilteredRecords
GROUP BY    [Device Type], 
            [Status], 
            [Date]
ORDER BY    [Date], 
            [Device Type], 
            [Status]

<== EDIT: Comentário para responder o pedido do OP para uma explicação ==>

Count()é uma função agregada - opera sobre os dados dentro de um subgrupo dos resultados. Neste caso, os subgrupos são aqueles que correspondem a [Device Type], [Status]e [Date]- este é especificado na GROUP BYcláusula da consulta.

GROUP BY    [Device Type], 
            [Status], 
            [Date]

Na consulta original, a GROUP BYcláusula convertido os DateTimevalores dentro do campo hist.dateadatetimede Datevalores (sem componente de tempo). Removendo o tempo parte do valor significa que todos os valores para um determinado dia são agora iguais, e todos formar parte da mesma sub-grupo. ( 01-Jan-2018 15:22:33Torna-se 01-Jan-2018, assim como 01-Jan-2018 22:51:00, etc).

GROUP BY 
    CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)), 
    oper.failurecode, 
    hist.status

O que tenho feito é tomar um passo adiante para remover eficazmente o tempo de parte eo dia parte do valor, definindo todas as datas a ser o primeiro dia do mês.

CAST(DateAdd(dd, -(day([Date])-1), [Date]) as Date) AS [Date]

Vamos supor que a data de 28 de setembro de 2018. Eu uso day([Date])para obter o dia do mês (por exemplo, 28) e subtrair 1 a partir deste valor (dá 27) e assumir que o número de dias de folga a data, assim que cai de volta para o 1º dia do mês.

Então agora quando o avaliamos a GROUP BY [Device Type], [Status], [Date], nós agrupar todos os registros que ocorrem dentro do mesmo mês ( 11-Sep-2018torna-se 01-Sep-2018, como faz 30-Sep-2018, etc).

Aplicando a função agregada Count()a esses grupos menos granulares de registros (agrupados meu mês em vez de dia) dá o resultado enrolado que você está procurando.

Respondeu 27/11/2018 em 21:34
fonte usuário

votos
0

Sua consulta sugerem SQL Serversintaxe se assim for, então você pode fazer:

SELECT oper.failurecode AS 'Device Type', COUNT(hist.status) AS 'Count', hist.status AS 'Status', CONVERT(DATE, DATEADD(MINUTE, -30, hist.datadatetime)) AS 'Date'
FROM locations loc INNER JOIN 
     locoper oper 
     ON oper.location = loc.location INNER JOIN 
     serclochistory hist 
     ON hist.location = loc.location
WHERE hist.status NOT IN ('DECOMMISSIONED', 'TRANSITION', 'NOT READY', 'NOT READY - Fiber') AND CONVERT(TIME, hist.datadatetime) > '23:00:00'
GROUP BY CONVERT(VARCHAR(7), DATEADD(MINUTE, -30, hist.datadatetime), 120), oper.failurecode, hist.status
ORDER BY Date, oper.failurecode, hist.status;
Respondeu 27/11/2018 em 18:19
fonte usuário

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more