CODEWITHSUNDEEP
sqlsql-servert-sql
Krivitskiy Grigoriy
Krivitskiy Grigoriy

Reputation: 406

Get latest entry in each week over several years period

I have the following table to store history for entities:

    Date                   Id        State
    -------------------------------------
    2017-10-10              1        0
    2017-10-12              1        4
    2018-5-30               1        8
    2019-4-1                2        0
    2018-3-6                2        4
    2018-3-7                2        0

I want to get last entry for each Id in one week period e.g.

    Date                   Id        State
    -------------------------------------
    2017-10-12              1        4
    2018-5-30               1        8
    2019-4-1                2        0
    2018-3-7                2        0

I'd try to use Partition by: 

select
        ID
       ,Date
        ,State
        ,DatePart(week,Date) as weekNumber
        from TableA
where   Date = (
            select max(Date) over (Partition by Id Order by DatePart(week, Date) Desc)
        )
order by ID

but it still gives me more than one result per week.

Upvotes: 0

Views: 42

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use ROW_NUMBER():

SELECT a.*
FROM (SELECT a.*, ROW_NUMBER() OVER (PARTITION BY a.id, DATEPART(WK, a.Date) ORDER BY a.Date DESC) AS Seq
      FROM tablea a
     ) a
WHERE seq = 1
ORDER BY id, Date;

Upvotes: 1

Related Questions