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