>Thanks! I will most likely go with the reference table(s) option, but I
>will test both.
One alternative that I don't think have been mentioned, Cornie, is EXECUTE
BLOCK, which is simpler than a recursive CTE without requiring any extra table:
execute block returns (yearno integer, dayno integer, "SUM" integer)
as
declare variable d date;
declare variable dMax date;
begin
select MIN(DT), MAX(DT)
from CLIENT_INVOICES
into :d, :dMax; /*or use fixed FROM and TO dates*/
while (d <= dMax) do
begin
yearno = EXTRACT(YEAR from d);
dayno = EXTRACT(YEARDAY from d);
select coalesce(sum(DUE), 0)
from CLIENT_INVOICES
where DT = :d
into :"SUM";
suspend;
d=d+1;
end
end;
This works on recent Firebird versions (though I would recommend using a
different name than SUM).
HTH,
Set
