Return 3 previous months from day 1

Question

Return 3 previous months from day 1

Navigation

#1 by (1 votes)

1

Every Friday I need to generate a report that brings me transactions made in the last 3 months. However, I have to pick up from the 1st of each month, for example, we are in month 9, so I have to pick up all the transactions since 01/06. I always make the following query:

SELECT 
    Z.ID_CONTA
    ,W.ID_TRANSACAO
    ,Y.DS_TIPO_TRANSACAO
    ,W.DT_ORIGEM
    ,W.VL_TRANSACAO
FROM T_CONTA Z
LEFT JOIN W_TRANSACAO W ON Z.ID_CONTA = W.ID_CONTA
LEFT JOIN T_TIPOTRANSACAO Y ON W.CD_TIPO_TRANSACAO = Y.CD_TIPO_TRANSACAO 
WHERE MONTH(DT_ORIGEM) >= MONTH(GETDATE()) - 3 AND YEAR(DT_ORIGEM) = 2018

It always worked, but now I'm thinking when to turn the year so I do not have to keep changing.

sql sql-server

asked by anonymous 28.09.2018 / 17:37

1 answer

How to compare arrays? Problem with grid bootstrap

score 1 · Accepted Answer

Use the GetDate() function to return the current date and from the date returned take 3 months with the function DATEADD :

SELECT 
    Z.ID_CONTA,
    W.ID_TRANSACAO,
    Y.DS_TIPO_TRANSACAO,
    W.DT_ORIGEM,
    W.VL_TRANSACAO
FROM T_CONTA Z
LEFT JOIN W_TRANSACAO W ON Z.ID_CONTA = W.ID_CONTA
LEFT JOIN T_TIPOTRANSACAO Y ON W.CD_TIPO_TRANSACAO = Y.CD_TIPO_TRANSACAO 
WHERE DT_ORIGEM >= CONCAT(
    YEAR(DATEADD(MONTH, -3, GETDATE())),
    '-',
    MONTH(DATEADD(MONTH, -3, GETDATE())), '-01'
)

See more about the function DATEADD here .