Select previous column value from previous row in Select

Question

Hello,

 

I have a situation where I need to retrive column values from other rows in select statement.

 

Please see the attached screen shot that gives fair idea.

 

Actually I want the Quantity for past date with respect to date passed as parameter.

 

Let say, If I pass date as 12/04/2019, it should give me the result along with Old quantity that is exists on date "11/04/2019". The Old date can vary, In this case it requires to get Quantity one day prior to suppied date, but it can be two day prior or it can be three day prior.(I am having Prior day offset value).

 

 

 

I tried with the query below with no luck.

 

DECLARE @offsetDays INT = -1; -- This can be -1, -2 or -3

DECLARE @reportDate DATETIME = '2019-04-12';

 

SELECT im.[Date], im.ItemName, im.Quantity AS CurrentQuantity,

(CASE WHEN CAST(im.[Date] AS DATE) = CAST(DATEADD(day, @offsetDays, @reportDate) AS Date)

            THEN im.Quantity

ELSE 0

END) AS OldQuantity

FROM dbo.ItemMaster im

WHERE im.[Date] = @reportDate;

 

Could someone help me out? 

 

Thanks in advance!