471,873 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,873 software developers and data experts.

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 2779
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 Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.