Query to retain latest non-zero value

user_4357650 0
Viewed 162

I'm using SQL Server 2016 and have a table with the following data:

PlaySeq TransMaxValue
1 250
2 500
3 0
4 400
5 0
6 300
7 500
8 0
9 0

I'm trying to construct a query that adds a column TransMaxValueContd to that dataset.
That TransMaxValueContd column should retain the last non-zero value encountered in column TransMaxValue.
Ordering is done by PlaySeq.

My expected result from the query is this:

PlaySeq TransMaxValue TransMaxValueContd
1 250 250
2 500 500
3 0 500
4 400 400
5 0 400
6 300 300
7 500 500
8 0 500
9 0 500

I've been using windowing functions like LAST_VALUE() and LAG(), but can't seem to get the results right.
Maybe I'm overcomplicating it.
Does anyone know how to do this?

ps: I'm just looking for the query. No need to modify the source table.

Edit: Added a SQLFiddle example with a failed attempt, which is:

SELECT a.PlaySeq, 
       a.TransMaxValue,
       IIF(ISNULL(LAG(a.TransMaxValue,1) OVER (ORDER BY a.PlaySeq), a.TransMaxValue) = a.TransMaxValue, a.TransMaxValue, LAG(a.TransMaxValue,1) OVER (ORDER BY a.PlaySeq)) AS TransMaxValueContd
FROM   myTable AS a;

Edit: Thank you for all the answers, which provided new useful insights!
I ended up using this, inspired by the answer from SteveC.

SELECT  a.PlaySeq, 
        a.TransMaxValue,
        (SELECT     TOP 1 x.TransMaxValue 
         FROM       myTable AS x 
         WHERE      x.PlaySeq <= a.PlaySeq 
         AND        x.TransMaxValue != 0 
         ORDER BY   x.PlaySeq DESC) AS TransMaxValueContd
FROM    myTable AS a;
sql-server-2016 tsql sql-server
4 Answers

A simple way could use OUTER APPLY and SELECT TOP(1). Something like this

select m.PlaySeq, m.[TransMaxValue],
       case when m.[TransMaxValue]=0 
            then oa.TransMaxValue 
            else m.TransMaxValue end TransMaxValueContd
from myTable m
     outer apply (select top(1) mm.[TransMaxValue]
                  from myTable mm
                  where m.PlaySeq>mm.PlaySeq
                        and mm.[TransMaxValue]>0
                  order by mm.PlaySeq desc) oa;

PlaySeq TransMaxValue   TransMaxValueContd
1       250             250
2       500             500
3       0               500
4       400             400
5       0               400
6       300             300
7       500             500
8       0               500
9       0               500
user_67487580

Perhaps this will help:

DECLARE @Tab TABLE(PlaySeq INT, TransMaxValue INT)
INSERT @Tab
VALUES(1,250),(2,500),(3,0),(4,400),(5,0)
     ,(6,300),(7,500),(8,0),(9,0)

SELECT PlaySeq, 
       TransMaxValue,
       FIRST_VALUE(TransMaxValue) OVER(PARTITION BY Grp ORDER BY PlaySeq) AS TransMaxValueContd
FROM (
      SELECT
        PlaySeq,
        TransMaxValue,
        SUM(CASE WHEN TransMaxValue = 0 THEN 0 ELSE 1 END) OVER(ORDER BY PlaySeq) AS Grp
      FROM @Tab
) AS tbl
ORDER BY PlaySeq
user_41110900

Please try the following solution.

It is based on Windows function ROW_NUMBER() and non-equal relationship clause:

FROM @tbl AS t1 INNER JOIN 
            @tbl AS t2 ON t1.PlaySeq > t2.PlaySeq

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (PlaySeq INT, TransMaxValue INT);
INSERT INTO @tbl (PlaySeq, TransMaxValue) VALUES
(1, 250),
(2, 500),
(3, 0  ),
(4, 400),
(5, 0  ),
(6, 300),
(7, 500),
(8, 0  ),
(9, 0  );
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT t1.*
        , t2.TransMaxValue AS prev_value
        , ROW_NUMBER() OVER (PARTITION BY t1.PlaySeq ORDER BY t2.PlaySeq DESC) AS seq
    FROM @tbl AS t1 INNER JOIN 
        @tbl AS t2 ON t1.PlaySeq > t2.PlaySeq
    WHERE t2.TransMaxValue > 0
)
SELECT PlaySeq, TransMaxValue, TransMaxValue AS TransMaxValueContd
FROM @tbl WHERE PlaySeq = 1
UNION ALL
SELECT PlaySeq, TransMaxValue, IIF(TransMaxValue > 0, TransMaxValue, prev_value) AS TransMaxValueContd
FROM rs
WHERE seq = 1
ORDER BY PlaySeq ASC;

Output

+---------+---------------+--------------------+
| PlaySeq | TransMaxValue | TransMaxValueContd |
+---------+---------------+--------------------+
|       1 |           250 |                250 |
|       2 |           500 |                500 |
|       3 |             0 |                500 |
|       4 |           400 |                400 |
|       5 |             0 |                400 |
|       6 |           300 |                300 |
|       7 |           500 |                500 |
|       8 |             0 |                500 |
|       9 |             0 |                500 |
+---------+---------------+--------------------+
user_19323110

There may be a better way, but you could try using correlated subqueries:

SELECT q.PlaySeq, q.TransMaxValue
 , (CASE 
      WHEN q.TransMaxValue <> 0 THEN q.TransMaxValue 
      ELSE (SELECT d.TransMaxValue FROM myTable d WHERE d.PlaySeq = q.PlaySeqRef) 
    END) TransMaxValueContd
      FROM (
         SELECT PlaySeq
              , TransMaxValue
              , (SELECT MAX(PlaySeq) 
                    FROM myTable b 
                   WHERE b.PlaySeq < a.PlaySeq 
                     AND b.TransMaxValue <> 0) PlaySeqRef
             FROM myTable a) q;
user_84825880
Related