Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    19

    Unanswered: Possible to improve this query?

    Hi everyone,
    I figure there must be a better way to get the results I am after, this query is very long, and has to be repeated for each "class"...

    Code:
    SQLWELD		 = "SELECT	( SUM(CASE WHEN MONTH(clockout) = 01 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 01 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 01 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld01, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 02 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 02 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 02 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld02, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 03 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 03 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 03 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld03, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 04 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 04 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 04 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld04, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 05 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 05 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 05 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld05, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 06 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 06 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 06 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld06, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 07 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 07 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 07 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld07, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 08 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 08 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 08 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld08, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 09 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 09 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 09 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld09, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 10 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 10 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 10 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld10, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 11 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 11 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 11 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld11, " & _
    "											( SUM(CASE WHEN MONTH(clockout) = 12 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) - SUM(CASE WHEN MONTH(clockout) = 12 AND jobnum='0' AND series='0' THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) ) / SUM(CASE WHEN MONTH(clockout) = 12 THEN ROUND(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600,1) ELSE 0 END) * 100 AS weld12 " & _
    " FROM hours, employees WHERE hours.empid = employees.employeeid AND class='welding' AND SUBSTRING(hours.clockout,1,4) = '"&reportyear&"';"
    Basically, were calculating a percentage for each month from 1-12, for the class "welding". Now, I need to do this for like 10 classes... it ends up being hundreds of lines of code just in queries, and I'm running each one under its own execution. There must be a way to simplify this, perhaps some for-each function?

    Any help very much appreciated.

    Edit: Wow, that doesnt post well... I hope it's readable and understandable like that... if you copy paste it in Notepad++ it's much more readable.
    Last edited by rtown; 03-17-15 at 13:18.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Consider this construct instead:
    Code:
    SELECT <which_table?>.class
         , Month(hours.clockout) As the_month
         , Sum(Round(Time_To_Sec(TimeDiff(hours.clockout, hours.clockin)) / 3600, 1))
           ... etc
    FROM   hours
     INNER
      JOIN employees
        ON employees.employeeid = hours.empid
    WHERE  <which_table?>.class IN ('welding', 'something_else')
    AND    Year(hours.clockout) = 2015
    GROUP
        BY <which_table?>.class
         , Month(hours.clockout)
    This will give you one row, per class, per month, rather than adding new columns each time!
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2010
    Posts
    19
    Quote Originally Posted by gvee View Post
    Consider this construct instead:
    Code:
    SELECT <which_table?>.class
         , Month(hours.clockout) As the_month
         , Sum(Round(Time_To_Sec(TimeDiff(hours.clockout, hours.clockin)) / 3600, 1))
           ... etc
    FROM   hours
     INNER
      JOIN employees
        ON employees.employeeid = hours.empid
    WHERE  <which_table?>.class IN ('welding', 'something_else')
    AND    Year(hours.clockout) = 2015
    GROUP
        BY <which_table?>.class
         , Month(hours.clockout)
    This will give you one row, per class, per month, rather than adding new columns each time!
    This is interesting, but how do I then access the information? For example, I want to display the sum for the 3rd month, in class welding... how do I access that info from this query?
    Also why do we set the_month, what is this used for because if I print this variable it's always "1".

    Thank you!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    "the_month" is just an alias for the computed column, just like you had "weld01" in your query.

    Here's a slightly improved version for you to toy with:
    Code:
    SELECT the_year
         , the_month
         , class
         , some_sum
    FROM   (
            SELECT Year(hours.clockout) As the_year
                 , Month(hours.clockout) As the_month
                 , <which_table?>.class
                 , Sum(Round(Time_To_Sec(TimeDiff(hours.clockout, hours.clockin)) / 3600, 1)) As some_sum
            FROM   hours
             INNER
              JOIN employees
                ON employees.employeeid = hours.empid
            GROUP
                BY Year(hours.clockout)
                 , Month(hours.clockout)
                 , <which_table?>.class
           ) As a_subquery
    WHERE  the_year = 2015
    AND    the_month = 3
    AND    class = 'welding'
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2010
    Posts
    19
    Quote Originally Posted by gvee View Post
    "the_month" is just an alias for the computed column, just like you had "weld01" in your query.

    Here's a slightly improved version for you to toy with:
    Code:
    SELECT the_year
         , the_month
         , class
         , some_sum
    FROM   (
            SELECT Year(hours.clockout) As the_year
                 , Month(hours.clockout) As the_month
                 , <which_table?>.class
                 , Sum(Round(Time_To_Sec(TimeDiff(hours.clockout, hours.clockin)) / 3600, 1)) As some_sum
            FROM   hours
             INNER
              JOIN employees
                ON employees.employeeid = hours.empid
            GROUP
                BY Year(hours.clockout)
                 , Month(hours.clockout)
                 , <which_table?>.class
           ) As a_subquery
    WHERE  the_year = 2015
    AND    the_month = 3
    AND    class = 'welding'
    Hmm, but i'll still have to repeat this query for every month 1-12, and every class that exists. My query above functions fine I was just looking for a way to simplify it and hopefully pose less of a load on the server.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You need to play with the where clause a bit more...

    All data in 2015 for welding classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class = 'welding'
    All data in 2015 for hammering classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class = 'hammering'
    All data in 2015 for welding and hammering classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class IN ('welding', 'hammering')
    All data, ever for welding classes (all months with data)
    Code:
    WHERE  class IN 'welding'
    etc, etc.
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2010
    Posts
    19
    Quote Originally Posted by gvee View Post
    You need to play with the where clause a bit more...

    All data in 2015 for welding classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class = 'welding'
    All data in 2015 for hammering classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class = 'hammering'
    All data in 2015 for welding and hammering classes (all months with data)
    Code:
    WHERE  the_year = 2015
    AND    class IN ('welding', 'hammering')
    All data, ever for welding classes (all months with data)
    Code:
    WHERE  class IN 'welding'
    etc, etc.
    But then I still need multiple queries. I'm not 100% sure you understand what I am trying to accomplish... In any case, my query works it's just very long.
    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •