Search code examples
sqloraclewindow-functions

Get running total of consecutive values

I have a table that stores the hours reported by users for activities completed, by reporting year and month. I have a function in an old Oracle database, that I need to replicate in SQL Server, that shows the current value for consecutive 0 hour reports. i.e. when someone hasn't reported hours for 3 months in a row, the number would be 3, if they have reported for a given month, the counter would then be reset back to 0, so as to only count the consecutive reports with 0 hours.

The table is always pre-filled with the expected amount of reporting periods for the user. i.e. if the user is expected to report hours for 6 months, we create the 6 rows in this table, with NULL in the HOURS_REPORTED field.

Here is a sample of the PLSQL.

FUNCTION FN_NIL_RPT_CNT ( PI_USER_ID IN NUMBER )
 RETURN NUMBER
 IS

 LV_NIL_CNT NUMBER := 0;

 CURSOR CR_DTLS IS
 SELECT YEAR
       ,MONTH
       ,HOURS_REPORTED
 FROM HOURS
 WHERE STATUS = 'CURRENT'
       AND USER_ID = PI_USER_ID
 ORDER BY TO_DATE(MONTH||YEAR, 'mmyyyy'); 

 BEGIN
   FOR LR_HR IN CR_DTLS
   LOOP
     EXIT WHEN TO_DATE(LR_HR.MONTH||LR_HR.YEAR,'mmyyyy') >= TRUNC(SYSDATE,'MM');

     IF LR_HR.HOURS_REPORTED IS NOT NULL
     THEN
       IF LR_HR.HOURS_REPORTED = 0
       THEN
         LV_NIL_CNT := LV_NIL_CNT + 1;
       ELSE
         LV_NIL_CNT := 0;
       END IF;
     ELSE 
       NULL; -- ignore
     END IF;
   END LOOP;
 END;
 RETURN LV_NIL_CNT;
END FN_NIL_RPT_CNT;

So if the cursor contained the following, the expected result would be 2 because hours were reported in 2019/10, which would then reset the counter to 0, and the subsequent 2 months had 0 hours reported.

Solution

  • My suggestion is to make use of straight sql, and to lose the cursor logic. Anyday its going to be more efficient, faster and less expensive

    Eg: the following query can get you the last_consecutive_count

    CREATE TABLE dbo.t
(
    USER_ID INT
   ,YEAR INT
   ,MONTH INT
   ,HOURS_REPORTED INT
);

insert into dbo.t
VALUES
( 254, '2017', '12', 0 ), 
( 254, '2018', '01', 8 ), 
( 254, '2018', '02', 11 ), 
( 254, '2018', '03', 16 ), 
( 254, '2018', '04', 12 ), 
( 254, '2018', '05', 16 ), 
( 254, '2018', '06', 20 ), 
( 254, '2018', '07', 14 ), 
( 254, '2018', '08', 12 ), 
( 254, '2018', '09', 11 ), 
( 254, '2018', '10', 22 ), 
( 254, '2018', '11', 10 ), 
( 254, '2018', '12', 6 ), 
( 254, '2019', '01', 11 ), 
( 254, '2019', '02', 12 ), 
( 254, '2019', '03', 5 ), 
( 254, '2019', '04', 12 ), 
( 254, '2019', '05', 10 ), 
( 254, '2019', '06', 9 ), 
( 254, '2019', '07', 10 ), 
( 254, '2019', '08', 9 ), 
( 254, '2019', '09', 0 ), 
( 254, '2019', '10', 4 ), 
( 254, '2019', '11', 8 ), 
( 254, '2019', '12', 0 ), 
( 254, '2020', '01', 0 ), 
( 254, '2020', '02', 5 ),
( 254, '2020', '03', 7 )



WITH data
AS (SELECT *
          ,CAST(CONCAT(YEAR, '-', MONTH, '-01') AS DATE) AS dt
          ,ROW_NUMBER() OVER (ORDER BY YEAR desc, MONTH desc) AS rnk
          ,ROW_NUMBER() OVER (partition by case when hours_reported=0 then 1 end ORDER BY YEAR desc, MONTH desc) AS rnk2
          ,DATEADD(MONTH, -ROW_NUMBER() OVER (ORDER BY YEAR, MONTH), CAST(CONCAT(YEAR, '-', MONTH, '-01') AS DATE)) AS grp
    FROM dbo.t
    WHERE USER_ID = 254 /* you can parameterize this in your sql server function*/
    )
   ,last_val
    as(SELECT *
          ,case when hours_reported<>0 then rnk else rnk-rnk2 end as val1
          ,rank() over(order by case when hours_reported<>0 then rnk else rnk-rnk2 end) as rnk_min
      FROM data
     )
select count(*) as last_consec_cnt
  from last_val
 where rnk_min=1


+----------------------+
| last_consecutive_cnt |
+----------------------+
|                    1 |
+----------------------+

    You can put this in a function and pass the user_id to the table..

    Here is a db fiddle link. https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=9ac60e62a99e2465cc8e979ae9f29f7c