I have a table where I record all transactions with transaction date and other details . I need an sql statement to retrieve all the last transactions for every year, specially the last date of transaction for every year. I'm using MSSQL 2014 Ex, If my records starts from 01/01/2000, I need to get all the last date of transaction from 01/01/2000 to present.
Example expected result:
TransactionDate
--------------------
12/31/2000
12/29/2001
12/30/2002
12/31/2003
.
.
.
12/30/2021
CodePudding user response:
Try breaking your TransactionDate into a year using DATEPART. Once you have that, you can select max TransactionDate and group by year, to get the last transaction of each year
That would look like this…
SELECT LastTransactionDateOfYear
FROM (
SELECT DATEPART(year,TransactionDate),
MAX(TransactionDate) LastTransactionDateOfYear
FROM <yourtable>
GROUP BY DATEPART(year,TransactionDate)
)