How to allocate transactions or group by day?

Asked

Viewed 152 times

3

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 cargo was missing in May 2017?
  • And how to allocate transactions or group by day(May)?
sql database sql-server

  • Inform the structure of the tables and, if possible, the data available in each of them

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

1 answer

1

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

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

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.