SQL Streaks

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 6, 2017 at 2:25 am

  #1949632

  Mark Cowne - Tuesday, July 4, 2017 8:24 AM

  Not totally clear what you want but see if this helps

  WITH OrderedSrc AS (
SELECT Site,C_Date,ID,Series,LongRun,Flag,
   ROW_NUMBER() OVER(PARTITION BY Site ORDER BY ID) -
        ROW_NUMBER() OVER(PARTITION BY Site,LongRun ORDER BY ID) AS grp
FROM DataTable
),
Grps AS (
SELECT Site,C_Date,ID,Series,LongRun,Flag,grp,
   ROW_NUMBER() OVER(PARTITION BY Site,LongRun,grp ORDER BY ID) AS rnFwd,
   ROW_NUMBER() OVER(PARTITION BY Site,LongRun,grp ORDER BY ID DESC) AS rnRev
FROM OrderedSrc)
SELECT ID,Site,C_Date,Series,LongRun,
     CASE WHEN LongRun = 1 AND rnFwd + rnRev - 1 >= 7 AND rnFwd = 1 THEN 'LongRunStart'
       WHEN LongRun = 1 AND rnFwd + rnRev - 1 >= 7 AND rnRev = 1 THEN 'LongRunEnd' END AS Flag,
     CASE WHEN LongRun = 1 AND rnFwd + rnRev - 1 >= 7 THEN 'LongRun' END AS Flag2,
     CASE WHEN LongRun = 1 AND rnFwd + rnRev - 1 >= 7 AND rnFwd <= 5 THEN 'ReCalc' END AS Flag3
FROM Grps
ORDER BY Site,ID;

  Thanks that is brilliant your a star 🙂