How to ungroup transactions or group by day? [closed]

Question

How to ungroup transactions or group by day? [closed]

Navigation

#1 by (1 votes)

2

SELECT 
       COUNT(*) QTD_TRANSACOES,
       CD_RSP_RECUSA,
       SUM(VL_AUTORIZADO) VALOR_SOMATORIO,
       CASE WHEN
                    MR.DS_MOTIVO_RECUSA IS NULL THEN 'CÓDIGO DE RECUSA NÃO CADASTRADO'


       WHEN
             MR.DS_MOTIVO_RECUSA = 'APROVADO' THEN 'TRANSAÇÃO DECLINADA'
       ELSE
             MR.DS_MOTIVO_RECUSA 
       END
              MOTIVO_RECUSA
FROM   PRD_BI.DBO.TB_AUTORIZACAO T WITH (NOLOCK) 
LEFT   JOIN PRD_RELATORIOS.TRANSACAO.TB_MOTIVO_RECUSA MR WITH (NOLOCK)
ON     T.CD_RSP_RECUSA = MR.CD_MOTIVO_RECUSA
WHERE   TP_AUTORIZACAO = 'D'
AND     CD_AUT = ''
AND     DT_AUTORIZACAO BETWEEN '20170501' AND '20170531'
GROUP BY CD_RSP_RECUSA,MR.DS_MOTIVO_RECUSA

How to check if loads were missing in May 2017?
And how to ungroup the transactions or group by day (May)?

sql sql-server

asked by anonymous 03.07.2017 / 15:22

1 answer

Use an activity / fragment on a bottom sheet Copy of Oracle Material Structure

score 1 · Answer 1

You can use WITH to generate the available days of the month and link them to your original query :

WITH dias
AS (
  SELECT CAST('2017-05-01' AS DATE) AS dia,
         1 AS nivel
   UNION ALL
  SELECT CAST(DATEADD(DAY, 1, dia) AS DATE),
         nivel + 1
    FROM dias
   WHERE dia < '2017-05-31'
)
SELECT COUNT(*) AS QTD_TRANSACOES,
       CONVERT(VARCHAR, D.DIA, 103) AS DIA,
       CD_RSP_RECUSA,
       SUM(VL_AUTORIZADO) AS VALOR_SOMATORIO,
       CASE
         WHEN MR.DS_MOTIVO_RECUSA IS NULL THEN 'CÓDIGO DE RECUSA NÃO CADASTRADO'
         WHEN MR.DS_MOTIVO_RECUSA = 'APROVADO' THEN 'TRANSAÇÃO DECLINADA'
         ELSE MR.DS_MOTIVO_RECUSA 
       END AS MOTIVO_RECUSA
  FROM PRD_BI.DBO.TB_AUTORIZACAO T WITH (NOLOCK) 
       INNER JOIN DIAS D ON D.DIA = T.DT_AUTORIZACAO
       LEFT JOIN PRD_RELATORIOS.TRANSACAO.TB_MOTIVO_RECUSA MR WITH (NOLOCK) ON T.CD_RSP_RECUSA = MR.CD_MOTIVO_RECUSA
 WHERE TP_AUTORIZACAO = 'D'
   AND CD_AUT = ''
   AND DT_AUTORIZACAO BETWEEN '2017-05-01' AND '2017-05-31
 GROUP BY CD_RSP_RECUSA, MR.DS_MOTIVO_RECUSA, D.DIA