Prev: Help with Query
Next: Person's age based on DOB
From: --CELKO-- on 27 May 2010 22:42
I could not figure out what a NULL donation amount would mean; was
that supposed to be NOT NULL? Why did you use CHAR(4) for a numeric
value? I fleshed out the skeleton with a little meat and cut out the
rot. Also, never use MONEY -- it has math problems and makes you look
like a hillbilly.

CREATE TABLE Donations
(member_id INTEGER NOT NULL
REFERENCES Membership (member_id),
donation_fiscal_yr INTEGER NOT NULL
CHECK (fiscal_yr BETWEEN 2005 AND 2010 ),
donation_amt DECIMAL(10,2) DEFAULT 0.00 NOT NULL
CHECK (donation_amt >= 0.00),
PRIMARY KEY (member_id, fiscal_yr));

Since you have a fixed range, you use a look up table instead of
computations:

CREATE TABLE ThreeYearRanges
(donation_period INTEGER NOT NULL PRIMARY KEY,
start_fiscal_yr INTEGER NOT NULL,
end_fiscal_yr INTEGER NOT NULL,
CHECK (start_fiscal_yr + 2 = end_fiscal_yr)
);

INSERT INTO ThreeYearRanges
VALUES (1, 2005, 2007),
(2, 2006, 2008),
(3, 2007, 2009),
(4, 2008, 2010);

This is soooo short, I would put it in a CTE, but you get the point.

>> I want to do is pull [sic: RDBMS is not punch cards] records [sic: rows are not records] based on if they gave $1000 or more for 3 or more consecutive years. <<

No wonder you used CHAR(4); you still think in punch cards! Selecting
a set is not like pulling cards one at a time.

SELECT DISTINCT D.member_id
FROM Donations AS D, ThreeYearRanges AS R
WHERE D.donation_fiscal_yr BETWEEN R.start_fiscal_yr AND
R.end_fiscal_yr
AND D.donation_amt >= 1000.00
GROUP BY D.member_id, R.donation_period
HAVING COUNT(DISTINCT start_fiscal_yr) >= 3;

You can play with the Range table to do a LOT of other things, but
that is another topic.
First  |  Prev  | 
Pages: 1 2
Prev: Help with Query
Next: Person's age based on DOB