I have a question about SQL Server: how to get previous day's values?

CREATE TABLE [dbo].[productdetails]
(
    [productname] [varchar](50) NULL,
    [productdate] [date] NULL,
    [price] [int] NULL
) 
 
INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-22' AS Date), 10)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-23' AS Date), 20)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-25' AS Date), 30)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-26' AS Date), 40)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-20' AS Date), 30)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-22' AS Date), 40)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-23' AS Date), 50)

INSERT INTO [dbo].[productdetails] ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-26' AS Date), 60)

Based on above data I want output like this:

Here when we compare this product name lux and productdate = 2022-02-25 with prevous date 2022-02-24, that time we do not have any record for this product.

So we need to show value null in previous day price filed.

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

This query not return the excepted result. Can you please tell me how to write query to achieve this task in SQL Server?

CodePudding user response：

You have can achieve expected result with below query.

Select p2.productname, p2.productdate, p2.price, p1.price AS previousdayprice from dbo.productdetails p1 
right outer join dbo.productdetails p2
on p1.productname = p2.productname and p1.productdate = DateAdd(day,-1,p2.productdate)

CodePudding user response：

In the below, using outer apply we order all the dates descendant.
Then, we filtered by last product dates for each product must be bigger than previous ones. Therefore,we got the result set which is required.

Table variable is used so that it is added.

DECLARE  @productdetails TABLE
(
    [productname] [varchar](50) NULL,
    [productdate] [date] NULL,
    [price] [int] NULL
) 



INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-22' AS Date), 10)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-23' AS Date), 20)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-25' AS Date), 30)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'lux', CAST(N'2022-02-26' AS Date), 40)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-20' AS Date), 30)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-22' AS Date), 40)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-23' AS Date), 50)

INSERT INTO @productdetails ([productname], [productdate], [price]) 
VALUES (N'pen', CAST(N'2022-01-26' AS Date), 60)

Result query;

SELECT  p1.[productname]
        ,p1.productdate
        ,p1.price
        ,p2.previousprice 
FROM 
    @productdetails as p1
    OUTER APPLY(SELECT TOP 1 previousprice=p2.price
            FROM 
                @productdetails as p2 
            WHERE 
                p1.productname=p2.productname 
                AND p1.productdate>p2.productdate 
            ORDER BY p2.productdate DESC 
            ) as p2
ORDER BY productname,productdate

Result set;

  <table border = 1>   <tr>  <th> [productname] </th> <th>[productdate]</th> <th>[price]</th> <th>[previousprice]</th></tr><tr><td>lux</td><td>2022-02-22</td><td>10</td></tr><tr><td>lux</td><td>2022-02-23</td><td>20</td><td>10</td></tr><tr><td>lux</td><td>2022-02-25</td><td>30</td><td>20</td></tr><tr><td>lux</td><td>2022-02-26</td><td>40</td><td>30</td></tr><tr><td>pen</td><td>2022-01-20</td><td>30</td></tr><tr><td>pen</td><td>2022-01-22</td><td>40</td><td>30</td></tr><tr><td>pen</td><td>2022-01-23</td><td>50</td><td>40</td></tr><tr><td>pen</td><td>2022-01-26</td><td>60</td><td>50</td></tr></table>