question

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 );

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

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).