ok, this is what I have come up with and it seems to work after a little testing. I can't really say that I understand much of this, except that the query for the compare appears to rely upon the data table being joined to itself so that it can compare one time period to another...does that sound about right?
I hope you all don't mind if I have more questions about this, going forward 'cause I suspect that I will.
Thanks to all for all the help, v nOID
CREATE TABLE Name_Symbol (dsymbol CHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE)
CREATE TABLE Industry (dindustry CHAR(30) PRIMARY KEY)
CREATE TABLE Sector (dsector CHAR(30) PRIMARY KEY)
CREATE TABLE Hyperlinks (dyahoo_main CHAR(100) PRIMARY KEY, yahoo_main VARCHAR (100) , msn_10Qs VARCHAR(100) NOT NULL UNIQUE, msn_events VARCHAR(100) NOT NULL UNIQUE, multex_events VARCHAR(100) NOT NULL UNIQUE)
CREATE TABLE Data_03 (cash INTEGER, cash_flow INTEGER, cash_ratio DECIMAL, current_assets INTEGER, current_liabilities INTEGER, debt_long_term INTEGER, dso INTEGER, eps DECIMAL, income INTEGER, inventories INTEGER, margin_net DECIMAL, margin_net_con_ops DECIMAL, margin_operating DECIMAL, pe INTEGER, ps DECIMAL, quick_ratio DECIMAL, revenue INTEGER, roa INTEGER, roe INTEGER, roic INTEGER, shares_out INTEGER, tax_rate DECIMAL,
dsymbol CHAR(10) REFERENCES Name_Symbol (dsymbol),
dyear INTEGER CHECK (dyear BETWEEN 1990 AND 2100),
dquarter INTEGER CHECK(dquarter BETWEEN 1 AND 4),
PRIMARY KEY (dsymbol, dyear, dquarter))
INSERT INTO Name_Symbol VALUES ('A', 'Agilent')
INSERT INTO Name_Symbol VALUES ('B', 'Brillo')
INSERT INTO Industry VALUES ('Semiconductors')
INSERT INTO Industry VALUES ('Advertising')
INSERT INTO Sector VALUES ('Technology')
INSERT INTO Sector VALUES ('Cyclicals')
INSERT INTO Hyperlinks VALUES ('
http://www.yahoo.com/A', '
http://www.yahoo.com/A', '
http://www.microsoft.com/A', '
http://www.microsoft.com/A', 'http://www. multex.com/A')
INSERT INTO Hyperlinks VALUES ('
http://www.yahoo.com/B', '
http://www.yahoo.com/B', '
http://www.microsoft.com/B', '
http://www.microsoft.com/B', '
http://www.multex.com/B')
INSERT INTO Data_03 VALUES (12 ,45,1.2,34,57,45,35,0.78,45,567,4.5,3.4,56,7,8.9,1.5,200,34,26,32,617,3.4,'A', 2003, 1)
INSERT INTO Data_03 VALUES (13 ,55,1.2,34,57,45,35,0.78,45,567,4.5,3.4,56,7,8.9,1.5,700,34,26,32,617,3.4,'A', 2003, 2)
INSERT INTO Data_03 VALUES (11 ,25,1.2,34,57,45,35,0.78,45,267,4.5,3.4,56,7,6.9,2.5,600,34,66,32,817,6.4,'A', 2003, 3)
INSERT INTO Data_03 VALUES (12 ,45,1.2,34,27,65,25,0.78,65,567,4.5,3.4,66,7,8.9,1.5,700,34,26,32,617,3.8,'A', 2003, 4)
INSERT INTO Data_03 VALUES (13 ,55,1.2,34,57,45,35,0.78,45,267,2.5,3.4,56,7,8.9,1.5,700,34,26,32,817,3.4,'B', 2004, 1)
INSERT INTO Data_03 VALUES (14 ,45,1.2,34,57,45,35,0.78,45,567,4.5,3.4,56,7,8.9,2.5,600,34,66,32,617,6.4,'B', 2004, 2)
INSERT INTO Data_03 VALUES (17 ,65,1.2,34,57,65,35,0.78,45,567,2.5,3.4,66,7,6.9,1.5,700,34,26,32,617,3.4,'B', 2004, 3)
INSERT INTO Data_03 VALUES (12 ,45,1.2,34,27,45,25,0.78,25,267,4.5,3.4,56,7,8.9,2.5,200,34,26,32,617,3.8,'B', 2004, 4)
SELECT cash, N.name, dyear, dquarter
FROM Data_03 AS D
JOIN Name_Symbol AS N
ON D.dsymbol = N.dsymbol
WHERE dquarter BETWEEN 1 AND 3
AND NOT EXISTS
(SELECT *
FROM Data_03 AS D1
JOIN Data_03 AS D2
ON D1.dsymbol = D2.dsymbol
AND D1.dyear = D2.dyear
AND D1.dquarter = D2.dquarter-1
AND D2.dquarter BETWEEN 2 AND 3
AND D1.cash >= D2.cash
