October 13, 2008 at 9:33 am
Hi once again. I was hoping one of you gurus might be able to advise how I can get the desired output from the query below + educate me when using nested SELECT and maybe suggest a better way of constructing the query? This post is similar to a previous post in that it uses the same tables, however the question is different hence the new thread (hope that makes sense).
What is it I want to achieve?
I have a DB where users can enter a progress payment against a sales order. I would like to list all Sales Orders + any Progress Payment information. The query is to output Customer Name / Order Value / Payment Made / Outstanding Balance
So Query would return
Customer Name 'A' Sales Order '12345' Sales Order Value '£1000' Progress Payment '£500' Outstanding Balance '£500'
The DB does not store the Sales Order Value in the underlying table. Instead this is calculated by summing the 'SalesOrderItems.CurItemValue'.
Current query:
SELECT Customers.CustomerName,
SalesOrders.SalesOrderId,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS OutStandingBalance,
(Select SUM(SalesOrderItems.CurItemValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') <> 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS TotalOrderValue
FROM salesorders
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
INNER JOIN ProgressPayments ON SalesOrders.SalesOrder = ProgressPayments.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
GROUP BY Customers.CustomerName,
SalesOrders.SalesOrder,
SalesOrders.SalesOrderId,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue
I tried modifying the above to replace SUM(SalesOrderItems.CurItemValue) AS OutStandingBalance with
(Select SUM(SalesOrderItems.CurItemValue - ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') <> 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutstandingBalance,
SQL returned error:
Msg 8124, Level 16, State 1, Line 1
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.
The following line is incorrect SalesOrderItems.CurItemValue - ProgressPayments.InvoiceValue it's the - ProgressPayments.InvoiceValue that is incorrect.
I would also like the output to be ex vat (currently ProgressPayments.InvoiceValue includes VAT).
I appreciate it may be difficult to advise based on the above, if you require further information just ask and I will post it if I am able.
I have posted example of the output currently returned by the query.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 13, 2008 at 10:07 am
I have been having a play. I updated line:
(Select SUM(SalesOrderItems.CurItemValue) - SUM(ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') = 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,
Nearly but not quite (well I did not get the error!).
Rather than the outstanding balance return 0 for order where value was 600, payment made was 600 outstanding balance returned -1300.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 13, 2008 at 10:10 am
This is a guess based on your above query. This may or may not return the correct results, as this is completely untested.
[font="Courier New"]SELECT Customers.CustomerName,
SalesOrders.SalesOrderId,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue AS PaymentMade,
SUM(SalesOrderItems.CurItemValue) AS OutStandingBalance,
SUM(SOI.CurItemValue) AS TotalOrderValue
FROM salesorders
INNER JOIN SalesOrderItems ON SalesOrders.SalesOrder = SalesOrderItems.SalesOrder
INNER JOIN ProgressPayments ON SalesOrders.SalesOrder = ProgressPayments.SalesOrder
INNER JOIN Customers ON SalesOrders.Customer = Customers.Customer
LEFT JOIN SalesOrderItems SOI ON SalesOrders.SalesOrder = SOI.SalesOrder
AND ISNULL(SOI.freetextitem,'') = 'Progress Payment'
GROUP BY Customers.CustomerName,
SalesOrders.SalesOrderID,
ProgressPayments.PaymentDate,
ProgressPayments.InvoiceValue [/font]
If this doesn't work for you, please post sample table structure / data in the method described in the link in my signature.
October 13, 2008 at 10:11 am
Update AGAIN. OK now amended further.....
(Select SUM(SalesOrderItems.CurItemValue) - (ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') <> 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,
This appears to return what I am after.....will need to test a few as the test data is poor.
Can anyone advise the formula to strip off the VAT?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 13, 2008 at 2:46 pm
This one is now closed, many thanks for posting Garadin.
kind regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply