I think you should normalise your table with the 12 date fileds.
nr_row maps the 12 records in the normalised table to one row of the original table, nr_d_column is the number of the d column in the original table (d1 is 1, d2 is 2, ..., d12 is 12)
Code:
CREATE TABLE t (
nr_row INTEGER NOT NULL,
nr_d_column SMALLINT NOT NULL,
d DATE NOT NULL,
CONSTRAINT pk_t PRIMARY KEY (nr_row, nr_d_column)
)
SELECT 'd'||CHAR(nr_d_column),
COUNT(*)
FROM t1
WHERE d BETWEEN :h1 AND :h2
ORDER BY nr_row, nr_d_column
In this case, your users will only get a record back when there is at least 1 match.
If they want a record, even when the result is 0, you could use:
Code:
SELECT 'd'||CHAR(nr_d_column),
SUM(CASE WHEN d BETWEEN :h1 AND :h2
THEN 1
ELSE 0
END)
FROM t1
ORDER BY nr_row, nr_d_column
Perhaps someone knows a more elegant way for this last query.
Wim
dBforums
>
SQL Advice
02-28-05, 10:40
Linear Mode
