sql stored-procedures macros db2 declare

Declare and Loop using Stored Procedures/Macros in DB2 SQL

I have a table_A which has names and delivery dates as follows

Name	Del_dt
Mark	2022-04-01
Mark	2022-04-03
Mark	2022-04-05
Mark	2022-04-07
Mark	2022-04-09
Dave	2022-04-02
Dave	2022-04-04
Dave	2022-04-06
Dave	2022-04-08
Dave	2022-04-10

and so on. I want to have a cumulative count of Name upto a particular date. For e.g. Output sample for date from 2022-04-07 to 2022-04-10

Date	Name	cul_cnt
2022-04-07	Mark	4
2022-04-08	Mark	4
2022-04-09	Mark	5
2022-04-10	Mark	5
2022-04-07	Dave	3
2022-04-08	Dave	4
2022-04-09	Dave	4
2022-04-10	Dave	5

Ofc the dates can be common for different names. I am trying to use Dynamic SQL (as I am a beginner and want to explore more). I have generated the following code based on what I could find about Dynamic SQL methods. I am also open to other methods too.

begin
    declare cur_date date;
    declare og_date date;
    declare Cust_Name varchar2(4);
    declare cul_cnt number;
    set cur_date = current_date; /*today's date*/
    set og_date = cur_date - 10; /*calculating cumulative counts for last 10 days*/
loop
    select Name,count(Name) into Cust_Name,cul_cnt,og_date
    from table_A
    where prod_type like 'SHOES' /*Another column in the table just for filter*/
    and Del_dt <= og_date
    group by Name;
    og_date = og_date + 1;
exit when (og_date > cur_date);
end;
end;

Currently I am getting this error

Error report -
Error starting at line : 1 in command -
DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CUR_DATE;SECTION, DRIVER=3.69.49

However, I am sure there would be more errors in the code. How can I get the desired output as shown in the Output table. Also, how do I get the og_date (Date column in output) as it is not present in the original table_A, but a defined variable

Solution

Try this:

 WITH 
-- Table of 10 dates generation starting from 2022-04-10
-- CURRENT_DATE can be used instead   
  D (I, DT) AS 
(
  SELECT 1, DATE ('2022-04-10') FROM SYSIBM.SYSDUMMY1
    UNION ALL
  SELECT I + 1, DT - 1 DAY FROM D WHERE I < 10
)
/*
, table_A (Name, Del_dt) AS 
(
          SELECT 'Mark', DATE ('2022-04-01') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-03') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-05') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-07') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Mark', DATE ('2022-04-09') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-02') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-04') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-06') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-08') FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'Dave', DATE ('2022-04-10') FROM SYSIBM.SYSDUMMY1
)
*/
SELECT D.DT, A.Name, A.cul_cnt
FROM D
CROSS JOIN TABLE 
(
  SELECT A.Name, COUNT (1) cul_cnt
  FROM table_A A
  WHERE A.Del_dt <= D.DT
  GROUP BY A.Name
) A
WHERE DT BETWEEN DATE ('2022-04-07') AND DATE ('2022-04-10')
ORDER BY A.Name DESC, D.DT

If you uncomment the commented out block, you may run the statement as is. The result in this case is the same as yours:

DT	NAME	CUL_CNT
2022-04-07	Mark	4
2022-04-08	Mark	4
2022-04-09	Mark	5
2022-04-10	Mark	5
2022-04-07	Dave	3
2022-04-08	Dave	4
2022-04-09	Dave	4
2022-04-10	Dave	5

The same query in Oracle:

WITH
  D (I, DT) AS 
(
  SELECT 1, TO_DATE ('2022-04-10', 'YYYY-MM-DD') FROM DUAL
    UNION ALL
 SELECT I + 1, DT - INTERVAL '1' DAY FROM D WHERE I < 10
)
/*
, table_A (Name, Del_dt) AS 
(
          SELECT 'Mark', TO_DATE ('2022-04-01', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-03', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-05', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-07', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Mark', TO_DATE ('2022-04-09', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-02', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-04', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-06', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-08', 'YYYY-MM-DD') FROM DUAL
UNION ALL SELECT 'Dave', TO_DATE ('2022-04-10', 'YYYY-MM-DD') FROM DUAL
)
*/
SELECT TO_CHAR (D.DT, 'YYYY-MM-DD') DT, A.Name, A.cul_cnt
FROM D
CROSS JOIN LATERAL 
(
  SELECT A.Name, COUNT (1) cul_cnt
  FROM table_A A
  WHERE A.Del_dt <= D.DT
  GROUP BY A.Name
) A
WHERE D.DT BETWEEN TO_DATE ('2022-04-07', 'YYYY-MM-DD') AND TO_DATE ('2022-04-10', 'YYYY-MM-DD')
ORDER BY A.Name DESC, D.DT