SQL Statement for generating expired customer

[The Blob]

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?

[--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. 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

[Kevin Frey]

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.

[Kevin Frey]

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?

[Joe Celko]

>> 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!