sql server programming:
I am having difficulty in designing my SQL. :(

In this setup, an Invoice can have multiple Bills (installment
payments). I would like a query that returns invoices with overdue
bills

a bill is overdue if :: NOT B_Paid and Now() > B_DueDate --- how to
put this in the following '???'

SELECT B_Invoice, ??? As Overdue
FROM T_Bill
GROUP BY B_Invoice


CREATE TABLE T_Bill (
RefNo INTEGER IDENTITY PRIMARY KEY,
B_Invoice INTEGER NOT NULL REFERENCES
T_Invoice,
B_BillDate SMALLDATETIME DEFAULT CURRENT_TIMESTAMP
NOT NULL,
B_DueDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP
+14) NOT NULL,
B_Paid BIT DEFAULT 0 NOT NULL,
B_PaidDate SMALLDATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
B_PaymentInfo VARCHAR(24) DEFAULT '???' NOT NULL
);

Riyaz

Hi
Untested

SELECT B_Invoice, B_DueDate As Overdue
FROM T_Bill
WHERE EXISTS (SELECT * FROM T_Bill TB
WHERE TB.B_Paid >T_Bill.B_DueDate AND GETDATE()> T_Bill.B_DueDate)



[quoted text, click to view]

Your table is not designed properly. Your assembly-style bit flag is
redundant when you have a payment date. IDENTITY cannot be a
relational key by definition and it is redundant given the invoice
number. You never put the table as a prefix on a data element names
because that would change the names from table to table. If the due
date is always 14 days after the billing date, then put compute it in
a VIEW or a query instead of wasting space. This also gives you more
control without having to alter the tables.

CREATE TABLE Billings
(invoice_nbr INTEGER NOT NULL
REFERENCES Invoices (invoice_nbr),
billing_date DATETIME NOT NULL,
PRIMARY KEY (invoice_nbr, billing_date),
paid_date DATETIME, -- null is unpaid
payment_note VARCHAR(24) DEFAULT '???' NOT NULL);

[quoted text, click to view]

SELECT invoice_nbr, CURRENT_TIMESTAMP
FROM Billings
WHERE paid_date IS NULL
AND DATEADD (D, billing_date, 14) >= CURRENT_TIMESTAMP;

You need to get a book on basic data modeling. And one on RDBMS.
What you have is a badly designed punch card file system, with flag,
sequential numbering, etc. .