While I've technically solved my own problem, I am rather curious if there's a better solution than the one I hacked together.
I have a table of values of weather data. The table is StateCode|Year|Month|w1|w2...|W6|.
We have three reporting methods to build into a web-form chart (we used Dotnet.highcharts, if anyone is interested in that), Monthy, Quarterly, and Yearly. Since the data comes as monthly, that one is given. Yearly data is easy to average out, but Quarterly reports proved to be a huge pain in my ass.
To get quarterly data, I first had to select out the average of every stat for the first three months, each year, for each statecode. Then I selected from that the average of each stat for each statecode and grouped by year. I had to execute this query four times, once for each set of three months. I made a view out of each one.
Then I did:
SELECT 1, * FROM Quarter1
UNION
SELECT 2, * FROM Quarter2
UNION
SELECT 3, * FROM Quarter3
UNION
SELECT 4, * FROM Quarter4
And made that a view as well. From that view, I can then do
SELECT * FROM Quarters_Data
ORDER BY year, 1
Which will give me all the quarterly data, in order of year and quarter, which I can properly plot on a graph.
What I'm wondering is if there's a better way to do this. I have a very limited understanding of cursors and indexes and I feel like there has to be a way that involves fewer transactions. This is also not in a stored procedure because the table I'm getting the original data from is dynamically named by the web application and I have no idea how to pass in a table name and dynamically create a table in SQL. The queries are built in the webapp and passed to the database with the appropriate table name in the string.
Does anyone have an ideas for how to improve this process? I'm not looking to get too fancy, but there has to be a better way, right? I can upload my scripts somewhere later tonight when I get home if anyone is interested in taking a closer look. I'm working in SQL Server 2014.
Thanks for your time.