Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automatically generate rows

Status
Not open for further replies.

jackdoe77

Programmer
Joined
Aug 19, 2010
Messages
1
Location
ID
Hi,
Could someone assist me how to automatically generate rows.
I have 2 tables :
Table1 : id, cust_name, period, date_due, amount
Table2 : id, subid, seq, date_due, date_pay, amount_paid

Table1 record : 1, jack, 12, 15/05/2010, 500
Table2 record:
1, 1, 1, 15/05/2010, 27/05/2010, 500
1, 2, 2, 15/06/2010, 30/06/2010, 300
1, 3, 2, 15/06/2010, 05/07/2010, 200
1, 4, 3, 15/07/2010, 01/08/2010, 300

I need to retrieve records that payment is due per today.
jack, 15/05/2010, 0
jack, 15/06/2010, 0
jack, 15/07/2010, 200 (500 - 300)
jack, 15/08/2010, 500 -> due payment (no record on Table2)

My SQL would be like this :
SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table2 b ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

My problem is, I can't generate the last row since there is no payment in Table2 for 15/08/2010 due date. How can I generate this record on-the-fly (without creating the record in the table). It's only for reporting purpose.

Thanks in advance.
 
You could create a 3rd table with all the expected payments then UNION it to the old query...


SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table2 b
ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

union

SELECT a.cust_name, b.date_due, due = a.amount - sum(amount_paid)
FROM table1 a
LEFT JOIN table3 b
ON b.id = a.id
GROUP BY a.cust_name, b.date_due, a.amount

That should work..

Simi
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top