jason_woods
asked on
Visual Studio SQL Query for SubTotal and Total
I want to create a query that analyses the amount a customer has been invoiced compared to the total invoices for a month. As a bonus, we have a customer that skews the numbers and I would like to also show a total that excludes that customer.
The results would look like this:
Customer|CustomerInvoices| DateMonth| DateYear|T otalInvoic es|TotalIn voicesNotC ustA
The tables have these fields:
Customer:
CustNo
CustName
Invoices:
InvNo
CustNo
InvDate
InvTotal
The customer to exclude has the CustNo of '0000001046'.
I have tried nested tables and it looks like it could work, but the joins appear convoluted and the whole thing is quite slow.
I don't think my example is a good query, so don't be afraid to toss out a new one.
The results would look like this:
Customer|CustomerInvoices|
The tables have these fields:
Customer:
CustNo
CustName
Invoices:
InvNo
CustNo
InvDate
InvTotal
The customer to exclude has the CustNo of '0000001046'.
I have tried nested tables and it looks like it could work, but the joins appear convoluted and the whole thing is quite slow.
I don't think my example is a good query, so don't be afraid to toss out a new one.
SELECT customer.custname AS Customer, SUM(plmain.invtotal) AS CustInvoices, `MONTH`(plmain.invdate) AS DateMonth, `YEAR`(plmain.invdate) AS DateYear,
SUM(TotalSales.AllTotalSales) AS TotalSalesAll, SUM(TotalSalesNotInsitsu.NotTotalSales) AS TotalSalesNotInsitsu
FROM plmain, customer,
(SELECT SUM(invtotal) AS AllTotalSales, `MONTH`(invdate) AS AllDateMonth, `YEAR`(invdate) AS AllDateYear
FROM plmain plmain_2
GROUP BY AllDateMonth, AllDateYear) TotalSales,
(SELECT SUM(invtotal) AS NotTotalSales, `MONTH`(invdate) AS NotDateMonth, `YEAR`(invdate) AS NotDateYear
FROM plmain plmain_1
WHERE (custno <> '0000001146')
GROUP BY NotDateMonth, NotDateYear) TotalSalesNotInsitsu
WHERE plmain.custno = customer.custno AND plmain.invdate < GoMonth(customer.acct_date, 48) AND `MONTH`(plmain.invdate) = TotalSales.AllDateMonth AND
`YEAR`(plmain.invdate) = TotalSales.AllDateYear AND `MONTH`(plmain.invdate) = TotalSalesNotInsitsu.NotDateMonth AND `YEAR`(plmain.invdate)
= TotalSalesNotInsitsu.NotDateYear
GROUP BY customer.custname, DateYear, DateMonth, TotalSalesAll, TotalSalesNotInsitsu
ORDER BY customer.custname, DateYear, DateMonth
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, the `MONTH`() and `YEAR`() function call isn't allowed neither in FoxPro nor in SQL Server. It was the main reason I am asking for SQL engine version.
ASKER
I am using visual Studio 2008 and creating an ASP.Net page. The database is VFP9.
I am using VFPOLEDB.1 in my connection string.
I am using VFPOLEDB.1 in my connection string.
So in such case you have to use MONTH() and YEAR() without quotes. And IIF() in the SUM() instead of CASE WHEN.
ASKER
Correct, to a point. In Visual Studio 2008 I have to use the `Month` feature so that VS08 doesn't complain. I also am using the IIF() in the previous code you gave.
I am now getting a GROUP BY error. It doesn't occur in the Nested statement when I use it alone.
I am now getting a GROUP BY error. It doesn't occur in the Nested statement when I use it alone.
SELECT customer.custname AS Customer, SUM(plmain.invtotal) AS CustInvoices, `MONTH`(plmain.invdate) AS DateMonth, `YEAR`(plmain.invdate) AS DateYear,
SUM(TotalSales.AllTotalSales) AS TotalSalesAll, SUM(TotalSales.NotTotalSales) AS TotalSalesNotInsitsu
FROM plmain, customer,
(SELECT SUM(invtotal) AS AllTotalSales, SUM(IIF(custno <> '0000001146',invtotal ,0.00)) AS NotTotalSales, `MONTH`(invdate) AS AllDateMonth,
`YEAR`(invdate) AS AllDateYear
FROM plmain plmain_2
GROUP BY AllDateMonth, AllDateYear) TotalSales,
WHERE plmain.custno = customer.custno AND plmain.invdate < GoMonth(customer.acct_date, 48)
AND `MONTH`(plmain.invdate) = TotalSales.AllDateMonth
AND `YEAR`(plmain.invdate) = TotalSales.AllDateYear
GROUP BY customer.custname, DateYear, DateMonth
ORDER BY customer.custname, DateYear, DateMonth
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Still a problem. I may have to do the Nested Select on a row-by-row basis in code.
What is the error mesaage ?
ASKER
Command Contains Unrecognized Phrase/keyword
To be honest, I am not sure if nested selects are allowed at this place and I have no VFP 9 installed to test it right now. It will take some time to download it and test.
To do it row by row in code is possible. Another option could be temporary table and joining this temp table but, again, I am not sure if it is allowed by ADO.
To do it row by row in code is possible. Another option could be temporary table and joining this temp table but, again, I am not sure if it is allowed by ADO.
So, you should test if it works without nested select. I'll install VFP9 later today.
ASKER
Found it!!! There was an extra comma in the FROM clause at the end!!!
SELECT customer.custname AS Customer, SUM(plmain.invtotal) AS CustInvoices, `MONTH`(plmain.invdate) AS DateMonth, `YEAR`(plmain.invdate) AS DateYear,
TotalSales.AllTotalSales AS TotalSalesAll, TotalSales.NotTotalSales AS TotalSalesNotInsitsu
FROM plmain, customer,
(SELECT SUM(invtotal) AS AllTotalSales, SUM(IIF(custno <> '0000001146',invtotal ,0.00)) AS NotTotalSales, `MONTH`(invdate) AS AllDateMonth, `YEAR`(invdate) AS AllDateYear
FROM plmain plmain_2
GROUP BY AllDateMonth, AllDateYear) TotalSales
WHERE plmain.custno = customer.custno AND plmain.invdate < GoMonth(customer.acct_date, 48)
AND `MONTH`(plmain.invdate) = TotalSales.AllDateMonth
AND `YEAR`(plmain.invdate) = TotalSales.AllDateYear
GROUP BY customer.custname, DateYear, DateMonth, TotalSales.AllTotalSales, TotalSales.NotTotalSales
ORDER BY customer.custname, DateYear, DateMonth
Perfect, now I see it :-)
ASKER
The final answer would be at the bottom of the post.
You even answered my question about nested query (or subselect) possibility in VFP9 and you have to be honored, so I've asked new question in Web development - Web languages/standards - ASP zone and waiting for your answer. Thanks for it.
1) What SQL engine is used to execute above select? Is it FoxPro? Which version?
2) What contains the customer.acct_date?