All Forums Database
Rhino_373 2 posts Joined 07/16
06 Jul 2016
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.

dnoeth 4628 posts Joined 11/04
07 Jul 2016

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.

Dieter

Rhino_373 2 posts Joined 07/16
07 Jul 2016

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!

You must sign in to leave a comment.