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
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.
There's no need for loops, you can utilize an OLAP-function plus RESET WHEN:
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.
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!