SQL query for Report Design in FastReport4 for Delphi

Question

I develop an Account program with Delphi7 and design report with Fastreport4 I have a Bill tables that has 6 columns (BillID, BillDate, BillDesc, BillPrice, DebitID, CreditID) that DebitID and CreditID is integer value and in relationship with Account table that hase two columns(AccID, AccName). I want create a report with FastReport in Delphi7 that has this result with these columns:

Report Name : Balance for Mr.x

BillID , BillDate ,  BillDesc    , DebitPrice , CreditPrice , Remain
-----------------------------------------------------------------
1      , 2012/01/22, Sample Desc1, 100USD     , 0           , -100USD
1      , 2012/01/22, Sample Desc2, 0          , 100USD      ,  0USD

I think that if I can create a SQL query with above result it's easy for creating report. if u have any idea, pls let me know?

Answer 1

select BillID , BillDate ,BillPrice,  BillDesc, b.accid, c.accid
from a Bill 
left outer join account b on a.DebitID = b.AccID
left outer join account c on a.CreditID = c.AccID

then, you must evaluate "on before print event" of your masterdata/detaildata band.

if (b.accid <> '') then
  DebitPrice := BillPrice
else
  CreditPrice := BillPrice

Note : DebitPrice, CreditPrice are global variable