Em 1/8/2012 08:23, mahdoom_a escreveu:
Dear all,
I need your help to create this report
AQNOJan Feb Mar Apr May Jun Jul..Dec. Total_paid
--- --- --- --- --- --- --- -----
10012000 2000 2000 2000 8000
CHQNO 214 215216 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,
DUEDATEDATE,
PAYMNT DOUBLE PRECISION,
PAYTYP VARCHAR(30),
CHQNO VARCHAR(30),
PAYDATEDATE,
INVOICEVARCHAR(30),
EXPNS DOUBLE PRECISION,
XPNSDATE DATE,
XPENSTYP VARCHAR(30),
MENAINTYP VARCHAR(30),
CHRGVALDOUBLE PRECISION,
CHRGTYPVARCHAR(30),
RNTNO INTEGER,
EXNSNO INTEGER,
AQNO INTEGER,
PANNO INTEGER,
CONTNO VARCHAR(30),
CHRGPERFLOAT,
FROM_DATE DATE,
TO_DATEDATE,
AWQAFNONEW_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
