On 29 Jul 2003 at 5:37, Sudheer Palaparambil wrote:

>I have a few records in a table like this
>
>Code entry_date value

Assume that you have a list of codes in another table...

>and I want to generate a report like this in
>FB 1.5.
>
>Code January February March

Select a.Code,
(Select sum(t.value) from thistable t where t.code=a.code and
EXTRACT(MONTH from entry_date)=1) as Jan_sum,
.......... and so on ...........
from another_table a order by a

You'll fail if there are values from more than one year.

Another solution is an SP with smth like this inside:

saved_year = null;
saved_code = null;
for select code, sum(value),
EXTRACT(YEAR from entry_date),
EXTRACT(MONTH from entry_date) from thistable
group by code,
EXTRACT(YEAR from entry_date),
EXTRACT(MONTH from entry_date)
into :code, :temp_var, :a_year, :a_month do
begin
if (saved_year <> a_year or saved_code<>code) then // new year or
code
begin
SUSPEND;
jan_sum = 0;
feb_sum = 0;
.......
end;
saved_year = a_year;
saved_code = code;
if (a_month=1) then
jan_sum = temp_var;
else
if (a_month=2) then
feb_sum = temp_var;
......... and so on .........
end;
suspend;

SY, Dimitry Sibiryakov.