question

adam2354 avatar image
adam2354 asked

Retrieve latest records in each week over time

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.
group-byweeks
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered
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 = sd.Id );
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vivekgrover44 avatar image
vivekgrover44 answered
declare @startdate datetime, @enddate datetime, @count int = 1, @valcount int = 1 select @enddate = getdate() select @startdate = dateadd(m,-3,@enddate) create table #sourcedata(col1 varchar(50), processtime datetime ) while @count <= datediff(d,@startdate,@enddate) begin insert into #sourcedata select 'value'+cast(@valcount as varchar), dateadd(d,@count,@startdate) select @count = @count + 2, @valcount = @valcount + 1 end create table #tmpdata(col1 varchar(50), processtime datetime, weekday int, week int, year int) insert into #tmpdata select col1, processtime, datepart(weekday, processtime) , datepart(week, processtime) , datepart(YEAR, processtime) from #sourcedata select a.col1, a.processtime from #tmpdata a inner join ( select max(weekday) as weekday, week, year from #tmpdata s group by year,week ) b on a.week = b.week and a.weekday = b.weekday drop table #sourcedata drop table #tmpdata
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
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).
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Well, "large" table has just about 100 entries per day. So it is not that large. I am not very concerned with performance at the moment so anything can work here.
0 Likes 0 ·
Have you tried the code in my answer, Adam? Does that work for you?
0 Likes 0 ·
Hahaha, I guess "large" is always relative. As my wife always tells me... Daddum tshhhh :D
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.