Trouble creating a SQL query
- by JoBu1324
I've been thinking about how to compose this SQL query for a while now, but after thinking about it for a few hours I thought I'd ask the SO community to see if they have any ideas.
Here is a mock up of the relevant portion of the tables:
contracts
id
date
ar (yes/no)
term
payments
contract_id
payment_date
The object of the query is to determine, per month, how many payments we expect, vs how many payments we received.
conditions for expecting a payment
Expected payments begin on contracts.term months after contracts.date, if contracts.ar is "yes". Payments continue to be expected until the month after the first missed payment.
There is one other complication to this: payments might be late, but they need to show up as if they were paid on the date expected.
The data is all there, but I've been having trouble wrapping my head around the SQL query. I am not an SQL guru - I merely have a decent amount of experience handling simpler queries. I'd like to avoid filtering the results in code, if possible - but without your help that may be what I have to do.
Expected Output
Month Expected Payments Received Payments
January 500 450
February 498 478
March 234 211
April 987 789
...
SQL Fiddle
I've created an SQL Fiddle: http://sqlfiddle.com/#!2/a2c3f/2
