|
|
Posted by --CELKO-- on 04/13/06 00:11
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You talk about "autonumbers", which are non-relational. You use
non-ISO-8601 date format in your vague narratives.. You have data
element names like "foobar_type_id" -- is it a type or an identifier,
since it CANNOT EVER BE BOTH!!! Do you know that DATE is both too
vague and a reserved word??
Let's assume that you are selling squid on line Maybe you meant this?
CREATE TABLE SquidSales
(squid_wgt DECIMAL (10,2) NOT NULL
CHECK (squid_wgt > 0.0),
squid_size INTEGER DEFAULT 1 NOT NULL
CHECK (squid-size IN (1,2, 3)),
stock_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL PRIMARY KEY);;
>> I want to query this data to produce a data structure where each row has a date (I will get this from a temp table of all dates in my desired range [Google "Calendar Table"] ), then the SUM of any rows containing that date, with each of the three product types being a column. <<
Why are you using temp tables?
SELECT stock_date,
SUM(CASE WHEN squid_size = 1 THEN squid_wgt ELSE 0.0 END) AS
size_1,
SUM(CASE WHEN squid_size = 2 THEN squid_wgt ELSE 0.0 END) AS size_2
,
SUM(CASE WHEN squid_size = 3 THEN squid_wgt
ELSE 0.0 END) AS size_3]
FROM SquidSale
GROUP BY stock_date,;
Navigation:
[Reply to this message]
|