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