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

so what would be the output you expect? January: 12 payments expected, 10 payments received? — Horen
– Horen, Commented Oct 19, 2012 at 19:17
@Horen That is exactly the output I'm looking for (except in table format) — JoBu1324
– JoBu1324, Commented Oct 19, 2012 at 19:19
@Horen my previous comment was wrong, actually - I misunderstood you. I'm going to update the question with the expected output — JoBu1324
– JoBu1324, Commented Oct 19, 2012 at 19:22
for the "expected payments part" try something like this: select count(*), DATE_FORMAT(DATE_ADD(date, INTERVAL term MONTH), "%M") as month from contracts where ar='yes' group by month — Horen
– Horen, Commented Oct 19, 2012 at 19:23
Ok, that would give me the first date that payments are expected. How would that help me in subsequent months, though? — JoBu1324
– JoBu1324, Commented Oct 19, 2012 at 19:32

Vinod Vishwanath · Accepted Answer · 2012-10-19 19:18:44Z

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.

answered Oct 19, 2012 at 19:18

Vinod Vishwanath

5,9212 gold badges29 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

JoBu1324 Over a year ago

payments are received once a month though, and they should only show up in received payments for the month they occurred on It doesn't look like you're taking that into account - am I wrong?

Barmar Over a year ago

It's more complicated than that. If a payment is expected in January, and has a term of 2 months, whether it's expected in February depends on whether it was received in January or not. And the day of month complicates it further, since it will be expected in March if the contract date is Jan 15.

JoBu1324 Over a year ago

I don't see how the first part of the query will contain more than just the first expected payment - say it happened in Jan. If that payment gets made, a payment will also be expected in Feb.

JoBu1324 Over a year ago

I also added another requirement to the question - late payments need to be handled as if they occurred on time.

Collectives™ on Stack Overflow

Trouble creating a SQL query

contracts

payments

Expected Output

SQL Fiddle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

contracts

payments

Expected Output

SQL Fiddle

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related