Hi,

What is the best (most efficient) way to get number of distinct values
per day in date range. Ie, I have a table like

CREATE TABLE TAB_Test (
ID INTEGER,
X INTEGER,
Alates DATE NOT NULL, /* start date */
Kuni DATE /* end date, might be "open" */
);

with data like

INSERT INTO TAB_Test(ID, X, Alates, Kuni) VALUES(1, 1, '2000-01-01',
'2000-12-31');
INSERT INTO TAB_Test(ID, X, Alates, Kuni) VALUES(2, 2, '2001-01-01',
'2001-12-31');
INSERT INTO TAB_Test(ID, X, Alates, Kuni) VALUES(3, 2, '2001-01-01',
'2002-12-31');
INSERT INTO TAB_Test(ID, X, Alates, Kuni) VALUES(4, 3, '2002-01-01',
'2002-12-31');
INSERT INTO TAB_Test(ID, X, Alates, Kuni) VALUES(5, 4, '2002-01-01',
NULL);

and I want to get the maximum number of distinct X values at any day
within given date range. Date which happens to have this maximum
number of distinct values would be bonus (in case serveral dates share
maximum any of them would do).

For example, query for date range 2000-02-01 .. 2001-12-01 should
return 1 as only records with ID = 1, 2 and 3 do match the date range,
but 2 and 3 do have same X value and 1 don't overlap with either of
them so at any given day there is only one distinct value for X.

For query for 2002-06-01 .. NULL (end date is open) answer is 3 as
records with ID = 3, 4 and 5 are in given date range and all do have
different X. 2002-06-01 would be the first date when there is 3
different values for X, days before it and after 2002-12-31 would have
less distinct X values.

Is there some better way than brute force approach?


TIA
ain