skygizmo

Hi All,

I am trying to create an Aged Receivables Report. I am stuck trying to write the query. I am using Firebird database.

Here is my feeble attempt at the SQL statement:

select tblRecord.RecordID,
tblRecord.DocumentID,
tblRecord.TermID,
tblRecord.RecordDate,
tblProject.GoToID,
Sum(tblTaxAgent.ServiceRate) ServiceRate,
Sum(tblTaxAgent.ProductRate) ProductRate,

case tblDetail.LineTaxType of
1 : TaxRate = ServiceRate
2 : TaxRate = ProductRate
else
TaxRate = 0.00
end

Sum(tblTerm.Discount) TotalDiscount,
Sum((tblDetail.Quantity * tblDetail.UnitPrice) * (1 + TaxRate)) LineTotal,
Sum(LineTotal) TotaDetail,
Sum(tblPayment.PayAmount) TotalPayment,

case tblRecord.AllowDiscount of
0 : AppliedDiscount = 0.00
1 : AppliedDiscount = TotalDiscount
else
AppliedDiscount = 0.00
end

where tblRecordType = 4 and TotalDetail - AppliedDiscount - TotalPayment > 0.01


Here are the tables and columns involved:

tblRecord.RecordID
tblRecord.ProjectID
tblRecord.DocumentID
tblRecord.InvoiceTerm
tblRecord.RecordDate
tblRecord.AllowDiscount


tblProject.ProjectID
tblProject.GoToID

tblDetail.RecordID
tblDetail.Quantity
tblDetail.UnitPrice
tblDetail.LineTaxType

tblGoTo.GoToID
tblGoTo.TaxAgentComboID


tblTaxAgentCombo.TaxAgentComboID


tblTaxComBo.TaxComboID
tblTaxCombo.TaxAgentComboID
tblTaxCombo.TaxAgentID

tblTaxAgent.TaxAgentID
tblTaxAgent.ServiceRate
tblTaxAgent.ProductRate

tblTerm.TermID
tblTerm.Discount
tblTerm.DiscountDays

Calculating the tax is the tricky part. Each TaxAgentCombo
has at least one and maybe more TaxAgent associated with it.

For example one might be collecting taxes for the Nation, State,
County, City, and the new ball park. I link the TaxAgentCombo with the TaxAgent through tblTaxCombo. Further
complicating things, each TaxAgent might have different rates
for Products and Services.

Thanks for any help and enjoy the day,

John Ullom