I have a column which contain dates of transactions occurred on working days. I am trying to group my customers to see their end of month totals for the last number of months.

Essentially, how do I get the max date for each of the last 6 months in SQL?

Example table below

what I would like to return is the table below

CodePudding user response：

Aggregate and group by the YEAR and MONTH of your date column in a subquery to get the MAX date per month (and year), then do not return the year and month in the outer query.

CodePudding user response：

I have assumed the table name as Test and first column as Code and Second column name as TransactionDate. I have implemented my query in SQL SERVER.

SELECT Code, FORMAT (Max(TransactionDate), 'dd/MM/yyyy ') 
FROM Test
GROUP By Code, MONTH(TransactionDate), YEAR(TransactionDate)
ORDER By Code ASC, Max(TransactionDate) DESC

You can check my query in SQL Fiddle. Link