Previous months latest date

Question

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

ID	Date
610	11/30/2021
610	11/29/2021
610	11/28/2021
610	10/29/2021
610	10/15/2021
610	9/15/2021
610	9/14/2021
610	8/10/2021
725	11/30/2021
725	11/29/2021
725	11/28/2021
725	10/29/2021
725	10/15/2021
725	9/15/2021
725	9/14/2021
725	8/10/2021

what I would like to return is the table below

ID	Date
610	11/30/2021
610	10/15/2021
610	9/15/2021
610	8/10/2021
725	11/30/2021
725	10/29/2021
725	9/15/2021
725	8/10/2021

Have you checked https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column ? — Jmartnz, Jan 03 '22 at 16:22
In the expected result you have made a mistake..According to your expectation the date will be 10/29/2021. But you mentioned 10/15/2021 — Md. Tarikul Islam Soikot, Jan 03 '22 at 18:01

score 0 · Answer 1 · answered Jan 03 '22 at 16:25

0

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.

answered Jan 03 '22 at 16:25

divisionby0

170
5

Md. Tarikul Islam Soikot · Answer 2 · 2022-01-03T18:18:08.523

0

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

edited Jan 03 '22 at 18:18

answered Jan 03 '22 at 18:06

Md. Tarikul Islam Soikot

455
1
4
14

Previous months latest date

2 Answers2