Solved

Visual Studio SQL Query for SubTotal and Total

Posted on 2009-05-13
16
459 Views
Last Modified: 2013-11-26
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|TotalInvoices|TotalInvoicesNotCustA

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

Open in new window

0
Comment
Question by:jason_woods
  • 10
  • 6
16 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24375945
First of all some questions:

1) What SQL engine is used to execute above select? Is it FoxPro? Which version?
2) What contains the customer.acct_date?
0
 
LVL 41

Assisted Solution

by:pcelba
pcelba earned 500 total points
ID: 24376370
It is not necessary to have three selects, two are enough. Hope your SQL engine allows conditional summing:
SUM(CASE WHEN custno <> '0000001146' THEN invtotal ELSE 0 END) AS NotTotalSales,
if not, you may try:
SUM(IIF(custno <> '0000001146', invtotal, 0.00)) AS NotTotalSales,

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(CASE WHEN custno <> '0000001146' THEN invtotal ELSE 0 END) 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

Open in new window

0
 
LVL 41

Expert Comment

by:pcelba
ID: 24376513
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.
0
 
LVL 4

Author Comment

by:jason_woods
ID: 24376702
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24376788
So in such case you have to use MONTH() and YEAR() without quotes. And IIF() in the SUM() instead of CASE WHEN.
0
 
LVL 4

Author Comment

by:jason_woods
ID: 24376857
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.
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

Open in new window

0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24377019
Interesting. I've never used VS to call VFP... The GROUP BY could be solved following way (removing sum from select list and adding two columns to Group by):
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

Open in new window

0
 
LVL 4

Author Comment

by:jason_woods
ID: 24377276
Still a problem. I may have to do the Nested Select on a row-by-row basis in code.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:pcelba
ID: 24377341
What is the error mesaage ?
0
 
LVL 4

Author Comment

by:jason_woods
ID: 24377453
Command Contains Unrecognized Phrase/keyword
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24377461
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24377513
So, you should test if it works without nested select. I'll install VFP9 later today.
0
 
LVL 4

Author Comment

by:jason_woods
ID: 24378774
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

Open in new window

0
 
LVL 41

Expert Comment

by:pcelba
ID: 24378789
Perfect, now I see it :-)
0
 
LVL 4

Author Closing Comment

by:jason_woods
ID: 31580980
The final answer would be at the bottom of the post.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24379344
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.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now