previous with current date values compare in sql server

Question

hi I have one doubt in sql server how to get previouday values in sql server

CREATE TABLE [dbo].productdetails

INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'lux', CAST(N'2022-02-22' AS Date), 10)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'lux', CAST(N'2022-02-23' AS Date), 20)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'lux', CAST(N'2022-02-25' AS Date), 30)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'lux', CAST(N'2022-02-26' AS Date), 40)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'pen', CAST(N'2022-01-20' AS Date), 30)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'pen', CAST(N'2022-01-22' AS Date), 40)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'pen', CAST(N'2022-01-23' AS Date), 50)
INSERT [dbo].[productdetails] ([productname], [productdate], [price]) VALUES (N'pen', CAST(N'2022-01-26' AS Date), 60)

based on above data I want output like below
productname |productdate|price|priviousdayprice
lux |2022-02-22 |10 |NULL
lux |2022-02-23 |20 |10
lux |2022-02-25 |30 |null
lux |2022-02-26 |40 |30
pen |2022-01-20 |30 |NULL
pen |2022-01-22 |40 |null
pen |2022-01-23 |50 |40
pen |2022-01-26 |60 |null

here when we compare this prodcut name :lux prodcutdate=2022-02-25 with privous date 2022-02-24 that time we donot have any record for this prodcut .
so we need to show value null in previouday price filed.

select * ,
lag(price)over(partition by productname order by productdate )priviousdayprice
from [test].[dbo].[productdetails]
order by productname,productdate

above query not give excepted result .can you please tell me how to write query to achive this task in sql server

Answer 1

Try:

select d1.*, d2.price as previousdayprice  
from productdetails d1  
left join productdetails d2 on d1.productname = d2.productname and d2.productdate = dateadd(day, -1, d1.productdate)  
order by d1.productname, d1.productdate  

Answer 2

 select *  
 ,Case when productdate=dateadd(day,1,lag(productdate)over(partition by productname order by productdate ))   
 then lag(Price)over(partition by productname order by productdate ) else null end priviousdayprice  
  from productdetails    
 order by productname, productdate  

Answer 3

Hi @harinathu
How about using subquery to get previous day's price, like this:

SELECT A.*,(SELECT B.price FROM [productdetails] B WHERE A.productname=B.productname AND DATEDIFF(DAY,B.productdate,A.productdate)=1) AS priviousdayprice  
FROM [productdetails] A  

Output:

Best regards,
LiHong

---

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.