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