Trouble creating a SQL query

Question

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

Answer 1

Without writing up the query, I can give you the general idea: In the contracts table, cast date + term in months, and group the result set by months where ar = 'YES'. This gives you the expected payments.

With the second table, cast payment_date in months and group by months for the number of received payments.

You can then join these two sub-results on month to get both pieces of information in one result set.