Em 1/8/2012 08:23, mahdoom_a escreveu:
> Dear all,
>
> I need your help to create this report
>
> AQNO Jan Feb Mar Apr May Jun Jul......Dec. Total_paid
> ---- --- --- --- --- --- --- --- --- ----------
> 1001 2000 2000 2000 2000 8000
> CHQNO 214 215 216 217
>
>
> it will fill the amount(PAYMNT) in each month (PAYDATE) and its cheque number
> (CHQNO)
>
>
>
>
>
>
> table structure is
>
>
> CREATE TABLE RNTSTAT (
> STATNO INTEGER NOT NULL,
> RNTDUE DOUBLE PRECISION,
> DUEDATE DATE,
> PAYMNT DOUBLE PRECISION,
> PAYTYP VARCHAR(30),
> CHQNO VARCHAR(30),
> PAYDATE DATE,
> INVOICE VARCHAR(30),
> EXPNS DOUBLE PRECISION,
> XPNSDATE DATE,
> XPENSTYP VARCHAR(30),
> MENAINTYP VARCHAR(30),
> CHRGVAL DOUBLE PRECISION,
> CHRGTYP VARCHAR(30),
> RNTNO INTEGER,
> EXNSNO INTEGER,
> AQNO INTEGER,
> PANNO INTEGER,
> CONTNO VARCHAR(30),
> CHRGPER FLOAT,
> FROM_DATE DATE,
> TO_DATE DATE,
> AWQAFNO NEW_DOMAIN
> );
>
>
> any hint will be helpful.
>
> thanks & regards,
>
> AHMAD
>
>
>
>
I would usually do this on the client-side, it's called cross-tab report.
You could implement it on the server-side using sub-queries, in-line
views or CTE's.
I would post a sample query that I used on a talk I did last July on
Firebird Developers Day to show how to use CTE's
with
ProdutoVendaAnual(Ano, ProdutoID, TotalVendido) as
(select
extract(year from DataEntrega) Ano, ProdutoID,
Sum(QuantidadeOriginal)
from
PedidoVendaItem
group by
1, 2)
select
P.Codigo, P.Descricao,
V_2006.TotalVendido, V_2007.TotalVendido,
V_2008.TotalVendido, V_2009.TotalVendido
from
Produto P left join
ProdutoVendaAnual V_2006 on (V_2006.ProdutoID = P.ProdutoID and
V_2006.Ano = 2006) left join
ProdutoVendaAnual V_2007 on (V_2007.ProdutoID = P.ProdutoID and
V_2007.Ano = 2007) left join
ProdutoVendaAnual V_2008 on (V_2008.ProdutoID = P.ProdutoID and
V_2008.Ano = 2008) left join
ProdutoVendaAnual V_2009 on (V_2009.ProdutoID = P.ProdutoID and
V_2009.Ano = 2009)
HTH
