How to make SQL query to retrieve all last transactions for every year from a transaction table

Question

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

Answer 1

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)
)