SQLTeam.com | Weblogs | Forums

Get Related Value


#1

Dear All
please find attached this problem


#2

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


#3

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


#4

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


#5

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

SELECT ...
FROM table1 t1
CROSS APPLY (SELECT TOP 1 
               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'

#6

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


#7

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?


#8

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