SQL Get max date in dataset for each month

Question

I have a table with INT id and DATETIME date, amongst other fields. Rows are inserted into this table each weekday (not guaranteed), and several other tables use this id as a foreign key.

My question is, how can I get the id for the max date of each month, which I can then use to join to other data tables? For example, if the process ran today, I would want to see data for Jan 31, Feb 28, ... , Oct 31, Nov 23.

I am using SQL Server 2005.

Answer 1

This will pull the month and year and last id:

SELECT month(date), year(date), max(id)
FROM mytable
GROUP BY month(date), year(date)

And here's a test script

create table #mytable (
date datetime,
id int
)

insert into #mytable (date, id) values ('11/7/2009', 1)
insert into #mytable (date, id) values ('11/8/2009', 2)
insert into #mytable (date, id) values ('12/21/2009', 3)
insert into #mytable (date, id) values ('12/30/2009', 4)
insert into #mytable (date, id) values ('10/7/2009', 5)
insert into #mytable (date, id) values ('10/12/2009', 6)

SELECT month(date), year(date), max(id)
FROM #mytable
GROUP BY month(date), year(date)

drop table #mytable

Answer 2

I'd select back the maximum date using a group by query, like this:

Select Year(datetimefield) as MyYear, month(datetimefield) as MyMonth max(day(datetimefield)) as MaxDate
from table1
group by Year(datetimefield), month(datetimefield)

The query above will give you back the maximum transaction date for each month. To get the maximum Id associated with that date for each month, join the results of this back to the source table to get the max id for that day.

So, your full query would look like this:

select  year(datetimefield) as MyYear, Month(datetimefield) as MyMonth, day(datetimefield), max(IdFieldName) as MaxID
from someTable inner join 
    (select  year(datetimefield) as MyYear, Month(datetimefield) as MyMonth,     max(day(datetimefield)) as MaxDate
    from someTable 
    group by year(datetimefield), Month(datetimefield)) as innerSelect
on innerselect.MyYear = year(datetimefield) and 
    innerselect.MyMonth = Month(datetimefield) and
    innerselect.MaxDate = day(datetimefield)
group by year(datetimefield), Month(datetimefield), day(datetimefield)

Answer 3

I'm assuming you only want the last day of the month that you have a record for (eg. Jan 30 if you've got nothing for Jan 31).

SELECT
  id,
  date
FROM (
  SELECT
    id,
    date,
    ROW_NUMBER() OVER (PARTITION BY YEAR(date), MONTH(date) ORDER BY DAY(date) DESC) AS rowNum
  FROM sometable
) z
WHERE rowNum = 1;

Answer 4

CREATE TABLE #foo (id INT, d DATETIME);

INSERT #foo(id,d) SELECT 1, '20091101'
UNION ALL SELECT 2, '20091102'
UNION ALL SELECT 3, '20091006'
UNION ALL SELECT 4, '20091001'
UNION ALL SELECT 5, '20091002'
UNION ALL SELECT 6, '20090904';

SELECT d, id
FROM 
( 
  SELECT d, id, rn = ROW_NUMBER() OVER 
  (PARTITION BY DATEDIFF(MONTH, '20000101', d)
   ORDER BY d DESC)
  FROM #foo
) AS x
WHERE x.rn = 1
ORDER BY x.d;

DROP TABLE #foo;