Get last record for each date from a table

Question

I have a table that logs inventory updates throughout the day.

Table InventoryHistory:

UpdateID INT IDENTITY
ProductID INT
UpdateDateTime DATETIME
QuantityOnHand INT

Sample records:

1, 1, '7/29/2009 9:00', 100
2, 1, '7/29/2009 14:00', 99
3, 1, '7/29/2009 20:00', 98
4, 1, '7/30/2009 9:00', 97

For a given ProductID, I need to get a row returned for each day for the last 30 days which has the last update BEFORE 5PM for that given day.

So, the results for productid = 1 should be:

2, 1, '7/29/2009 14:00', 99
4, 1, '7/30/2009 9:00', 97

I've tried building a temp table with the last 30 days in it, and then a subquery against that, but I've been running in circles for hours, and I'm trying to find an elegant solution (without using CURSORS).

Help!

Answer 1

how about trying a CTE with a partitioned row number (SQL 2005 & newer)...

WITH MyCte AS (SELECT  *,
                       RowNum = ROW_NUMBER() OVER (PARTITION BY CONVERT(CHAR(8), UpdateDateTime, 112) ORDER BY UpdateDateTime DESC ) 
               FROM    InventoryHistory
               WHERE   UpdateDateTime >= convert(char(8), dateadd(yy, -30, getdate()), 112)
                       AND DATEPART(hh, UpdateDateTime) < 17)
SELECT   UpdateID, 
         ProductID, 
         UpdateDateTime, 
         QuantityOnHand 
FROM     MyCte
WHERE    RowNum = 1


/* Results 
UpdateID    ProductID   UpdateDateTime          QuantityOnHand
----------- ----------- ----------------------- --------------
2           1           2009-07-29 14:00:00.000 99
4           1           2009-07-30 09:00:00.000 97

(2 row(s) affected)
*/