SQL Get Last DateTime for Each Day

Question

I have a table, that includes stats for each Advertisers ads in our system by datetime added. The data is updated hourly, so for each user's ad we have impression data per hour.

I want to be able to run a SQL Query that extracts the last hour for each ad for each day.

The Query I have now, only extracts the last entry period... how do I modify this to extract the last entry for each ad per Advertiser, to calculate a total number of impressions for that one day....

DECLARE @adid INT
SET @adid = 596749

SELECT t1.*
FROM stats t1
WHERE t1.dateadded = (
    SELECT max(dateadded)
    FROM stats t2
    WHERE t1.name = t2.name
) AND advertiserID = @adid

Any help is appreciated!!!!

score 0 · Answer 1 · answered Oct 28 '14 at 15:04

Use ROW_NUMBER():

;WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(8),dateadded,112) 
                                   ORDER BY dateadded DESC)
    FROM dbo.[stats]
    WHERE advertiserID = @adid
)
SELECT *
FROM CTE
WHERE RN = 1;

SQL Get Last DateTime for Each Day

1 Answers1