Table - Chart

[Cifale]

Hi all,

I am working on a Chart showing the activity per customer for the last 12 months + the current month (I work in a technical support team).

Basically, my SQL query is providing me with the right result most of the time. It looks like this:

Sep 10 185 44 96 22
Oct 10 330 96 154 46
Nov 10 365 114 202 46
Dec 10 220 63 126 23
Jan 11 335 125 184 53
Feb 11 283 72 222 32
Mar 11 357 81 214 52
Apr 11 167 20 196 17
May 11 294 64 218 26
Jun 11 288 71 202 34
Jul 11 365 166 200 65
Aug 11 301 114 180 72
Sep 11 206 79 129 56

It works fine and my chart is nice.

Now, I need to set the same data per customer (chosen in the user interface).
The SQL Query gives me the following for instance:

Oct 10 1 0 0 NULL
Nov 10 10 6 5 2
Dec 10 1 0 0 NULL
Feb 11 1 1 1 0
May 11 1 0 0 NULL
Jun 11 9 4 4 4
Jul 11 3 0 1 NULL

This particular customer does not call every month.

What I would like to get is as follow, so I can draw my chart.

Sep 10 0 0 0 0
Oct 10 1 0 0 NULL
Nov 10 10 6 5 2
Dec 10 1 0 0 NULL
Jan 11 0 0 0 0
Feb 11 1 1 1 0
Mar 11 0 0 0 0
Apr 11 0 0 0 0
May 11 1 0 0 NULL
Jun 11 9 4 4 4
Jul 11 3 0 1 NULL
Aug 11 0 0 0 0
Sep 11 0 0 0 0

I understand that SQL query won't work for this but I'm wondering if I could use Dataset or something.
I'm stuck here (I'm not really a developer and I'm just learning SQL Queries and Visual Basic for this purpose).

Any ideas?

Let me know. Thanks a lot.

[lolafuertes]

If you are usiong SQL Server, you can merge a query from your table obtaining an empty structure and the table filtered contents using a union all joint like

SELECT Month, Year, Sum(F1) AS Fi1, Sum(F2) AS Fi2, Sum(F3) as Fi3, Sum(F4) as Fi4 FROM (SELECT DISTINCT Month, Year, 0 as F1, 0 as F2, 0 as F3, 0 As F4 FROM YourTable UNION ALL SELECT Month, Year, F1, F2, F3, F4 FROM YourTable WHERE Customer = '1234') T GROUP BY Month, Year ORDER BY Month, Year;

Hope this helps

[Cifale]

That would be OK if I knew in front which month this customer called... Which I don't until I run my query.

Maybe I was not clear enough in my explanation.

I'm using the same query for each and every customer. Just changing the SQL query (in Visual Basic) depending on the user choice (in the user interface).
So, the query is in 2 parts:
Query 1: "SELECT Month, Year, Sum(F1) AS Fi1, Sum(F2) AS Fi2, Sum(F3) as Fi3, Sum(F4) as Fi4 FROM Table "

Query 2: " GROUP BY Month, Year ORDER BY Month, Year"

In between, I have a piece of SQL query:
Querycustomer: "WHERE customer = " & selectedcustomer.text

Before sending the query to the MySql Database, I concatene the 3 parts:

Query = query1 & querycustomer & query2

That way, I don't care when the customer has called... until I need the same kind of chart for everyone.
So I think it is not in SQL that I have to change, but in VB.Net.

Does that make more sense?

[lolafuertes]

Sorry, maybe i need to explain my solution a little bit more...

The part "SELECT DISTINCT Month, Year, 0 as F1, 0 as F2, 0 as F3, 0 As F4 FROM Table" will assure that, for every existing month & year in the database you will always have a record with almost a value of 0. No matter wich customer is. Allways you'll have a record for a month if almost one customer has called this month.

The part "SELECT Month, Year, F1, F2, F3, F4 FROM Table WHERE customer = " & selectedcustomer.text returns all the info from your customer, but in this case, you are unsure to have all the months.

You need to sum both results, the months with 0 values (nothing altered) with the month returned according to the filter applied and yu'll obtain a record for each existing month & year. Here the UNION ALL will return a dataset with 1 or 2 rows by month depending if the custumer has made calls (2) or not (1).

Then, this intermediate result (or sub query) named T, is totalized Grouping by month & year and giving sum of each field for that month year.

If this solution is not supported by your SQL version, then you can do it using the 2 queries (orderd by month & year) then you can cicle over the very first (year & month with 0 values) and search if a record exist in the second (Customer values) for the same month Year.

Finally you need to present only the 13 last months retrieved from any of the solutions.

Hope this helps.

[Cifale]

Thank you so much! I was able to apply your query and with some work, I succeeded1 (yes, my query is way more complex than what I showed).
You're a genius, Lola!

[lolafuertes]

Im glad to hear that.

If this thread has been solved, please be so kind to mark it.
Thanks in advance.