how do i get the prior day price in sql server

Question

HI team how do i get the prior date price from the given table can you give me the syntax for this example input

output

Answer 1

That's really to less on information and at all, I don't understand, what you are looking for. Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 2

With the example and the text you have given, this would work:

; WITH CTE AS (
    SELECT OrderNo, Date, Price, rank (PARTITION BY OrderNo ORDER BY Date DESC) AS rank
    FROM tbl
)
SELECT OrderNo, Date, Price
FROM  CTE
WHERE rank = 2

But I don't think this is the answer.

You have been give advice in another thread on how to ask questions, but you have chosen to ignore that advice.

If you can't explain properly what you are asking for, you will not get any good answers.

Answer 3

Hi @Naresh y

how do i get the prior date price from the given table

Not quite understood what you mean. How about this query using Lag() function?

DECLARE @table TABLE (OrderNO VARCHAR(10),DATE DATE, PRICE INT)
INSERT INTO @table VALUES
('A','2014-01-30',10),('A','2014-01-29',13)

;WITH CTE AS
(
SELECT *
      ,LAG(DATE)OVER(PARTITION BY OrderNO ORDER BY DATE) AS Prev_DATE
      ,LAG(PRICE)OVER(PARTITION BY OrderNO ORDER BY DATE) AS Prev_PRICE
FROM @table
)
SELECT OrderNO,Prev_DATE,Prev_PRICE
FROM CTE
WHERE DATE = '2014-01-30'

Best regards, Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.