Sudheer Palaparambil wrote:

>
> Hi,
>
> I have a few records in a table like this
>
> Code entry_date value
> ---- ---------- -------
> 01 01.01.2003 200.00
> 01 15.01.2003 1200.00
> 02 30.01.2003 500.00
> 02 08.02.2003 100.00
> 01 15.02.2003 300.00
> 02 04.03.2003 200.00
> 01 01.03.2003 1000.00
> 02 11.03.2003 500.00
>
> and I want to generate a report like this in
> FB 1.5.
>
> Code January February March
> -----------------------------------------------
> 01 1400.00 300.00 1000.00
> 02 500.00 100.00 700.00
>

This is a cross-tab report and is not supported by SQL.

1. Create a table Summary with fields for Jan, Feb and Mar.

Code
January NUMERIC NULL,
February NUMERIC NULL,
March NUMERIC NULL

2a. January data

INSERT INTO Summary (Code, January)
SELECT Code, Value
FROM TheValueTable
WHERE (entry_date >= 1/1/2003)
AND (entry_date <= 31/1/2003)

2b. February data
2c. March data

3. Now flatten the table:

SELECT Code, SUM(January), SUM(February), SUM(March)
FROM Summary
GROUP BY Code


--
Raymond Kennington
Programming Solutions
TeamW2W (InfoPower)