Possible loop usage in SQL?

Database

Possible loop usage in SQL?

I have to tables that have two columns. Both have a date and an amount of money. The first table generally has every day of the month. The second table often skips days such as weekends and possibly other days. I want to compare the two tables but when days are missing, I need to sum up the previous days

Table 1

06/01 $100

06/02 $75

06/03 $125

06/04 $50

Table 2

06/01 $100

06/03 $200

06/04 $50

Comparitively the two tables have the same balance but if I were to innter join on date I would get a mismatch on balance and would lose 06/02. 

Is there a way to create a loop that will loop through the min and max days and when it detects a gap in dates it selects the sum of the first table between that gap and when there is no gap it just selects the information between that 1 day? In this example I want to select all results from table 2 and show that by day it equals table 1 (with the exception of sometimes multiple days from table 1 were used). 

I understand I can likely just do this by month but when something doesn't equal by month I do need to break it down by day that is why I'm trying to compare by day.

2 REPLIES
Senior Apprentice

Re: Possible loop usage in SQL?

There's no need for loops, you can utilize an OLAP-function plus RESET WHEN:

SELECT ...
SUM(t1.amt)
OVER (ORDER BY t1.dt DESC
RESET WHEN t2.dt IS NOT NULL)
FROM table1 AS t1
LEFT JOIN table2 AS t2
ON t1.dt = t2.dt
QUALIFY t2.dt IS NOT NULL

This returns the sum of all previous NULL rows up to the next non-NULL row, resulting in two STAT-steps in Explain.

Depending on your actual needs this might be further simplified to a single STAT-step.

Re: Possible loop usage in SQL?

Dieter, you've been very helpful. I've been able to modify this to use a subquery for one of the tables and tested it in a number of situations. I appreciate your assistance!