Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --demo setup based on your sample data
- Declare @T table(DIV VARCHAR(20), LS INT, TT VARCHAR(5), TN INT, LAT DECIMAL(7,3), LONG DECIMAL(7,3), [DATE] DATETIME, ORDER_NUM VARCHAR(30))
- INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
- ('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
- ('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
- ('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
- ('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
- ('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
- ('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')
- --the query
- ;WITH CTE
- AS (
- SELECT *
- ,ROW_NUMBER() OVER (
- PARTITION BY DIV ORDER BY [DATE] DESC
- ) AS rn
- ,LEAD([date]) OVER (
- PARTITION BY div ORDER BY [date] DESC
- ) AS prev
- FROM @T
- )
- SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
- FROM CTE
- WHERE rn = 1
- | div | ls | tt | tn | lat | long | Latest_Test_Date | Second_Latest_Dte | ORDER_NUM |
- |--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
- | BRIDGE | 4 | M | 1 | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
- | CROSS | 4 | M | 1 | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement