Return 3 months previous from day 1

Asked

Viewed 1,538 times

1

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

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 don’t have to keep changing forever.

sql sql-server

  • Your data set is MySQL?

  • Good afternoon, it’s SQL Server.

  • Change 2018 for YEAR(GETDATE())

  • GETDATE() returns the current date

  • I already thought about it, but when it is Jan/19, I will have to bring the data of Oct/18, Nov/18, Dec/18 and Jan/19

  • @fercs89 (1) Since the column DT_ORIGEM is in table joined by LEFT JOIN, there will not always be value to compare; what to do in such cases? (2) How the column DT_ORIGEM is declared?

  • @Josédiz did not understand the question

Show 2 more comments

2 answers

2


Use the function GetDate() to return the current date and from the date returned remove 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.

  • I put this query and it returned the data since Mar/18, IE, a much longer period than I need.

  • Had a -3 over there, I corrected

  • now it worked out, thank you very much! = D

  • 1

    @Robertodecampos When possible rewrite the code, because the WHERE clause can return wrong result. For example, if the current month is February/2018, it returns only lines from December/2017. Also, the code is not sargable.

  • @Josédiz would be more or less like this?

  • @Robertodecampos Sorry for the delay in responding. The modification you made is already better.

Show 1 more comment

0

Another way I got: 

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 DATEDIFF(MONTH, DT_ORIGEM, GETDATE()) < 4

Browser other questions tagged sql sql-server

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