SQL Get max date in dataset for each month

sql, sql-server, sql-server-2005

Solution

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;

Problem

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.

Original source