SQL Server Return the next row value that meets a specific criteria

vsaztqbk 于 12个月前发布在其他

关注(0)|答案(2)|浏览(71)

In SQL Server, I have the below data, where the first seven columns are data I've collected through a query. The last column (Desired) is what I want to show based on the first seven columns, but I can't quite there.

The desired column is supposed to return a value based on this criteria:

If Current = Dest, then show the ToLoc column.
If Current <> Dest, then show the next value in the ToLoc column where Current = Dest (ordered by EntryTime and partitioned by Box and Step).

Code:

DECLARE @t TABLE 
           (
               Box CHAR(20),
               Step CHAR(20),
               Curr CHAR(20),
               Dest CHAR(20),
               FromLoc CHAR(20),
               ToLoc CHAR(20),
               EntryTime datetime,
               Desired CHAR(20)
           )

INSERT INTO @t
VALUES  
('A','40','Lane 1','Lane 3','Storage A','Tunnel B',45212.8375810185,'Storage B'),
('A','40','Lane 2','Lane 3','Tunnel B','Tunnel C',45212.8377777778,'Storage B'),
('A','40','Lane 3','Lane 3','Tunnel C','Storage B',45212.8395833333,'Storage B'),
('A','40','Lane 4','Lane 4','Storage C','Storage C',45212.8460532407,'Storage C'),
('A','40','Lane 5','Lane 5','Storage D','Storage D',45213.1816666667,'Storage D'),
('A','40','Lane 6','Lane 6','Storage E','Storage E',45213.1839930556,'Storage E'),
('A','40','Lane 7','Lane 7','Storage F','Storage F',45214.2659837963,'Storage F'),
('A','40','Lane 8','Lane 8','Storage G','Storage G',45214.2679513889,'Storage G'),
('B','41','Lane 9','Lane 9','Storage H','Storage H',45212.8965162037,'Storage H'),
('B','41','Lane 10','Lane 10','Storage I','Storage I',45212.9026967593,'Storage I'),
('B','41','Lane 11','Lane 11','Storage J','Storage J',45212.9909606481,'Storage J')

SELECT * FROM @t

I have tried using LAST_VALUE but it's not returning what I expected:

SELECT
    LAST_VALUE(To) OVER (PARTITION BY Box, Step, Dest ORDER BY EntryTime)

sql-server

来源：https://stackoverflow.com/questions/77377658/return-the-next-row-value-that-meets-a-specific-criteria

2条答案

按热度按时间

oyt4ldly1#

I believe the following provides a solution for your requirements, I have implemented the required logic for both conditions in a cross apply:

select * 
from t
cross apply(
  select top(1) ToLoc
  from t t2
  where t2.Box = t.Box and t2.Step = t.Step 
    and (
      (t.Curr = t.Dest and t2.EntryTime = t.EntryTime) or
      (t2.Curr = t2.Dest and t2.EntryTime > t.EntryTime)
    )
  order by t2.EntryTime
)d(CalculatedDesired)
order by Box, Step, EntryTime;

See a demo Fiddle

赞(0）回复(0）举报 12个月前

v8wbuo2f2#

This is not "elegant" but, it seems to work:

select tmain.*, 
(select ToLoc from @t where EntryTime = 
CASE
    WHEN tmain.curr = tmain.dest THEN tmain.EntryTime
    ELSE 
        (select min(entrytime) entrytime from @t tsub
        where entrytime > tmain.entrytime 
        and tsub.box = tmain.box and tsub.step = tmain.step --partition by box and step
        and tsub.curr = tsub.dest)
END
) DesiredCalc
from @t tmain

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Return the next row value that meets a specific criteria

2条答案

相关问题

热门标签

最新问答