CODEWITHSUNDEEP
sqlsql-serverexcelt-sql
cjones26
cjones26

Reputation: 3784

How can I convert this Excel graph data into a SQL query?

I have a 3 axis cylinder graph in Excel which is generated from a pivot table sourced from a SQL DB table. Currently, Excel is simply pulling all the data from the SQL table into a pivot table to generate the datasource behind the graph. I would like to use a T-SQL query to pull only the data needed to generate a similar graph outside of Excel (in Javascript to be precise). The graph looks like so:

Cylinder graph

And the SQL table column structure is as follows (terrible schema, I know):

So far I've been able to use code to pull the data needed to populate the source outside of Excel, though this generates 122 separate queries--clearly unacceptable. The way I'm doing it in the code is by selecting all distinct Date_Years, Date_Months, and Date_Operators like so:

Then iterating through the years, months, and operators. I'm looking for a way to pull all this data in as little queries as possible, rather than iterating and repeatedly hitting the DB server.

Any assistance would be greatly appreciated--please let me know if further clarification is necessary. Thanks!

Upvotes: 0

Views: 911

Answers (1)

Rowland Shaw
Rowland Shaw

Reputation: 38130

Essentially, Excel is grouping the values up in some way, so in your graph, it appears to be grouped by year, month and operator, with an aggregation for the values. Essentially it is doing something similar to:

SELECT      Date_Year,
            Date_Month,
            Operator,
            COUNT(*) AS Value

FROM        dbo.Items

GROUP BY    Date_Year,
            Date_Month,
            Operator

Of course, you could tweak that to suit with WHERE clauses or changing the aggregate function, but you might want to start with that...

Upvotes: 1

Related Questions