daily pastebin goal
51%
SHARE
TWEET

Untitled

a guest Feb 19th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --demo setup based on your sample data
  2. 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))
  3.  
  4. INSERT INTO @T(DIV, LS, TT, TN, LAT, LONG, [DATE], ORDER_NUM) VALUES
  5. ('BRIDGE',4,'M',1,128.2,186.638, '2/11/2019 17:58', '1004085730'),
  6. ('BRIDGE',4,'M',1,128.2,186.638, '2/13/2019 15:22', '1004085755'),
  7. ('BRIDGE',4,'M',1,128.2,186.638, '2/17/2019 14:22', '1004104405'),
  8. ('CROSS',4,'M',1,170.4,190.2, '2/1/2019 14:22', '1004104405'),
  9. ('CROSS',4,'M',1,170.4,190.2, '2/10/2019 14:22', '1004104520'),
  10. ('CROSS',4,'M',1,170.4,190.2, '2/17/2019 14:22', '1004104590')
  11.  
  12. --the query
  13. ;WITH CTE
  14. AS (
  15.     SELECT *
  16.         ,ROW_NUMBER() OVER (
  17.             PARTITION BY DIV ORDER BY [DATE] DESC
  18.             ) AS rn
  19.         ,LEAD([date]) OVER (
  20.             PARTITION BY div ORDER BY [date] DESC
  21.             ) AS prev
  22.     FROM @T
  23.     )
  24. SELECT div,ls,tt,tn,lat,long,[date] AS Latest_Test_Date,prev AS Second_Latest_Dte,ORDER_NUM
  25. FROM CTE
  26. WHERE rn = 1
  27.      
  28. | div    | ls | tt | tn | lat     | long    | Latest_Test_Date        | Second_Latest_Dte       | ORDER_NUM  |
  29. |--------|----|----|----|---------|---------|-------------------------|-------------------------|------------|
  30. | BRIDGE | 4  | M  | 1  | 128.200 | 186.638 | 2019-02-17 14:22:00.000 | 2019-02-13 15:22:00.000 | 1004104405 |
  31. | CROSS  | 4  | M  | 1  | 170.400 | 190.200 | 2019-02-17 14:22:00.000 | 2019-02-10 14:22:00.000 | 1004104590 |
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top