-2

I have a table with, let's say, RecordID (PK), Name, Date and FavoriteMovie. Each day I capture everyone's name and their favorite movie, and append rows if they're different than the LATEST record for that given person. What I need is a T-SQL query to tell me:

  • What is each person's favorite movie as of 5/15/2022?

Data

Expected results:

enter image description here

mateoc15
  • 509
  • 5
  • 18
  • 1
    Always best if you provide sample data and desired results – John Cappelletti Jul 07 '22 at 19:05
  • Added sample data, sorry - broke the rules – mateoc15 Jul 07 '22 at 19:26
  • 1
    The question got closed (and I can't reopen) , but your problem maps very well to the temporal tables feature https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16 . This would let you do a query literally using AS OF and you can pick ANY date, not just the most recent one. – Conor Cunningham MSFT Jul 07 '22 at 21:05
  • I should have mentioned that I'm using Azure Synapse, so temporal tables are not an option, but this is a GREAT idea for other platforms. Thank you @ConorCunninghamMSFT – mateoc15 Jul 08 '22 at 13:14

2 Answers2

3

I think a partitioned row number is pretty efficient, try something like this

DECLARE @EndDate DATE = '2022-01-01';
with cteFakeData as (--BEGIN fake sample data
    SELECT * FROM (VALUES ('Alice','2021-10-15','Aliens') 
        , ('Alice','2021-12-21','Speed')
        , ('Alice','2022-02-27','Coco')
        , ('Bob','2021-02-20','Matrix')
        , ('Bob','2021-08-02','Jaws')
        , ('Bob','2022-02-02','Dune')
    ) as mov(PerName, VoteDate, MovName)
)--END fake sample data, solution begins below
, cteTagged as (
    SELECT *, ROW_NUMBER () OVER (PARTITION BY PerName ORDER BY VoteDate DESC) as Newness
    FROM cteFakeData
    WHERE VoteDate <= @EndDate 
)
SELECT * 
FROM cteTagged 
WHERE Newness = 1
Robert Sheahan
  • 2,100
  • 1
  • 10
  • 12
1

I was previously unfamiliar with the rows and ranges clause. The default wasn't giving me what I needed, had to explicitly declare rows clause.

select last_value(favoriteMovie) over (partition by name order by dt rows between UNBOUNDED preceding and UNBOUNDED following) as l,
    first_value(favoriteMovie) over (partition by name order by dt rows between UNBOUNDED preceding and UNBOUNDED following) as f
mateoc15
  • 509
  • 5
  • 18