+ Reply to Thread
Results 1 to 6 of 6

Thread: Anyone a SQL Wizard?     submit to reddit submit to twitter

  1. #1
    a p. sweet dude
    Pens win! Pens Win!!! PENS WIN!!!!!

    Join Date
    Jul 2010
    Posts
    22,228
    BG Level
    10

    Anyone a SQL Wizard?

    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.

  2. #2
    Salvage Bans
    Join Date
    Feb 2007
    Posts
    811
    BG Level
    5
    FFXIV Character
    Orinthia Warsong
    FFXIV Server
    Excalibur
    FFXI Server
    Bahamut

    I'm no wizard but here's a site that may help in quickly sharing and testing things: http://sqlfiddle.com

    You mainly want to see if there's a way to speed up averaging the quarters. Since it's a specific thing that could be associated with all months, could you make a new little table that associates a month with quarters (like JAN and 1, APR and 2, etc), join the two tables on month to bring quarters into your main table, and then average based on matching quarters and years? I'm not very experienced with sql but that kinda sounds like it might make something, or a huge mess idk

  3. #3
    Unique and/or Creative Phrase
    Join Date
    Aug 2006
    Posts
    1,432
    BG Level
    6
    FFXI Server
    Shiva

    Did a bit of googling and it seems you can convert a date to a quarter. Unfortunately it seems your year and months are stored in their own fields so I tried casting them to datetime and then pulling the quarter from that.

    I haven't tested this and I'm not sure that I fully understood the scenario, but could you do something like this? The date conversion stuff I pulled from google so I'm not sure if it will work as-is.

    Code:
    select StateCode, 
           Year, 
           datepart(q, CAST(
                            CAST(year AS VARCHAR(4)) +
                            RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
                            '01'
                       AS DATETIME)) as quarter, 
           avg(w1), avg(w2), avg(w3), avg(w4), avg(w5), avg(w6) 
    from weather_data
    group by StateCode, 
             Year, 
             datepart(q, CAST(
                              CAST(year AS VARCHAR(4)) +
                              RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
                              '01'
                         AS DATETIME))
    order by Year, quarter;

    If this doesn't work for you let me know where at and I can modify it.

  4. #4
    So hard we fuck rocks
    Join Date
    Jan 2009
    Posts
    3,088
    BG Level
    7
    FFXI Server
    Sylph

    Would using the julian dates work? Just run a query on days 1-90, 91-180, etc.
    I have no idea what im talking about.

  5. #5
    Piece of shit Bruins fan

    Join Date
    Jun 2008
    Posts
    5,520
    BG Level
    8
    FFXIV Character
    Beat Daisukenojo
    FFXIV Server
    Hyperion

    Quote Originally Posted by Obsidian View Post
    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.
    Okay, I would ordinarily do this block-by-block but that will take me a while so I'm just going to dump.

    Index management is something devs should be aware of, but based on the size of the project it often falls more upon the shoulders of DBAs/sysadmins. An index is basically a pre-arranged "cheat sheet" that the table uses to jump quickly to relevant rows based on criteria you're likely to filter on. It's not free, however - inserting rows can take longer because the indices have to be updated, and they also need to be occasionally rebuilt in the same way that you would defragment a hard drive (basically, if you're familiar with compsci, you would need to rebalance your spanning tree to level out the nodes).

    Now then. As far as passing a table name into a SP, you can't select from a dynamically-declared table, but what you *can* do is to build a SQL statement as a dynamic string, execute that SQL in an SP, and return the results in a table variable (or possibly a #temp table). I probably wouldn't do the views you're doing, that's going to eat up more memory without a significant payoff in performance (the Query Analyzer should bear this out).

    What I don't understand is why your quarterly reports would be any more difficult than your yearly reports. Think about it this way. You're doing something like this:

    SELECT AVG(HiTemp), Year
    FROM Data
    GROUP BY Year

    If you need quarterly data, shouldn't your query basically be like this?

    SELECT AVG(HiTemp) As HiTemp, Year, (1 +FLOOR(Month/4))) As Quarter
    FROM Data
    GROUP BY Year, (1 +FLOOR(Month/4)))

    If you want the grouping to look a bit prettier:

    SELECT AVG(HiTemp) As HiTemp, (Year + '-Q' + (1 +FLOOR(Month/4))) As Quarter
    FROM Data
    GROUP BY (Year + '-Q' + (1 +FLOOR(Month/4)))

    Take a look at this, I think it'll help your understanding:

    http://stackoverflow.com/questions/1...lculated-field

  6. #6
    a p. sweet dude
    Pens win! Pens Win!!! PENS WIN!!!!!

    Join Date
    Jul 2010
    Posts
    22,228
    BG Level
    10

    Thanks everyone for your help, especially Cutriss. That SO link really did help.

Similar Threads

  1. Anyone here good with SQL?
    By Corrderio in forum Tech
    Replies: 2
    Last Post: 2012-05-01, 16:33