473,394 Members | 1,267 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

SQL Statement for generating expired customer

Hi all,

I need sone help on generating a SQL statement

i had 3 tables now

Customer
Name
Acct No
Address
Phone
Invoice Invoice no
Customer no
Invoice Details Invoice No
Commence Date
Expiry Date
Amount


Every month, the customer will come in to pay for the bills. 1
seperate row is generated for each invoice.

I need to generate a report stating customer who had came in to pay
for the month of march (expiry date=31/3/2004) but still have not make
payment for the month of April (date commence >1/4/2004).

Can anyone help?
Jul 20 '05 #1
4 2419
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Right now, Customers have no relationship to Invoices.
Is this what you meant to post?

CREATE TABLE Customers
(cust_nbr INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(35) NOT NULL,
);

CREATE TABLE Invoices
(invoice_nbr INTEGER NOT NULL PRIMARY KEY,
cust_nbr INTEGER NOT NULL
REFERENCES Customers(cust_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE);

CREATE TABLE InvoiceDetails
(invoice_nbr INTEGER NOT NULL
REFERENCES Invoices (invoice_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
commence_date DATETIME NOT NULL,
expiry_date DATETIME NOT NULL,
CHECK (commence_date < expiry_date),
amount DECIMAL (12,4) DEFAULT 0.00 NOT NULL,
PRIMARY KEY (invoice_nbr, commence_date));
customers who have paid for the month of march, but still have not

make payment for the month of April <<

SELECT C1.cust_nbr, C1.name
FROM Customers AS C1,
Invoices AS I1,
InvoiceDetails AS D1,
InvoiceDetails AS D1,
InvoiceDetails AS D2
WHERE C1.cust_nbr = I1.invoice_nbr
AND I1.invoice_nbr = D1.invoice_nbr
AND I1.invoice_nbr = D2.invoice_nbr
AND D1.commerce_date = '2004-03-01'
AND D2.commerce_date = '2004-04-01'
AND D1.amount > 0.00
AND D2.amount = 0.00
Jul 20 '05 #2
My first comment is that based on what you have described, the Invoice
table is redundant and should be combined with the Invoice Details
tables.

I post some [rough] DDL below:

create table Customer
(
C_Code varchar( 10 ),
C_Name varchar( 30 ) not null,
)

create table Invoice
(
C_Invoice_No varchar( 10 ) not null,
C_Customer_Code varchar( 10 ) not null,
C_Commences datetime not null,
C_Expires datetime not null,
C_Amount int
)

insert Customer( C_Code, C_Name ) values ( '001', 'Kevin' )
insert Customer( C_Code, C_Name ) values ( '002', 'Paul' )
insert Customer( C_Code, C_Name ) values ( '003', 'Roger' )

insert Invoice( C_Invoice_No, C_Customer_Code, C_Commences, C_Expires
)
values ( '1', '001', '01-March-2004', '31-March-2004' )
insert Invoice( C_Invoice_No, C_Customer_Code, C_Commences, C_Expires
)
values ( '2', '002', '01-March-2004', '31-March-2004' )
insert Invoice( C_Invoice_No, C_Customer_Code, C_Commences, C_Expires
)
values ( '3', '003', '01-February-2004', '28-February-2004' )

The query below assumes that:

a. The billing period is the same for all customers. That is, everyone
is billed from 1st of month to 31st (or last day) of the month. The
SQL below won't work unless this fact holds true.
b. The billing period has no breaks in it. That is, if the last day of
a billing period is 31st March, the commence date of the next period
is 1st April, not 2nd April etc.

select * from Customer
where C_Code in (
select distinct C_Customer_Code from Invoice
group by C_Customer_Code
having max( C_Expires ) = dateadd( dd, -1, '01-April-2004' )
)

Effectively '01-April-2004' is the date of your next "Commence" period
so this query simply looks for customers having an invoice with an
expiry date which matches the expiry date of the previous billing
period.

At the end of the day, however, you can vary the having clause to get
the type of result you need.
Jul 20 '05 #3
I suspect that I have misunderstood the requirement and resultant
answer in my original post. I assumed that there was no invoice
details entry yet. Guess that's what happens without DDL and sample
data...

Logistically, however, I wonder why a system would create an "Invoice
Details" record with a zero amount, so presumably there must be an
"Amount" column and an "Amount Paid" column?
Jul 20 '05 #4
>> I wonder why a system would create an "Invoice
Details" record with a zero amount, so presumably there must be an
"Amount" column and an "Amount Paid" column? <<

I agree; my mental model was a payment coupon book with one coupon per
month and the total amount due in the Invoices table. But even that is
awkward.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: LRW | last post by:
I'm trying to get the difference between two dates, formatted in: Time elapsed: X years, X months, X days but I can't seem to get it right. Here's my last attempt: <?php $oldtime =...
4
by: ecPunk | last post by:
Hi, We have a web application where we want a user to be able to change his/her password if the password has expired but we are unable to do this with ASP (at the moment) because we can't log...
1
by: The Blob | last post by:
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...
2
by: Chris Langston | last post by:
I have a Web Server running IIS 5 or 6 on Windows 2K and Windows 2003 Server that is experiencing strange shutdown problems. We are using ASP.NET v1.1 and our application is written in VB.NET ...
4
by: vvenk | last post by:
Hello: I am working with VB.Net and Oracle 10g database on a Windows 20043 server. I added a OleDBConnection to a form, configured the connection strings. And, I added a OleDBDataAdapter and...
4
by: rbronco28 | last post by:
I am pretty new at doing web development. I can make simple pages, but recently received a request from a small limo business I do web development for. We are currently working on a reservation...
6
by: Gibble | last post by:
Hi, We have a javascript on a page that calls a .NET web service. Oddly, in our web servers event logs the following errors kept repeating every minute (that made sense since the web service...
17
by: sharsy | last post by:
Hello guys, I would like some help in generating query criteria that will identify credit cards that have expired on an access database. The specific Field is formatted with a Data Type of...
28
by: DanicaDear | last post by:
I have set up a form to contain customer info. From that form, I have a button "Show Order" that links it to a Order Number form. That order number form contains a subform showing order details...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.