CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Brrr
Brrr

Reputation: 33

Get previous row value using LAG with specific condition

I want the new column to always show the Price value of the previous record where Type is not null. So far, I'm OK with the current result except the last row that is showing the value of 2 rows prior. The last row should display 0.666 instead of 1.66.

I am using an example I found here and added few more rows to test to see if it can always work properly for my purpose.

My data is larger than the example and might have multiple records where Type is not null. It seems like if the group_nbr happens to return same numbers as other groups, it will cause an issue.

I'm using SQL Server 2017:

DECLARE @a TABLE ( number int, price money, type varchar(2),
                   date date, time time)
INSERT @a VALUES
(23454,1.50, NULL,'2014/02/02','07:00:02'),
(23455,1.60, NULL,'2014/02/02','07:05:02'),
(23456,0.665,'SV','2014/02/02','07:50:48'),
(23457,1.3  ,NULL,'2014/02/02','07:50:45'),
(23658,2.4  ,NULL,'2014/02/02','07:50:47'),
(23660,2.4  ,NULL,'2014/02/02','07:50:49'),
(23465,0.668,'SV','2014/02/02','07:50:46'),
(23467,0.666,'SV','2014/02/02','08:50:40'),
(23668,1.4  ,NULL,'2014/02/02','09:50:49'),
(23466,1.66, NULL,'2014/02/02','08:36:34');

; WITH a AS 
(
     SELECT 
         *,
         ROW_NUMBER() OVER (ORDER BY [date], [time] ) x, 
         ROW_NUMBER() OVER (PARTITION BY CASE WHEN [type] IS NOT NULL THEN 1 ELSE 0 END ORDER BY [date], [time]) y 
     FROM 
         @a
), b AS 
(
     SELECT 
         *,
         x - y as group_nbr,
         ROW_NUMBER() OVER(PARTITION BY x-y ORDER BY x ASC) z1 
     FROM
         a
)
SELECT 
    *,
    CASE 
       WHEN [type] IS NOT NULL 
          THEN LAG(price, z1) OVER (PARTITION BY [type] ORDER BY x) 
          ELSE LAG(price, z1) OVER (ORDER BY x) 
    END
FROM
    b
ORDER BY 
    x

The last record should return 0.666 instead of 1.66.

Output should always return the prior Price value of rows where Type is not null.

Upvotes: 3

Views: 1524

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

WITH DataSource as
(
    SELECT *
      ,COUNT(type) OVER (ORDER BY [date], [time] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  - MAX(IIF(type is null, 0, 1)) OVER (ORDER BY [date], [time] ROWS BETWEEN 0 FOLLOWING AND 0 FOLLOWING) groupID
    FROM @a
), Groups (groupID, value) AS
(
    SELECT groupID
          ,MAX(IIF(type is not null, price, NULL))
    FROM DataSource
    GROUP BY groupID
)
SELECT *
FROM DataSource A
LEFT JOIN Groups B
    ON A.groupID = B.[groupID] + 1

enter image description here

The idea is to divide the rows in groups, then get the price for which group (which will be the price where type is not null. Then, just join the tables and get the price of the previous group.

Upvotes: 3

Related Questions