rows unbounded preceding

Database

Re: rows unbounded preceding

Hello,

 

I'm a bit stuck Smiley Sad... I have 3 tables (a, b, c).  Table a contains the base population that I would not like to limit.  The 2 fields from table a are 'ID' and 'Start_Date'.  I would like to join table b on 'ID' in order to get the 'Amt_Required'.  I would like to join table c on ID as well from table a.  Table c contains the full payment transaction history for each ID, the additional fields in table c are 'Total_Recd' and 'Recd_Date'.  I would like to add two columns that calculates the sum of the received amounts only acknowledging the 'Recd_Date' that is >= the 'Start_Date' from table a.  I'd like to show the date in which the amount met was fulfilled and the accumulated amount received.

 

For example if I have 5 payments that were received since the Start_Date for a particular ID, and the sum met the 'Amt_Required' on the 4th payment received.  I'd like to see that 4th payment date and accumulated amount.

 

 

I'm getting close but not quite what I need.

Highlighted
Junior Contributor

Re: rows unbounded preceding

I'm getting close but not quite what I need.

Show what you did :-)

 

Can you add some example data and expected result?

Re: rows unbounded preceding

Here is a sample of the data...

 

table a

ID       |  Start_Date

12345 |  1/19/18

 

table b

ID       |  Amt_Required

12345 |  500.00   

 

table c

ID        |   Tran_ID  |  Total_Recd  |  Recd_Date

12345 |   173         |  300.00          |  1/1/18

12345 |    173        |  100.00          | 1/23/18

12345 |    173        |  125.00          | 2/5/18

12345 |    173        |  300.00          | 3/1/18

12345 |     173       |  100.00          | 3/15/18

 

And I would want to see the following...

Final

ID       |   Tran_ID | Total_Recd | Recd_Date

12345 |    173       |  525.00       | 3/1/18

 

I would only want to see when the amount required from table b was met or passed and on whch received date that happened.  In this case the amount received surpassed the 500.00 required amount on 3/1/18.  I would not care about anything received after that amount was reached and I would also not care about any amount received before the start date from table a. Also, table c has different Tran_ID's but I'm only concerned with 173.  I didn't save the last code I had because I keep scraping it out of frustration and starting from scratch lol.

 

Junior Contributor

Re: rows unbounded preceding

Untested:

SELECT *
FROM 
 (
   SELECT c.ID, c.Tran_ID,
      Sum(c.Total_Recd)
      Over (PARTITION BY c.id
            ORDER BY c.Recd_Date 
            ROWS Unbounded Preceding) AS sumAmt,
      c.Recd_Date
   FROM c
   JOIN a
     ON a.ID = c.id
     -- I would also not care about any amount received before the start date from table a
    AND c.Recd_Date >= a.Start_Date 
   JOIN b
     ON c.id = b.id
   WHERE c.Tran_ID = 173 -- I don't know if this is hard-coded
    --  amount required from table b was met or passed
   QUALIFY sumAmt >= b. Amt_Required
 ) AS dt
QUALIFY
   -- I would not care about anything received after that amount was reached
   Row_Number()
   Over (PARTITION BY c.id
         ORDER BY Recd_Date) = 1

Would have been easier with Create Table and Inserts...