469,081 Members | 1,892 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,081 developers. It's quick & easy.

SQL Statement to generate customer list whose last payment date is jan 04

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
Jul 20 '05 #1
1 2532
Why did you start a separate thread from the one entitled "SQL
Statement for generating expired customer" ?

A number of people (myself included) have already provided help there
for you.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

37 posts views Thread by Steven Bethard | last post: by
18 posts views Thread by Steven Bethard | last post: by
6 posts views Thread by Twobridge | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.