Specific SQL for total billing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Specific SQL for total billing

Author

Topic

popo
Starting Member

3 Posts

Posted - 2008-04-10 : 10:33:01

My DB has the following description

Table & Columns: ID field is identity. Currency is either CDN or USD.
Order: ID, DateOrder, Currency, AmountOrder
Invoice: ID, NumOrder, DateInvoice, AmountInvoice
Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit
Rate: YYYY, MM, US

Relationship among tables:
Order & Invoice : on Order.ID = Invoice.NumOrder, one to many
Order & Credit: on Order.ID = Credit.NumOrder, one to many
Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many

Other conditions:
For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN.
The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN.
A credit always uses the rate of DateInvoice of its associated invoice.

Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN

Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)]
WHERE the DateInvoice and DateCredit must be in the specified period.
AND associated Order.Devise = USD

SQL:
1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder
3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate
6 AND DateCredit >= startDate AND DateCredit <= endDate

Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.

How can I correct this, please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 10:36:30

This is a MICROSOFT SQL Server forum.
Which RDBMS are you using?

E 12°55'05.25"
N 56°04'39.16"

popo
Starting Member

3 Posts

Posted - 2008-04-10 : 10:39:46

Thanks Peso for the quick reply.
I'm using MS SQL Server 2005

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-04-10 : 11:47:33

"AND DateCredit >= startDate AND DateCredit <= endDate"

I think having this bit in the where clause turns the LEFT join into a INNER JOIN
See can you move it into part of the ON clause

popo
Starting Member

3 Posts

Posted - 2008-04-10 : 11:57:42

Thanks Andrew,
I've tried your sugesstion like this:

SQL:
1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order INNER JOIN ( Invoice LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice)
3 ON Order.ID = Invoice.NumOrder
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate
6 AND DateCredit >= startDate AND DateCredit <= endDate

The results are the same. i.e. The orders which have the invoices but not credits are not included.

I think the problem is at the 2nd SUM in line (1): SUM (AmountCredit * ....). If the credit is not present, the order is not taken.
But how do I fix this, please?

bfoster
Starting Member

30 Posts

Posted - 2008-04-10 : 16:55:06

1 SELECT SUM((AmountInvoice - COALESCE(AmountCredit, 0))*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order INNER JOIN Invoice ON Order.ID = Invoice.NumOrder
3 LEFT JOIN Credit ON (Invoice.ID = Credit.NumInvoice AND DateCredit >= startDate AND DateCredit <= endDate)
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources