We help IT Professionals succeed at work.

Difficult Query.....

323 Views
Last Modified: 2010-03-20
This one may be a little difficult to explain, so please bear with me as I attempt to do so...

I am trying to generate a XML file as an export of my Invoicer's list of invoices.  What I am wondering is how I can get the $ totals correct in the query.

Right now I have 5 Tables that are related for this, I will give the general layout:

tblInvoices
iID >pk, increment 1
iInvoiced >datetime, getdate()

tblPayRate
rID >pk, increment 1
rRate >money

tblInventory
invID >pk, increment 1
invName >nvarchar, the parts name
invCost >money, the cost....

tblInvoiceTime
itID >pk, increment 1
iID >int, relates to tblInvoices
rID >int, relates to tblPayRate
rQuan >int, #of hours

tblInvoiceParts
ipID >pk, increment 1
iID >int, relates to tblInvoices
invID >int, relates to tblInventory
ipQuan >int, #of pieces

tblInvoicePayments
tipID >pk, increment 1
iID >int, relates to tblInvoices
tipAmount >money


Now, what makes this query difficult, is the fact that not only do I have to relate all these tables into a single query (I'm assuming a JOIN would be in order), but I also need to get the totals for each related table (tblInvoiceTime - need total $, tblInvoiceParts - need total $ + value from taxRate(which I get from a seperate table based on where the client is located), and tblInvoicePayments - total $), and then total everything together to get an balance due.

I have a function to generate my XML export that excepts a SQL Query as one of the parameters  (<---don't really think this is relevant, but may be nice to know...)

How can I do this without pulling my (and possibly yours) hairs out?
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
i think I actually do get the idea    =D

out of curiosity why the IsNull?
So it returns a 0 if there are no time, parts or payments for the selected invoice.

Looks like you are on your way!!

John

Author

Commented:
ahh....LOL

Author

Commented:
I keep getting a syntax error near ,
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
ah yes....  DOH!

Author

Commented:
think if I add everything up it'll work?

something like:

....AmountPayments, (AmountTime + AmountParts - AmountPayments) as SomeTotal

?

Author

Commented:
nope....that didn't work.....
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
SWEEEEEEETTTT!!!!!!!!

much thanks.....wish I could give ya more points than the max 500!!!!
I live to server.

Glad it works for you.

John
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.