Find value of next row upon condition

Question

Find value of next row upon condition

Matrix_you

SSC Enthusiast

Points: 189
More actions
February 22, 2023 at 4:03 pm

#4152850

In the following table,

I want to replace the zero in the column idPointsrv by the next row that is not a zero in this case replace all zero by 316770
Thanks in advance for your help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 1

It's helpful to provide sample data as a table and insert statements. Also, are you guarantee on the first column for ordering? I see these aren't sequential, but are they ordered?

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 2

Create some test data:

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable
(
    NoSeqTrt INT,
    NoSeqIti INT,
    idPointsrv INT
)
;
GO
-- Populate TestTable with test data
INSERT INTO TestTable(NoSeqTrt, NoSeqIti, idPointsrv)
VALUES (1, 0, 13157),
       (5, 2, 0),
       (6, 3, 0),
       (6, 22, 0),
       (7, 8, 0),
       (7, 13, 0),
       (7, 17, 0),
       (8, 15, 316770),
       (8, 54, 316770)
;

A select statement:

SELECT t.NoSeqTrt, t.NoSeqIti, ISNULL(t2.idPointsrv, t.idPointsrv) idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt < t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti < t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt DESC, t2.NoSeqIti  DESC
             ) t2(idPointsrv)
ORDER BY 1, 2
;

An update statement:

UPDATE t
   SET t.idPointsrv = t2.idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt < t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti < t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt DESC, t2.NoSeqIti  DESC
             ) t2(idPointsrv)
 WHERE t.idPointsrv = 0
;

This reply was modified 1 year, 1 month ago by Jonathan AC Roberts. Reason: corrected

Matrix_you SSC Enthusiast Points: 189 More actions · Answer 3

Matrix_you

SSC Enthusiast

Points: 189

February 22, 2023 at 8:01 pm

#4152961

Thank you for your input

drew.allen SSC Guru Points: 76944 More actions · Answer 4

Jonathan AC Roberts wrote:

Create some test data:

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable
(
    NoSeqTrt INT,
    NoSeqIti INT,
    idPointsrv INT
)
;
GO
-- Populate TestTable with test data
INSERT INTO TestTable(NoSeqTrt, NoSeqIti, idPointsrv)
VALUES (1, 0, 13157),
       (5, 2, 0),
       (6, 3, 0),
       (6, 22, 0),
       (7, 8, 0),
       (7, 13, 0),
       (7, 17, 0),
       (8, 15, 316770),
       (8, 54, 316770)
;

A select statement:

SELECT t.NoSeqTrt, t.NoSeqIti, ISNULL(t2.idPointsrv, t.idPointsrv) idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt < t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti < t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt DESC, t2.NoSeqIti  DESC
             ) t2(idPointsrv)
ORDER BY 1, 2
;

An update statement:

UPDATE t
   SET t.idPointsrv = t2.idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt < t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti < t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt DESC, t2.NoSeqIti  DESC
             ) t2(idPointsrv)
 WHERE t.idPointsrv = 0
;

I believe that it's a lot more efficient to use a windowed function here. Since we don't know whether the idPointsrv is monotonically increasing, I've coded it in a more flexible, but also more complicated way.

SELECT tt.NoSeqTrt
     , tt.NoSeqIti
     , tt.idPointsrv
     , CAST(SUBSTRING(MIN(b.bin_data) OVER(ORDER BY tt.NoSeqTrt, tt.NoSeqIti ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 11, 5) AS INT) AS idPointsrv
     -- If idPointsrv is monotonically increasing, we can use the simpler MIN(idPointSrv) OVER(....
     -- and get rid of all of the casting to binary and back.
FROM #TestTable AS tt
CROSS APPLY (VALUES(CAST(tt.NoSeqTrt AS BINARY(5)) + CAST(tt.NoSeqIti AS BINARY(5)) + CAST(NULLIF(tt.idPointsrv,0) AS BINARY(5)))) AS b(bin_data)
ORDER BY tt.NoSeqTrt, tt.NoSeqIti

I also noticed that your code doesn't give the expected results.

Here are the statistics:

Jonathan's

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 161 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 9 ms.

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________00000001FF19'. Scan count 10, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Drew's

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 15 ms.
SQL Server parse and compile time: 
   CPU time = 1 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________00000001FF19'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 5

drew.allen wrote:

I believe that it's a lot more efficient to use a windowed function here. Since we don't know whether the idPointsrv is monotonically increasing, I've coded it in a more flexible, but also more complicated way.

SELECT tt.NoSeqTrt
     , tt.NoSeqIti
     , tt.idPointsrv
     , CAST(SUBSTRING(MIN(b.bin_data) OVER(ORDER BY tt.NoSeqTrt, tt.NoSeqIti ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 11, 5) AS INT) AS idPointsrv
     -- If idPointsrv is monotonically increasing, we can use the simpler MIN(idPointSrv) OVER(....
     -- and get rid of all of the casting to binary and back.
FROM #TestTable AS tt
CROSS APPLY (VALUES(CAST(tt.NoSeqTrt AS BINARY(5)) + CAST(tt.NoSeqIti AS BINARY(5)) + CAST(NULLIF(tt.idPointsrv,0) AS BINARY(5)))) AS b(bin_data)
ORDER BY tt.NoSeqTrt, tt.NoSeqIti

I also noticed that your code doesn't give the expected results.

Here are the statistics:

Jonathan's

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 161 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 9 ms.

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________00000001FF19'. Scan count 10, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Drew's

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 15 ms.
SQL Server parse and compile time: 
   CPU time = 1 ms, elapsed time = 1 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#TestTable__________________________________________________________________________________________________________00000001FF19'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Drew

Thanks, I just rechecked the question and realise I initially misread it. I was selecting the previous no-zero result not the next.

Here is the corrected it to give the next not previous

Select

SELECT t.NoSeqTrt, t.NoSeqIti, ISNULL(t2.idPointsrv, t.idPointsrv) idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt > t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti > t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt ASC, t2.NoSeqIti  ASC
             ) t2(idPointsrv)
ORDER BY 1, 2
;

Update

UPDATE t
   SET t.idPointsrv = t2.idPointsrv
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
                FROM TestTable t2
               WHERE (t2.NoSeqTrt > t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti > t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt ASC, t2.NoSeqIti ASC
             ) t2(idPointsrv)
 WHERE t.idPointsrv = 0
;

And also your method looks to be much faster.

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 6

I thought I'd write a test to compare the performance of both methods:

Set up some test data using dbo.fnTally with about 25,000 rows of data:

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable
(
    NoSeqTrt INT,
    NoSeqIti INT,
    idPointsrv INT
)
;
GO

INSERT INTO TestTable(NoSeqTrt, NoSeqIti, idPointsrv)
SELECT a.N NoSeqTrt, y.NoSeqIti, 
       IIF(y.NoSeqIti%2=0,0,y.NoSeqIti)  idPointsrv
FROM dbo.fnTally(1, 10000) a
 cross apply (values ((ABS(CHECKSUM(NewId())) % 4) + 1)) t(RandomInt)
 cross apply (select NULL from dbo.fnTally(1,RandomInt)) x(x)
 cross apply (values ((ABS(CHECKSUM(NewId())) % 40000) + 1)) y(NoSeqIti)
 ;

Run Test:

DROP TABLE IF EXISTS #x
DROP TABLE IF EXISTS #y

SET STATISTICS IO, TIME ON
GO
SELECT tt.NoSeqTrt
     , tt.NoSeqIti
     , tt.idPointsrv idPointsrv
     , CAST(SUBSTRING(MIN(b.bin_data) OVER(ORDER BY tt.NoSeqTrt, tt.NoSeqIti ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 11, 5) AS INT) AS NewidPointsrv
     -- If idPointsrv is monotonically increasing, we can use the simpler MIN(idPointSrv) OVER(....
     -- and get rid of all of the casting to binary and back.
INTO #x
FROM TestTable AS tt
CROSS APPLY (VALUES(CAST(tt.NoSeqTrt AS BINARY(5)) + CAST(tt.NoSeqIti AS BINARY(5)) + CAST(NULLIF(tt.idPointsrv,0) AS BINARY(5)))) AS b(bin_data)
ORDER BY tt.NoSeqTrt, tt.NoSeqIti
GO
SELECT t.NoSeqTrt, t.NoSeqIti, t.idPointsrv, ISNULL(t2.idPointsrv, t.idPointsrv) NewidPointsrv
INTO #y
  FROM TestTable t
 OUTER APPLY (SELECT TOP(1) CASE WHEN t.idPointsrv = 0 THEN t2.idPointsrv 
                                 ELSE t.idPointsrv
                            END idPointsrv
               FROM TestTable t2
               WHERE (t2.NoSeqTrt > t.NoSeqTrt
                        OR (t2.NoSeqTrt = t.NoSeqTrt
                               AND t2.NoSeqIti > t.NoSeqIti
                            )
                      )
                AND t2.idPointsrv <> 0
               ORDER BY t2.NoSeqTrt ASC, t2.NoSeqIti  ASC
             ) t2(idPointsrv)
 ORDER BY 1, 2
;

Results

Windowed Function

***************************************************** Start Windowed Function

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'TestTable'. Scan count 1, logical reads 130, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 94 ms,  elapsed time = 89 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(49832 rows affected)
***************************************************** End Windowed Function

Subselect

***************************************************** Start Sub Select
Table 'TestTable'. Scan count 49837, logical reads 6478290, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 358342 ms,  elapsed time = 95428 ms.

(49832 rows affected)
***************************************************** Start Sub Select

So 89 ms for Drew's select compared to 95,482 ms for my sub-select. So Drew's is about 1000 times faster!