Running total for previous 7 rows

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 3, 2017 at 6:32 am

  #343368

  Hi I need a flag to tell me there is 7 1s in a row per site

  This woud class as a long run

  Anyone get an ideas? i have been trying for some while

  thanks in advance

  Site	MonthYr	LongRun
  A	Jan-16	1
  A	Feb-16	0
  A	Mar-16	0
  A	Apr-16	0
  A	May-16	0
  A	Jun-16	0
  A	Jul-16	1
  A	Aug-16	1
  A	Sep-16	1
  B	Jan-16	1
  B	Feb-16	0
  B	Mar-16	1
  B	Apr-16	1
  B	May-16	1
  B	Jun-16	1
  B	Jul-16	1
  B	Aug-16	1
  B	Sep-16	1

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 3, 2017 at 6:39 am

  #1949120

  Are you really using SQL 2008?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 3, 2017 at 6:51 am

  #1949121

  No I'm using 17 but the databases are old and most of the new functionality wont work so I thought id play it safe and put it in 08 forum

  thanks

• Neil Burton

  SSC-Insane

  Points: 23002

  More actions

  July 3, 2017 at 7:57 am

  #1949142

  michelle.mabbs - Monday, July 3, 2017 6:32 AM

  Hi I need a flag to tell me there is 7 1s in a row per site

  This woud class as a long run

  Anyone get an ideas? i have been trying for some while

  thanks in advance

  Site	MonthYr	LongRun
  A	Jan-16	1
  A	Feb-16	0
  A	Mar-16	0
  A	Apr-16	0
  A	May-16	0
  A	Jun-16	0
  A	Jul-16	1
  A	Aug-16	1
  A	Sep-16	1
  B	Jan-16	1
  B	Feb-16	0
  B	Mar-16	1
  B	Apr-16	1
  B	May-16	1
  B	Jun-16	1
  B	Jul-16	1
  B	Aug-16	1
  B	Sep-16	1

  
DECLARE @Streak TABLE
(
    Site        CHAR(1)
    ,MonthYr    VARCHAR(6)
    ,LongRun    BIT
    
);

  INSERT INTO @Streak
VALUES
('A','Jan-16',1),
('A','Feb-16',0),
('A','Mar-16',0),
('A','Apr-16',0),
('A','May-16',0),
('A','Jun-16',0),
('A','Jul-16',1),
('A','Aug-16',1),
('A','Sep-16',1),
('A','Oct-16',1),
('A','Nov-16',1),
('A','Dec-16',1),
('A','Jan-17',1),
('A','Feb-17',0),
('A','Mar-17',0),
('A','Apr-17',1),
('A','May-17',1),
('A','Jun-17',1),
('B','Jan-16',1),
('B','Feb-16',0),
('B','Mar-16',1),
('B','Apr-16',1),
('B','May-16',1),
('B','Jun-16',1),
('B','Jul-16',1),
('B','Aug-16',1),
('B','Sep-16',1),
('B','Oct-16',1),
('B','Nov-16',1),
('B','Dec-16',1),
('B','Jan-17',1),
('B','Feb-17',1),
('B','Mar-17',1),
('B','Apr-16',1),
('B','May-17',1),
('B','Jun-17',1),
('B','Jul-17',1)
;

  WITH cte_base as
(
    SELECT
        Site
        ,MonthYr
        ,LongRun
        ,C_Date
        ,CASE
        WHEN LongRun = 1
            THEN
            (
            ROW_NUMBER() OVER(PARTITION BY Site ORDER BY d.C_date ASC)
            -
            ROW_NUMBER() OVER(PARTITION BY Site,LongRun ORDER BY d.C_Date ASC)
            )
        ELSE NULL END AS Difference
    
    FROM
        @Streak
        CROSS APPLY
        (
            SELECT CAST('01 '+REPLACE(MonthYr,'-',' ')AS DATE) AS C_Date
        )d    
)
,cte_run AS
(

      SELECT
        Site
        ,MonthYr
        ,LongRun
        ,C_Date
        ,CASE WHEN LongRun = 1 THEN ROW_NUMBER() OVER(PARTITION BY site,difference ORDER BY C_Date) ELSE NULL END AS Series
    FROM cte_base
    
)

  SELECT
    site
    ,MonthYr
    ,LongRun
    ,CASE WHEN Series %7 = 0 THEN 'Longrun' ELSE NULL END AS Flag
FROM
cte_run

  ORDER BY
        Site
        ,C_Date;

  This will calculate a 'streak' of results and flag every seven consecutive 1's.  It's based on this[/url] which gives a more full explanation.  It will work on any version higher than 2005.  I've cleaned up the dates in the CROSS APPLY to actually make them dates rather than VARCHAR.

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  July 3, 2017 at 8:23 am

  #1949155

  michelle.mabbs - Monday, July 3, 2017 6:51 AM

  No I'm using 17 but the databases are old and most of the new functionality wont work so I thought id play it safe and put it in 08 forum

  Err.... SQL Server 2017 hasn't been released yet, so if you're using that, I'll be very surprised.

  There're a couple ways to do this, depending on the version of SQL that you have, so what version are you using here?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• TrooperMichelleM

  SSC-Addicted

  Points: 444

  More actions

  July 3, 2017 at 8:44 am

  #1949171

  Neil Burton - Monday, July 3, 2017 7:57 AM

  michelle.mabbs - Monday, July 3, 2017 6:32 AM

  Hi I need a flag to tell me there is 7 1s in a row per site

  This woud class as a long run

  Anyone get an ideas? i have been trying for some while

  thanks in advance

  Site	MonthYr	LongRun
  A	Jan-16	1
  A	Feb-16	0
  A	Mar-16	0
  A	Apr-16	0
  A	May-16	0
  A	Jun-16	0
  A	Jul-16	1
  A	Aug-16	1
  A	Sep-16	1
  B	Jan-16	1
  B	Feb-16	0
  B	Mar-16	1
  B	Apr-16	1
  B	May-16	1
  B	Jun-16	1
  B	Jul-16	1
  B	Aug-16	1
  B	Sep-16	1

  
DECLARE @Streak TABLE
(
    Site        CHAR(1)
    ,MonthYr    VARCHAR(6)
    ,LongRun    BIT
    
);

  INSERT INTO @Streak
VALUES
('A','Jan-16',1),
('A','Feb-16',0),
('A','Mar-16',0),
('A','Apr-16',0),
('A','May-16',0),
('A','Jun-16',0),
('A','Jul-16',1),
('A','Aug-16',1),
('A','Sep-16',1),
('A','Oct-16',1),
('A','Nov-16',1),
('A','Dec-16',1),
('A','Jan-17',1),
('A','Feb-17',0),
('A','Mar-17',0),
('A','Apr-17',1),
('A','May-17',1),
('A','Jun-17',1),
('B','Jan-16',1),
('B','Feb-16',0),
('B','Mar-16',1),
('B','Apr-16',1),
('B','May-16',1),
('B','Jun-16',1),
('B','Jul-16',1),
('B','Aug-16',1),
('B','Sep-16',1),
('B','Oct-16',1),
('B','Nov-16',1),
('B','Dec-16',1),
('B','Jan-17',1),
('B','Feb-17',1),
('B','Mar-17',1),
('B','Apr-16',1),
('B','May-17',1),
('B','Jun-17',1),
('B','Jul-17',1)
;

  WITH cte_base as
(
    SELECT
        Site
        ,MonthYr
        ,LongRun
        ,C_Date
        ,CASE
        WHEN LongRun = 1
            THEN
            (
            ROW_NUMBER() OVER(PARTITION BY Site ORDER BY d.C_date ASC)
            -
            ROW_NUMBER() OVER(PARTITION BY Site,LongRun ORDER BY d.C_Date ASC)
            )
        ELSE NULL END AS Difference
    
    FROM
        @Streak
        CROSS APPLY
        (
            SELECT CAST('01 '+REPLACE(MonthYr,'-',' ')AS DATE) AS C_Date
        )d    
)
,cte_run AS
(

      SELECT
        Site
        ,MonthYr
        ,LongRun
        ,C_Date
        ,CASE WHEN LongRun = 1 THEN ROW_NUMBER() OVER(PARTITION BY site,difference ORDER BY C_Date) ELSE NULL END AS Series
    FROM cte_base
    
)

  SELECT
    site
    ,MonthYr
    ,LongRun
    ,CASE WHEN Series %7 = 0 THEN 'Longrun' ELSE NULL END AS Flag
FROM
cte_run

  ORDER BY
        Site
        ,C_Date;

  This will calculate a 'streak' of results and flag every seven consecutive 1's.  It's based on this[/url] which gives a more full explanation.  It will work on any version higher than 2005.  I've cleaned up the dates in the CROSS APPLY to actually make them dates rather than VARCHAR.

  Oooh you've done wow thank you ever so much, I'm over the moon