CODEWITHSUNDEEP
sqlsql-server
JuanviR
JuanviR

Reputation: 13

How to select MAX date of each month in database

I'm trying to extract max date of each month for a later join to extract in results the records/rows that match (inner join) this max date in Transact-SQL for SQL Server

Main problem is that in the step before I convert dates that exactly match Sunday 1st or Sunday 2nd of the month substracting 1 or 2 days respecitvely. After that I tried a select I used for Max Date of every week of the year (similar behaviour) but it didnt work

I have this Select for max date of each week and no problem

SELECT max([date_field]) as date,
FROM [database].[dbo].[Table]
GROUP BY SUBSTRING(CAST([date] AS NVARCHAR(25)), 8,4) ,Week_Number
ORDER BY week_Number ASC;

But aplying same behaviour with the month can't Select the registers converted from 1st and 2nd of mont

SELECT max([date_field]) as date,
FROM [database].[dbo].[Table]
GROUP BY SUBSTRING(CAST([date] AS NVARCHAR(25)), 8,4) ,month_Number
ORDER BY month_Number ASC;

in this case It should return Max Date in June case -> 31-5-2019 (date that doenst appear in database and we converted it from 2nd june), but retuned 30-5-2019, this one appears but 31-5-2019 is bigger so it shouldnt be returned

Any ideas?

Upvotes: 1

Views: 10653

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Assuming you might have more than one year in the database, you should include the year in the calculation:

SELECT MAX(t.date_field) as date,
FROM [database].[dbo].[Table] t
GROUP BY YEAR(t.date_field), MONTH(t.date_field)
ORDER BY MAX(t.date_field) ASC;

Similarly, your code for week is taking year into account, albeit quite inelegantly.

Upvotes: 0

Programnik
Programnik

Reputation: 1555

SELECT max([date_field]) as date,
FROM [database].[dbo].[Table]
GROUP BY month([date])
ORDER BY month(date) ASC;

Upvotes: 2

SmartestVEGA
SmartestVEGA

Reputation: 8889

SELECT working_area, MAX(commission) 
FROM agents 
GROUP BY working_area;

Upvotes: 0

Related Questions