How can I write this query?

• manssourianm

  Mr or Mrs. 500

  Points: 556

  More actions

  February 13, 2019 at 9:44 am

  #416696

  Hi,
  I do reporting for a transit agency, wondering if anyone can help with this query.
  How can I go from this table:
  

  select '01' as 'rn', '2017-07-01' as 'dt', '1' as 'status' union all

  select '02' as 'rn', '2017-07-20' as 'dt', '1' as 'status' union all

  select '03' as 'rn', '2017-08-06' as 'dt', '0' as 'status' union all

  select '04' as 'rn', '2017-08-19' as 'dt', '0' as 'status' union all

  select '05' as 'rn', '2017-09-09' as 'dt', '0' as 'status' union all

  select '06' as 'rn', '2017-09-11' as 'dt', '1' as 'status' union all

  select '07' as 'rn', '2017-09-17' as 'dt', '1' as 'status' union all

  select '08' as 'rn', '2017-10-02' as 'dt', '1' as 'status' union all

  select '09' as 'rn', '2017-10-15' as 'dt', '1' as 'status' union all

  select '10' as 'rn', '2017-10-21' as 'dt', '0' as 'status' union all

  select '11' as 'rn', '2017-11-07' as 'dt', '0' as 'status' union all

  select '12' as 'rn', '2017-12-02' as 'dt', '1' as 'status' union all

  select '13' as 'rn', '2018-01-08' as 'dt', '1' as 'status' union all

  select '14' as 'rn', '2018-01-27' as 'dt', '1' as 'status' union all

  select '15' as 'rn', '2018-02-12' as 'dt', '1' as 'status' union all

  select '16' as 'rn', '2018-02-22' as 'dt', '0' as 'status'

  to this one:
  

  select '01' as 'rn', '2017-07-01' as 'dt', '1' as 'status' union all

  select '03' as 'rn', '2017-08-06' as 'dt', '0' as 'status' union all

  select '06' as 'rn', '2017-09-11' as 'dt', '1' as 'status' union all

  select '10' as 'rn', '2017-10-21' as 'dt', '0' as 'status' union all

  select '12' as 'rn', '2017-12-02' as 'dt', '1' as 'status' union all

  select '16' as 'rn', '2018-02-22' as 'dt', '0' as 'status'

  
  The rule is anytime the status flips, I'd like to keep that record. Any help is appreciated!
  -martin

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 13, 2019 at 10:11 am

  #2021946

  Try the following:
  IF OBJECT_ID(N'tempdb..#TEMP_TABLE', N'U') IS NOT NULL
    BEGIN
    DROP TABLE #TEMP_TABLE;
    END;

  SELECT '01' AS 'rn', '2017-07-01' AS 'dt', '1' AS 'status'
INTO #TEMP_TABLE
UNION ALL
SELECT '02' AS 'rn', '2017-07-20' AS 'dt', '1' AS 'status' UNION ALL
SELECT '03' AS 'rn', '2017-08-06' AS 'dt', '0' AS 'status' UNION ALL
SELECT '04' AS 'rn', '2017-08-19' AS 'dt', '0' AS 'status' UNION ALL
SELECT '05' AS 'rn', '2017-09-09' AS 'dt', '0' AS 'status' UNION ALL
SELECT '06' AS 'rn', '2017-09-11' AS 'dt', '1' AS 'status' UNION ALL
SELECT '07' AS 'rn', '2017-09-17' AS 'dt', '1' AS 'status' UNION ALL
SELECT '08' AS 'rn', '2017-10-02' AS 'dt', '1' AS 'status' UNION ALL
SELECT '09' AS 'rn', '2017-10-15' AS 'dt', '1' AS 'status' UNION ALL
SELECT '10' AS 'rn', '2017-10-21' AS 'dt', '0' AS 'status' UNION ALL
SELECT '11' AS 'rn', '2017-11-07' AS 'dt', '0' AS 'status' UNION ALL
SELECT '12' AS 'rn', '2017-12-02' AS 'dt', '1' AS 'status' UNION ALL
SELECT '13' AS 'rn', '2018-01-08' AS 'dt', '1' AS 'status' UNION ALL
SELECT '14' AS 'rn', '2018-01-27' AS 'dt', '1' AS 'status' UNION ALL
SELECT '15' AS 'rn', '2018-02-12' AS 'dt', '1' AS 'status' UNION ALL
SELECT '16' AS 'rn', '2018-02-22' AS 'dt', '0' AS 'status'

  WITH LAG_DATA AS (

      SELECT
        TT.rn,
        TT.dt,
        TT.[status],
        LAG(TT.[status], 1, NULL) OVER(ORDER BY TT.rn) AS old_status
    FROM #TEMP_TABLE AS TT
)
SELECT LD.*
FROM LAG_DATA AS LD
WHERE    LD.old_status IS NULL
    OR    LD.[status] <> LD.old_status
ORDER BY LD.rn;

  DROP TABLE #TEMP_TABLE;

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  February 13, 2019 at 10:35 am

  #2021951

  You can simplify this by using a different default value for the lag function.

  
WITH LAG_DATA AS (

      SELECT
          TT.rn,
          TT.dt,
          TT.[status],
          LAG(TT.[status], 1, '') OVER(ORDER BY TT.rn) AS old_status
      FROM #TEMP_TABLE AS TT
  )
  SELECT LD.*
  FROM LAG_DATA AS LD
  WHERE   LD.[status] <> LD.old_status
  ORDER BY LD.rn;

  I wouldn't expect any major impact on performance, but it would make your WHERE clause easier to write if you needed to add other conditions.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 13, 2019 at 10:54 am

  #2021954

  drew.allen - Wednesday, February 13, 2019 10:35 AM

  You can simplify this by using a different default value for the lag function.

  
WITH LAG_DATA AS (

      SELECT
          TT.rn,
          TT.dt,
          TT.[status],
          LAG(TT.[status], 1, '') OVER(ORDER BY TT.rn) AS old_status
      FROM #TEMP_TABLE AS TT
  )
  SELECT LD.*
  FROM LAG_DATA AS LD
  WHERE   LD.[status] <> LD.old_status
  ORDER BY LD.rn;

  I wouldn't expect any major impact on performance, but it would make your WHERE clause easier to write if you needed to add other conditions.

  Drew

  Excellent suggestion, Drew.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• manssourianm

  Mr or Mrs. 500

  Points: 556

  More actions

  February 13, 2019 at 11:18 am

  #2021960

  OK, that was easier than I was making it. Thank you Drew, I used your solution.
  -martin