text: --# Copyright IBM Corp. All Rights Reserved.
  --# SPDX-License-Identifier: Apache-2.0
  
  /*
   * Example of counting rows in a table 1 month at a time.
   * 
   */
  ----Test tables
  --CREATE TABLE  "DATAMART"."TRANSACTIONS"( TXN_DATE DATE )@
  --INSERT INTO   "DATAMART"."TRANSACTIONS" SELECT CURRENT_DATE + ROW_NUMBER() OVER() DAYS FROM SYSCAT.TABLES@
  
  CREATE TABLE DB_ROW_COUNT_BY_DATE (
      TABSCHEMA    VARCHAR(128) NOT NULL
  ,   TABNAME      VARCHAR(128) NOT NULL
  ,   FROM_DATE    DATE NOT NULL    
  ,   TO_DATE      DATE NOT NULL
  ,   ROW_COUNT    BIGINT NOT NULL
  )
  
  @
  
  BEGIN
    DECLARE MIN_DATE  DATE;  
    DECLARE MAX_DATE  DATE;
    DECLARE FROM_DATE DATE;
    DECLARE TO_DATE   DATE;
    --
    SELECT MIN("TXN_DATE"), MAX("TXN_DATE") INTO MIN_DATE, MAX_DATE
    FROM "DATAMART"."TRANSACTIONS" 
    ;
    SET FROM_DATE = MIN_DATE
    ;  
    WHILE FROM_DATE <= MAX_DATE
    DO
        SET TO_DATE = FROM_DATE + 1 MONTH;
        --  
        INSERT INTO   DB_ROW_COUNT_BY_DATE
        SELECT 'DATAMART', 'TRANSACTIONS', FROM_DATE, TO_DATE, COUNT(*) FROM "DATAMART"."TRANSACTIONS"
        WHERE 
            "TXN_DATE" >= FROM_DATE
        AND "TXN_DATE" <  TO_DATE
        ;
  --      COMMIT
  --    ;
        SET FROM_DATE = TO_DATE
        ;
    END WHILE;
  END
  