OraFAQ Forum: SQL & PL/SQL » Loop with Dates

Home » SQL & PL/SQL » SQL & PL/SQL » Loop with Dates (PL/SQL (Windows/Current Oracle vers))

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Loop with Dates [message #682109]

Mon, 05 October 2020 14:00

Adjanti
Messages: 2
Registered: October 2020

Junior Member

Good afternoon,
This is my first post here, so if I am in the wrong spot please let me know.

I have 3 tables that I am trying to join (and the insert query is verified to work as it should, I claim no such things for the rest of the code). What the query does is join the tables then insert them into the ShopDaysCounted table, by fiscal year. Rather than have 10 different sections of repeating code with the dates changed, our DBA has asked me to create a loop and increment the dates to do the inserts.

Loops have never been a strong point for me, and this is my first foray in to a PL/SQL block code. Since I have sensitive information, I have to change the names of the tables/columns, but I do not think that will hinder what you need to see.

Code: [Select all] [Show/ hide]

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO ShopDaysCounted (business_id, shopdate,house_id, count_shopdays)
SELECT tbl1.business_id, tbl2.house_id, to_char(trunc(tbl2.shopdate), ''YYYYMM''), count(distinct tbl2.shopdate) as count_shopdays
FROM db1.table1 tb1, db1.table2 as tbl2, db1.table3 as tbl3
WHERE
 tbl2.receipt_id = tbl1.receipt.id
AND tbl2.payemnt = tbl3.payment
AND table3.code = 1
AND to_char(tbl2.shopdate), ''YYYYMM'') BETWEEN ''201009'' and ''201108''
AND tbl2.authcode in (''A'',''B'',''C'')
AND tbl2.unit_pd > 0

GROUP BY tbl1.business_id, tbl2.house_id, to_char(trunc(tbl2.shopdate), ''YYYYMM'')';
commit;
END;

That is the first run of the query, when it loops through, I would like for it to change the date to '201109' and '201208', the next time '201209' and '201308', and so on until the end of the file. I am not even sure where to begin or how to make it work.

In VBA, I know that I could assign a parameter as a variable and then increment it until EOF. Or simply use a DO UNTIL EOF. I can see this solution clearly in other languages, but my lack of experience with Oracle is not letting me see how I can do this.

If anyone has a resource that speaks specifically to this or can show me a brief example I would appreciate it greatly!

Also, I am open to correction/suggestion as I want to do this correctly and not make our DBA mad with my first real assignment. To be fair, they hired me knowing I was not proficient in PLSQL, as my talents lie in other areas. However, this is one of the areas that fall under my purview and I am excited to learn and want to do well.

Thanks for any help!

Report message to a moderator

Re: Loop with Dates [message #682110 is a reply to message #682109]

Mon, 05 October 2020 14:22

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

1/ You do not need EXECUTE IMMEDIATE as everything is static in your query
2/ Never do in PL/SQL what can be done in SQL
3/ You don't need to loop as you group by month and fiscal year is irrelevant (or your insert statement is incorrect anyway), and fixing the order of the columns (you invert the order of house_id and shopdate between INSERT and SELECT), and assuming shopdate is of DATE datatype in the target table, and assuming the rest is correct:

Code: [Select all] [Show/ hide]

INSERT INTO ShopDaysCounted (business_id, house_id, shopdate, count_shopdays)
SELECT tbl1.business_id, 
       tbl2.house_id, 
       trunc(tbl2.shopdate,'MONTH'), 
       count(distinct tbl2.shopdate) as count_shopdaysa
FROM db1.table1 tb1, db1.table2 as tbl2, db1.table3 as tbl3
WHERE tbl2.receipt_id = tbl1.receipt.id
  AND tbl2.payemnt = tbl3.payment
  AND tbl3.code = 1
  AND tbl2.authcode in ('A','B','C')
  AND tbl2.unit_pd > 0
GROUP BY tbl1.business_id, tbl2.house_id, trunc(tbl2.shopdate,'MONTH')

[Updated on: Mon, 05 October 2020 15:08]

Report message to a moderator

Re: Loop with Dates [message #682111 is a reply to message #682110]

Mon, 05 October 2020 15:07

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But if you want to insert the aggregation of the whole fiscal year with shopdate of ShopDaysCounted the first day of the fiscal year then the statement is:

Code: [Select all] [Show/ hide]

INSERT INTO ShopDaysCounted (business_id, house_id, shopdate, count_shopdays)
SELECT tbl1.business_id, 
       tbl2.house_id, 
       add_months(trunc(add_months(tbl2.shopdate,-8),'YEAR'),8), 
       count(distinct tbl2.shopdate) as count_shopdays
FROM db1.table1 tb1, db1.table2 as tbl2, db1.table3 as tbl3
WHERE tbl2.receipt_id = tbl1.receipt.id
  AND tbl2.payemnt = tbl3.payment
  AND tbl3.code = 1
  AND tbl2.authcode in ('A','B','C')
  AND tbl2.unit_pd > 0
GROUP BY tbl1.business_id, tbl2.house_id, add_months(trunc(add_months(tbl2.shopdate,-8),'YEAR'),8)
/

[Updated on: Mon, 05 October 2020 15:08]

Report message to a moderator

Re: Loop with Dates [message #682137 is a reply to message #682111]

Tue, 06 October 2020 08:51

Adjanti
Messages: 2
Registered: October 2020

Junior Member

Thank you for your responses, I appreciate it.
Oracle version is 19.7.0.0/19c Enterprise

I think the fiscal years might be important as the rows have to be pulled specifically by YYYYMM, based on the other criteria. The tables these records come from have hundreds of millions of records in them and are not normalized, unfortunately.

I need to be sure for each house_id to count the number of shop_ids that fall between those dates, by month/year. The query I wrote above works and has been manually verified with their data files, tables, and our tables.

If you have the time, can you help me understand why the fiscal year is not important?

Thanks again -

Report message to a moderator

Re: Loop with Dates [message #682142 is a reply to message #682137]

Tue, 06 October 2020 10:36

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

If you have the time, can you help me understand why the fiscal year is not important?

Fiscal years do not matter for the result as you group the rows per month which do not depend on fiscal years definition, you just have to specify the first and last month you want, for instance 201909 up to 202008, and so have not to loop.
This is what my first statement gives (excepting that I did not specify the first and last months and so get the result for all months in your tables).

If you want the counts per fiscal year (and no more per month), then you have to use the second statement I posted which group per fiscal year (same remark about first and last).

Report message to a moderator

Previous Topic:	Row Generator Using MODEL Clause, # Repeats Table-Based?
Next Topic:	Join Unique Tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 30 21:56:23 CDT 2025