Select previous column value from previous row in Select

Dinesh Surve

6y892

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!

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
1. DECLARE @reportDate DATETIME = '2019-04-12';
3. SELECT im.[Date], im.ItemName, im.Quantity AS CurrentQuantity,
4. LAG(im.Quantity,1) OVER (PARTITION im.ItemName ORDER BY im.[Date])
5. AS OldQuantity
6. FROM dbo.ItemMaster im

Laxmidhar Sahoo6y ago

just remove where condition

Dinesh Surve6y ago

Thanks Laxmidhar!

I also tired with this way but it will give correct result only if the previous record is present just before the current row, else it returns NULL.

Please let me know if any alternate solution.

Laxmidhar Sahoo6y ago

I think LAG functiom solve your problem like the bellow code .Look more for lag function

DECLARE @reportDate DATETIME = '2019-04-12';
SELECT im.[Date], im.ItemName, im.Quantity AS CurrentQuantity,
LAG(im.Quantity,1) OVER (PARTITION im.ItemName ORDER BY im.Quantity)
AS OldQuantity
FROM dbo.ItemMaster im
WHERE im.[Date] = @reportDate;