Difficult Query.....
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Looks like you are on your way!!
John
ASKER
ahh....LOL
ASKER
I keep getting a syntax error near ,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah yes.... DOH!
ASKER
think if I add everything up it'll work?
something like:
....AmountPayments, (AmountTime + AmountParts - AmountPayments) as SomeTotal
?
something like:
....AmountPayments, (AmountTime + AmountParts - AmountPayments) as SomeTotal
?
ASKER
nope....that didn't work.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SWEEEEEEETTTT!!!!!!!!
much thanks.....wish I could give ya more points than the max 500!!!!
much thanks.....wish I could give ya more points than the max 500!!!!
I live to server.
Glad it works for you.
John
Glad it works for you.
John
ASKER
out of curiosity why the IsNull?