Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Need help with SQL for Aged Receivables Report

6 views
Skip to first unread message

John Ullom

unread,
Apr 29, 2008, 3:36:11 PM4/29/08
to

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!

John Ullom

unread,
Apr 29, 2008, 5:20:21 PM4/29/08
to

Sorry, I forgot to mention the database which is Firebird 2.0

Ricardo Carmo

unread,
Jul 9, 2008, 10:27:21 AM7/9/08
to
Wrong Newsgroup...

"John Ullom" <s...@try.fly> escreveu na mensagem
news:481778ab$1...@newsgroups.borland.com...

0 new messages