Click here to Skip to main content
15,355,050 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

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


What I have tried:

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;
Posted
Updated 10-May-22 2:58am

1 solution

Not sure what you mean by "cumulative counts"...

I'd suggest to use WITH clause[^]:

SQL
WITH CTE(Cust_Name, og_date, cul_cnt) AS 
(
  SELECT Name, Del_dt, COUNT(*)
  FROM YourTable
  WHERE Del_dt BETWEEN '2022-04-07' AND '2022-04-10'
  GROUP BY Name, Del_dt
)
SELECT *
FROM CTE
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900