Sum of last 9 records

• seubanks00

  Mr or Mrs. 500

  Points: 591

  More actions

  October 20, 2017 at 8:24 am

  #333406

  I have a table (MyTable) with the following structure:

  ID   |   AVG | INC        
  -----------------------
  1    |    62   |    0        
  2    |   76    |    1        
  3    |   77    |    1        
  4    |    79   |     0        
  5    |    79   |     0        
  6    |    67   |     1        
  7    |    76   |     1       
  8    |    76   |     1        
  9    |    67   |     1        
  10  |    66   |     1        
  11  |    78   |     1        
  12  |    73   |     1            
  13  |    75   |     1        
  14  |    73   |     1        
  15  |    71   |     1        
  16  |    75   |     0        
  17  |    76   |     1        
  18  |    58   |     0        
  19  |    75   |     1        
  20  |    75   |     1        

  I need to add a column that will return a '1'  whenever the INC value for 9 consecutive rows is '1' like the example below:

  ID    |  AVG | INC   | Test
  ------------------------------
  1     |   62   |     0   |    0
  2     |   76   |     1   |    0
  3     |   77   |     1   |    0
  4     |   79   |     0   |    0
  5     |   79   |     0   |    0
  6     |   67   |     1   |    0
  7     |   76   |     1   |    0
  8     |   76   |     1   |    0
  9     |   67   |     1   |    0
  10   |   66   |     1   |    0
  11   |   78   |     1   |    0
  12   |   73   |     1   |    0    
  13   |   75   |     1   |    0
  14   |   73   |     1   |    1
  15   |   71   |     1   |    1
  16   |   75   |     0   |    0
  17   |   76   |     1   |    0
  18   |   58   |     0   |    0
  19   |   75   |     1   |    0
  20   |   75   |     1   |    0

  I have logic that will give me the sum of the last 9 rows but I'm having trouble pulling it into the table for each record.
  
  SELECT SUM(INC)    -- Get the SUM of 'INC' for the previous 9 records
FROM MyTable
WHERE ID NOT IN (
                    SELECT TOP(
                                SELECT COUNT(*) - 9
                                FROM MyTable
                             ) ID
                    FROM MyTable
                    )

  Can anyone offer some guidance?

  Thanks in advance!

  Edit:  The environment is SQL Server 2008.
           The values of the 'INC' Column will always be '1' or '0'.

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 20, 2017 at 8:49 am

  #1964500

  Here you go.  Not tested.

  -- This relies on there being no gaps or repititons in the ID column.
-- If there are, you'll need to number the rows in a CTE with
-- the ROW_NUMBER function. Performance isn't going to be good
-- if your table is large
SELECT (
    SELECT SUM(INC)
    FROM MyTable
    WHERE ID BETWEEN m.ID - 8 AND m.ID
    )
FROM MyTable m

  -- If you have SQL Server 2012 or later, use this. It's much
-- more concise and will perform better
SELECT SUM(INC) OVER (ORDER BY ID ROWS BETWEEN 8 PRECEDING AND CURRENT ROW)
FROM MyTable

  John

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 20, 2017 at 8:49 am

  #1964501

  seubanks00 - Friday, October 20, 2017 8:24 AM

  I have a table (MyTable) with the following structure:

  ID   |   AVG | INC        
  -----------------------
  1    |    62   |    0        
  2    |   76    |    1        
  3    |   77    |    1        
  4    |    79   |     0        
  5    |    79   |     0        
  6    |    67   |     1        
  7    |    76   |     1       
  8    |    76   |     1        
  9    |    67   |     1        
  10  |    66   |     1        
  11  |    78   |     1        
  12  |    73   |     1            
  13  |    75   |     1        
  14  |    73   |     1        
  15  |    71   |     1        
  16  |    75   |     0        
  17  |    76   |     1        
  18  |    58   |     0        
  19  |    75   |     1        
  20  |    75   |     1        

  I need to add a column that will return a '1'  whenever the INC value for 9 consecutive rows is '1' like the example below:

  ID    |  AVG | INC   | Test
  ------------------------------
  1     |   62   |     0   |    0
  2     |   76   |     1   |    0
  3     |   77   |     1   |    0
  4     |   79   |     0   |    0
  5     |   79   |     0   |    0
  6     |   67   |     1   |    0
  7     |   76   |     1   |    0
  8     |   76   |     1   |    0
  9     |   67   |     1   |    0
  10   |   66   |     1   |    0
  11   |   78   |     1   |    0
  12   |   73   |     1   |    0    
  13   |   75   |     1   |    0
  14   |   73   |     1   |    1
  15   |   71   |     1   |    1
  16   |   75   |     0   |    0
  17   |   76   |     1   |    0
  18   |   58   |     0   |    0
  19   |   75   |     1   |    0
  20   |   75   |     1   |    0

  I have logic that will give me the sum of the last 9 rows but I'm having trouble pulling it into the table for each record.
  
  SELECT SUM(INC)    -- Get the SUM of 'INC' for the previous 9 records
FROM MyTable
WHERE ID NOT IN (
                    SELECT TOP(
                                SELECT COUNT(*) - 9
                                FROM MyTable
                             ) ID
                    FROM MyTable
                    )

  Can anyone offer some guidance?

  Thanks in advance!

  ;WITH SampleData AS (
 SELECT * FROM (VALUES
  (1, 62, 0),(2, 76, 1),(3, 77, 1),(4, 79, 0),(5, 79, 0),
  (6, 67, 1),(7, 76, 1),(8, 76, 1),(9, 67, 1),(10, 66, 1),
  (11, 78, 1),(12, 73, 1),(13, 75, 1),(14, 73, 1),(15, 71, 1),
  (16, 75, 0),(17, 76, 1),(18, 58, 0),(19, 75, 1),(20, 75, 1)
 ) d (ID, [AVG], INC)
)
SELECT ID, [AVG], INC,
 Test = CASE WHEN rt = 9 THEN 1 ELSE 0 END
FROM (
 SELECT *,
  rt = SUM(INC) OVER(ORDER BY ID ROWS BETWEEN 8 PRECEDING AND CURRENT ROW)
 FROM SampleData
) d

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 20, 2017 at 8:58 am

  #1964506

  A simpler solution, assuming that you can only get zeros and ones.
  
WITH SampleData AS (
SELECT * FROM (VALUES
(1, 62, 0),(2, 76, 1),(3, 77, 1),(4, 79, 0),(5, 79, 0),
(6, 67, 1),(7, 76, 1),(8, 76, 1),(9, 67, 1),(10, 66, 1),
(11, 78, 1),(12, 73, 1),(13, 75, 1),(14, 73, 1),(15, 71, 1),
(16, 75, 0),(17, 76, 1),(18, 58, 0),(19, 75, 1),(20, 75, 1)
) d (ID, [AVG], INC)
)
SELECT ID, [AVG], INC,
Test = MIN(INC) OVER(ORDER BY ID ROWS BETWEEN 8 PRECEDING AND CURRENT ROW)
FROM SampleData


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99130

  More actions

  October 20, 2017 at 9:07 am

  #1964509

  SQL Server 2008 Luis/Chris. 😉

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  October 20, 2017 at 9:31 am

  #1964513

  seubanks00 - Friday, October 20, 2017 8:24 AM

  I have a table (MyTable) with the following structure:

  ID   |   AVG | INC        
  -----------------------
  1    |    62   |    0        
  2    |   76    |    1        
  3    |   77    |    1        
  4    |    79   |     0        
  5    |    79   |     0        
  6    |    67   |     1        
  7    |    76   |     1       
  8    |    76   |     1        
  9    |    67   |     1        
  10  |    66   |     1        
  11  |    78   |     1        
  12  |    73   |     1            
  13  |    75   |     1        
  14  |    73   |     1        
  15  |    71   |     1        
  16  |    75   |     0        
  17  |    76   |     1        
  18  |    58   |     0        
  19  |    75   |     1        
  20  |    75   |     1        

  I need to add a column that will return a '1'  whenever the INC value for 9 consecutive rows is '1' like the example below:

  ID    |  AVG | INC   | Test
  ------------------------------
  1     |   62   |     0   |    0
  2     |   76   |     1   |    0
  3     |   77   |     1   |    0
  4     |   79   |     0   |    0
  5     |   79   |     0   |    0
  6     |   67   |     1   |    0
  7     |   76   |     1   |    0
  8     |   76   |     1   |    0
  9     |   67   |     1   |    0
  10   |   66   |     1   |    0
  11   |   78   |     1   |    0
  12   |   73   |     1   |    0    
  13   |   75   |     1   |    0
  14   |   73   |     1   |    1
  15   |   71   |     1   |    1
  16   |   75   |     0   |    0
  17   |   76   |     1   |    0
  18   |   58   |     0   |    0
  19   |   75   |     1   |    0
  20   |   75   |     1   |    0

  I have logic that will give me the sum of the last 9 rows but I'm having trouble pulling it into the table for each record.
  
  SELECT SUM(INC)    -- Get the SUM of 'INC' for the previous 9 records
FROM MyTable
WHERE ID NOT IN (
                    SELECT TOP(
                                SELECT COUNT(*) - 9
                                FROM MyTable
                             ) ID
                    FROM MyTable
                    )

  Can anyone offer some guidance?

  Thanks in advance!

  Edit:  The environment is SQL Server 2008.
           The values of the 'INC' Column will always be '1' or '0'.

  Problem here...  Your desired result does NOT agree with your statement about the previous 9 records.  By that statement, your record with ID = 14 should not have a TEST value of 1, as there are NOT 9 previous records with a value of 1 for INC.  Only if you include the current record as the 9th record does that become true, but then, you'd be saying that the previous 8 records AND the current record must have an INC value of 1.   Here's the code based on 8 previous records, that will work on SQL 2008:
  CREATE TABLE #TEST_TABLE (
    ID int PRIMARY KEY CLUSTERED,
    [AVG] int,
    INC int
);
INSERT INTO #TEST_TABLE (ID, [AVG], INC)
    VALUES    (1, 62, 0),
            (2, 76, 1),
            (3, 77, 1),
            (4, 79, 0),
            (5, 79, 0),
            (6, 67, 1),
            (7, 76, 1),
            (8, 76, 1),
            (9, 67, 1),
            (10, 66, 1),
            (11, 78, 1),
            (12, 73, 1),
            (13, 75, 1),
            (14, 73, 1),
            (15, 71, 1),
            (16, 75, 0),
            (17, 76, 1),
            (18, 58, 0),
            (19, 75, 1),
            (20, 75, 1);

  WITH ORDERED_DATA AS (

      SELECT T.ID, T.[AVG], T.INC, ROW_NUMBER() OVER(ORDER BY T.ID) AS RN
    FROM #TEST_TABLE AS T
)
SELECT T.ID, T.[AVG], T.INC, CASE WHEN ISNULL(T2.THE_COUNT, 0) = 9 THEN 1 ELSE 0 END AS TEST
FROM ORDERED_DATA AS T
    OUTER APPLY (
                SELECT COUNT(CASE WHEN O.INC = 1 THEN 1 ELSE NULL END) AS THE_COUNT
                FROM ORDERED_DATA AS O
                WHERE O.RN BETWEEN T.RN - 8 AND T.RN
                ) AS T2;

  DROP TABLE #TEST_TABLE;

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

• seubanks00

  Mr or Mrs. 500

  Points: 591

  More actions

  October 20, 2017 at 10:48 am

  #1964532

  sgmunson - Friday, October 20, 2017 9:31 AM

  seubanks00 - Friday, October 20, 2017 8:24 AM

  I have a table (MyTable) with the following structure:

  ID   |   AVG | INC        
  -----------------------
  1    |    62   |    0        
  2    |   76    |    1        
  3    |   77    |    1        
  4    |    79   |     0        
  5    |    79   |     0        
  6    |    67   |     1        
  7    |    76   |     1       
  8    |    76   |     1        
  9    |    67   |     1        
  10  |    66   |     1        
  11  |    78   |     1        
  12  |    73   |     1            
  13  |    75   |     1        
  14  |    73   |     1        
  15  |    71   |     1        
  16  |    75   |     0        
  17  |    76   |     1        
  18  |    58   |     0        
  19  |    75   |     1        
  20  |    75   |     1        

  I need to add a column that will return a '1'  whenever the INC value for 9 consecutive rows is '1' like the example below:

  ID    |  AVG | INC   | Test
  ------------------------------
  1     |   62   |     0   |    0
  2     |   76   |     1   |    0
  3     |   77   |     1   |    0
  4     |   79   |     0   |    0
  5     |   79   |     0   |    0
  6     |   67   |     1   |    0
  7     |   76   |     1   |    0
  8     |   76   |     1   |    0
  9     |   67   |     1   |    0
  10   |   66   |     1   |    0
  11   |   78   |     1   |    0
  12   |   73   |     1   |    0    
  13   |   75   |     1   |    0
  14   |   73   |     1   |    1
  15   |   71   |     1   |    1
  16   |   75   |     0   |    0
  17   |   76   |     1   |    0
  18   |   58   |     0   |    0
  19   |   75   |     1   |    0
  20   |   75   |     1   |    0

  I have logic that will give me the sum of the last 9 rows but I'm having trouble pulling it into the table for each record.
  
  SELECT SUM(INC)    -- Get the SUM of 'INC' for the previous 9 records
FROM MyTable
WHERE ID NOT IN (
                    SELECT TOP(
                                SELECT COUNT(*) - 9
                                FROM MyTable
                             ) ID
                    FROM MyTable
                    )

  Can anyone offer some guidance?

  Thanks in advance!

  Edit:  The environment is SQL Server 2008.
           The values of the 'INC' Column will always be '1' or '0'.

  Problem here...  Your desired result does NOT agree with your statement about the previous 9 records.  By that statement, your record with ID = 14 should not have a TEST value of 1, as there are NOT 9 previous records with a value of 1 for INC.  Only if you include the current record as the 9th record does that become true, but then, you'd be saying that the previous 8 records AND the current record must have an INC value of 1.   Here's the code based on 8 previous records, that will work on SQL 2008:
  CREATE TABLE #TEST_TABLE (
    ID int PRIMARY KEY CLUSTERED,
    [AVG] int,
    INC int
);
INSERT INTO #TEST_TABLE (ID, [AVG], INC)
    VALUES    (1, 62, 0),
            (2, 76, 1),
            (3, 77, 1),
            (4, 79, 0),
            (5, 79, 0),
            (6, 67, 1),
            (7, 76, 1),
            (8, 76, 1),
            (9, 67, 1),
            (10, 66, 1),
            (11, 78, 1),
            (12, 73, 1),
            (13, 75, 1),
            (14, 73, 1),
            (15, 71, 1),
            (16, 75, 0),
            (17, 76, 1),
            (18, 58, 0),
            (19, 75, 1),
            (20, 75, 1);

  WITH ORDERED_DATA AS (

      SELECT T.ID, T.[AVG], T.INC, ROW_NUMBER() OVER(ORDER BY T.ID) AS RN
    FROM #TEST_TABLE AS T
)
SELECT T.ID, T.[AVG], T.INC, CASE WHEN ISNULL(T2.THE_COUNT, 0) = 9 THEN 1 ELSE 0 END AS TEST
FROM ORDERED_DATA AS T
    OUTER APPLY (
                SELECT COUNT(CASE WHEN O.INC = 1 THEN 1 ELSE NULL END) AS THE_COUNT
                FROM ORDERED_DATA AS O
                WHERE O.RN BETWEEN T.RN - 8 AND T.RN
                ) AS T2;

  DROP TABLE #TEST_TABLE;

  I think this is it. Thank you!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 20, 2017 at 10:58 am

  #1964539

  Thom A - Friday, October 20, 2017 9:07 AM

  SQL Server 2008 Luis/Chris. 😉

  

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2