Board index » delphi » Need help with SQL for Aged Receivables Report

2008-04-30 03:36:11 AM
delphi48
Hi All,
Thanks for reading this. I need to create a query that will
return the dataset for a receivables report. The following tables and columns are involved:
tblProject.GoToID as CustomerID
tblProject.GoToID isd linked to tblGoTo.GoToID
tblProject.ProjectID is linked to tblTicket.ProjectID
tblTaxCombo.TaxComboID is linked to tblGoTo.TaxComboID
tblTaxAgent.TaxComboID is linked to tblTaxCombo.TaxComboID
Sum(tblTaxAgent.ServiceRate) as TotalServiceRate
Sum(tblTaxAgent.ProductRate) as TotalProductRate
//there maybe more then one TaxAgent
tblTicket.TicketID
tblTicket.DocumentNumber as DocumentNumber;
tblticket.TicketDate
tblTicket.TermsID
tblTerms.TermsID is linked to tblTicket.TermsID
tblTerms.DaysDue
tblTerms.DiscountPercent as TotalDiscount
Sum(tblDetail.Quantity * tblDetail.UnitPrice) as TotalDetail
tblDetail.TaxType //Service or Product
tblPayments.TicketID is linked to tblTicket.TicketID
Sum(tblPayment.PayAmount) as TotalPayment
tblTicket.TicketDate + tblTerms.DaysDue as DiscountDate
TotalTax is tricky. Needs to look at each Detail Record and examine TaxType to obtain correct tax amount for each line.
I want this query to create a datset that contains Tickets that money is due on.
I need to DocumentNumber, TotalDetail, TotalTax, TicketDate, DiscountDate, TotalDiscount , CustomerID
My application uses the n-tier model. I want the smallest dataset I can get so all math should be done on the server.
Thanks!
 

Wrong Newsgroup...
"John Ullom" <XXXX@XXXXX.COM>escreveu na mensagem