SQLTeam.com | Weblogs | Forums

Get Related Value

Dear All
please find attached this problem

looks like a join on name and transaction date, but I'm not sure. How do you define related in this context?

Select * From Table1 where TransactionDate > '2017-11-06'
but I need get Previous value directly for each name

check out the LAG windowing function in the docs. It should do the trick

Not sure LAG/LEAD will work here - it seems he is looking for the latest value that is less than or equal to 2017-11-06.

I would just use CROSS APPLY with a SELECT TOP 1 to get the related value...

FROM table1 t1
               FROM table1 t2 
              WHERE t2.ID = t1.ID 
                AND t2.Name = t1.Name 
               AND t2.TransactionDate <= '2017-11-06'
             ORDER BY t2.TransactionDate DESC) As r
WHERE t1.TransactionDate > '2017-11-06'

It should, he wants the previous date per name, LAG is made for that

I didn't read it that way - I read it that he wants the value from the row prior to a specific date. LAG would give you the prior row and that would result in 62 for row ID 5 - not 50 as it appears he wants.

If there are no gaps in the previous rows - that is no missing days then you could calculate the offset for LAG based on a DATEDIFF...but why go through that when a simple cross-apply will work just as well?

well i guess its a little ambiguous, need the OP to chime in again