CODEWITHSUNDEEP
sql-serverdate
mimi taha
mimi taha

Reputation: 1

SQL Server query with date range and max function

I have two tables, TBL_TRANSACTION(NUM_ID,TRANS_DATE,TRANS_TYPE,TOTAL_SUM) and TBL_PERSONS(NUM_ID,NAME)

I want to get the result of this query: select the NAME, NUM_ID and TRANS_DATE columns from two tables with the condition (Names that don't have a transaction between the current date and DATEADD(YYYY,-1,GETDATE()))) and TRANS_TYPE=1

SELECT  dbo.TBL_TRANSACTION.NUM_ID, dbo.TRANSACTION.TRANS_DATE, dbo.TBL_PERSON.NAME, dbo.TBL_PERSON.TOTAL_SUM
FROM    dbo.TBL_TRANSACTION INNER JOIN
        dbo.TBL_PERSON ON dbo.TBL_TRANSACTION.NUM_ID = dbo.TBL_PERSON.NUM_ID
WHERE  (dbo.TBL_TRANSACTION.TRANS_DATE NOT BETWEEN DATEADD(yyyy, - 1, GETDATE()) AND GETDATE()) AND TRANS_TYPE =1

However, I'm receiving no result from the above.

Upvotes: 0

Views: 63

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Some adjustment in date filtering made in this below query. Can you check with this-

SELECT T1.NUM_ID, 
       T1.TRANS_DATE, 
       T2.NAME, 
       T2.TOTAL_SUM
FROM dbo.TBL_TRANSACTION T1
     INNER JOIN dbo.TBL_PERSON T2 ON T1.NUM_ID = T2.NUM_ID
WHERE T1.TRANS_DATE < DATEADD(yyyy, -1, GETDATE())
     AND TRANS_TYPE = 1;

Upvotes: 1

Related Questions