Hi all,
I have a problem trying to generate the u/m list of customer.
I am trying to generate a list of customer whoes last commence date is
jan 04 to current.
It is part of a billing system which the customer come in and pay for
their season parking in carpark. They can pay for various period
shortest being 1 week.
so i will have customer paying for 1 week, 1 month, 2 months or even 1
year. Everytime a customer come in to pay, a new line at the incvoice
will be generated.
My DB structure is as followed
Customer Table
-Cust_Acc_No ----> (Primary Key)
-Customer Name
-Customer Address
Invoice
Cust_acc_no-- Link to customer table
Invoice_no -- primary key
invoice details
invoice_no -- link to invoice table
commence_date
expiry_date
amount_paid
if i do a
select * from customer a, invoice b, invoice_details c
where a.cust_acc_no=b.cust_acc_no and
b.invoice_no=c.invoice_no and
c.commence_date>1/1/04
it doesnt works as it will show
john, 1/1/04 - 31/1/04
john, 1/2/04 - 29/1/04
i do not want repetitive customer number just the latest commence
date.
can anyone help?
thanks