[firebase-br] Dados cruzados. Como fazer?

[marcelo - teflamar]

segue um exemplo com clientdataset

estrutura da tabela:
cod,cliente, data,     status,tipo
1   1       01/09/2004 ab     1
2   1       01/09/2004 ac     1
3   2       02/09/2004 aj     2
4   2       02/09/2004 ab     2
5   3       03/09/2004 ac     2
6   3       03/09/2004 ac     3
7   3       03/09/2004 aj     4
8   1       03/09/2004 dm     2
9   1       01/09/2004 dm     1
10  1       01/09/2004 dm     1

tipo  |1 | 2 |3 | 4 |total
------+--+---+--+---+-----+
ab    |1 | 1 |0 | 0 |  2
ac    |1 | 1 |1 | 0 |  3
aj    |0 | 1 |0 | 1 |  2
dm    |2 | 1 |0 | 0 |  3
total |4 | 4 |1 | 1 | 10

objetos:
1 clientdataset
1 datasource
1 database
1 transaction
3 ibquery
query1 - seleciona as colunas distintas
query2 - seleciona as linhas distintas
query3 - é a query de trabalho,
* as querys podem ser substituidas
por isql, mais rápidas.
//--------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
clientdataset1.Close;
clientdataset1.fielddefs.Clear;
ibdatabase.connected:=true;
ibtransaction1.starttransaction;
ibquery1.sql.text:='select distinct status from tabela where data between :datai and :dataf and cliente=:cliente';
ibquery1.parambyname('cliente').asinteger:=strtoint(edcli.text);
ibquery1.parambyname('datai').asdatetime:=strtodate(eddatai.text);
ibquery1.parambyname('dataf').asdatetime:=strtodate(eddataf.text);
ibquery1.open;
clientdataset1.FieldDefs.Add('tipo',ftstring,10,true);
clientdataset1.IndexFieldNames:='tipo';
while not ibquery1.eof do
begin
clientdataset1.FieldDefs.Add(ibquery1.fieldbyname('status').asstring,ftinteger,0,false);
ibquery1.next;
end;
clientdataset1.FieldDefs.Add('total',ftinteger,0,false);
clientdataset1.CreateDataSet;
clientdataset1.Append;
clientdataset1.fieldbyname('tipo').asstring:='ZZtotal';
ibquery2.close;
ibquery2.sql.text:='select distinct tipo from tabela where data between :datai and :dataf and cliente=:cliente';
ibquery2.parambyname('cliente').asinteger:=strtoint(edcli.text);
ibquery2.parambyname('datai').asdatetime:=strtodate(eddatai.text);
ibquery2.parambyname('dataf').asdatetime:=strtodate(eddataf.text);
ibquery2.open;
while not ibquery2.eof do
begin
clientdataset1.Append;
clientdataset1.fieldbyname('tipo').asstring:=ibquery2.fieldbyname('tipo').asstring;
ibquery1.First;
while not ibquery1.Eof do
begin
ibquery3.Close;
ibquery3.SQL.text:='select count(*) as qtd from tabela where tipo=:tipo and status=:status and cliente=:cliente and data between :datai and :dataf';
ibquery3.parambyname('cliente').asinteger:=strtoint(edcli.text);
ibquery3.parambyname('datai').asdatetime:=strtodate(eddatai.text);
ibquery3.parambyname('dataf').asdatetime:=strtodate(eddataf.text);
ibquery3.parambyname('tipo').asstring:=ibquery2.fieldbyname('tipo').asstring;
ibquery3.parambyname('status').asstring:=ibquery1.fieldbyname('status').asstring;
ibquery3.open;
clientdataset1.findkey([ibquery2.fieldbyname('tipo').asstring]);
clientdataset1.edit;
clientdataset1.fieldbyname(ibquery1.fieldbyname('status').asstring).asinteger:=ibquery3.fieldbyname('qtd').asinteger;
clientdataset1.fieldbyname('total').asinteger:=clientdataset1.fieldbyname('total').asinteger + ibquery3.fieldbyname('qtd').asinteger;
clientdataset1.findkey(['ZZtotal']);
clientdataset1.Edit;
clientdataset1.fieldbyname(ibquery1.fieldbyname('status').asstring).asinteger:=
clientdataset1.fieldbyname(ibquery1.fieldbyname('status').asstring).asinteger +
ibquery3.fieldbyname('qtd').asinteger;
clientdataset1.fieldbyname('total').asinteger:=clientdataset1.fieldbyname('total').asinteger + ibquery3.fieldbyname('qtd').asinteger;
ibquery1.next;
end;
ibquery2.next;
end;
ibtransaction1.Commit;
end;
//--- marcelo luiz stefaniak