I have large table with multiple records. I need to retrieve latest record in each week over time period, say last 3 month. Somehow I need to build table of weeks (I am not allowed to modify DB and create temp table) and then find max record which is smaller than given week.
To simply get the max value for each week, you can use a MAX() aggregate function on a record ID or date/time grouping by the week. If you need the whole record for that record, use a CTE to get the value and then select from the source table for the values in the CTE. The following code assumes the date range starts on a Sunday. I cannot test this code now, so please let me know if I mistyped something. DECLARE @StartDate date = '12/14/2014'; DECLARE @EndDate date = '1/13/2015'; WITH Numbers (Number) AS ( SELECT (ROW_NUMBER() OVER (ORDER BY c.object_id)) - 1 FROM sys.columns c ) , DateRange (OneDate) AS ( SELECT DATEADD(day, n.Number, @StartDate) FROM Numbers n WHERE n.Number <= DATEDIFF(day, @StartDate, @EndDate) AND DATEPART(weekday, DATEADD(day, n.Number, @StartDate)) = 1 -- start of a week ) --SELECT * FROM DateRange; , WeeklyMaxRecords (RecordId, WeekStartDate) AS ( SELECT MAX(
sd.Id), dr.OneDate FROM SourceData sd INNER JOIN DateRange dr ON sd.TransactionTime >= dr.OneDate AND sd.TransactionTime < DATEADD(day, 7, dr.OneDate) ) SELECT sd.* FROM SourceData sd WHERE EXISTS ( SELECT 1 FROM WeeklyMaxRecords wmr WHERE wmr.RecordId =
You say: > I have large table with multiple records > I am not allowed to modify DB and create temp table Presumably, this means that you have lots of data to search and that you can't request new indexes to support your operation. I'm not sure that it's possible to answer this question properly (i.e. produce an efficient solution) without knowing about the structure of your table (including index info).