Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Wink Unanswered: simplified codes

    hi,
    how can i simplify these codes?

    Code:
    SELECT
    coding_log.`date`,
    coding_log.last_name,
    `user`.tl,
    batch_log.`client`,
    batch_log.service,
    sum(coding_log.break_hours) as 'Total Break',
    
    Hour(if (min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start))))) + minute(if (min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start)))))/60 as 'Over Time',
    
    sum(Hour(IF(coding_log.`Time_End`<coding_log.`Time_Start`,
        addtime('24:00:00', timediff(coding_log.`Time_End`, coding_log.`Time_Start`)),
        timediff(coding_log.`Time_End`, coding_log.`Time_Start`)))) +
       (sum(minute(IF(coding_log.`Time_End`<coding_log.`Time_Start`,
        addtime('24:00:00', timediff(coding_log.`Time_End`, coding_log.`Time_Start`)),
         timediff(coding_log.`Time_End`, coding_log.`Time_Start`))))/60) as 'Total Man Hours',
    
    HOUR(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.time_end,coding_log.time_start)))),
      if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start)))))) +
    
    MINUTE(subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.time_end,coding_log.time_start)))),
      if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start))))))/60 as 'Regular Hours',
    
    subtime(SEC_TO_TIME(SUM(TIME_TO_SEC(SUBTIME(coding_log.time_end,coding_log.time_start)))),
      if(min(coding_log.time_start) <= '16:00' and max(coding_log.time_end) >= '16:00',
      subtime(max(coding_log.time_end),'16:00'), if(min(coding_log.time_start)
      and max(coding_log.time_end) <= '16:00', '0', subtime(max(coding_log.time_end),
      min(coding_log.time_start))))) as 'Reg Hour'
    
    FROM
    coding_log left join `user` on (`user`.last_name = coding_log.last_name)
    inner join batch_log on (batch_log.job_number = coding_log.job_no)
    where batch_log.total_batches is not null
    
    group by
    coding_log.`date`,
    coding_log.last_name,
    batch_log.`client`
    
    order by
    coding_log.`date` desc
    and the output is this


    HTML Code:
    <table border=1 cellspacing=1 cellpadding=0><tr>
    <th>date</th><th>last_name</th><th>tl</th><th>client</th><th>service</th><th>Total Break</th><th>Over Time</th><th>Regular Hours</th><th>Total Man Hours</th></tr>
    <tr>
    <td>2008-04-15</td><td>Ocampo</td><td>Mercy</td><td>IDEARC/VER</td><td>YELLOW</td><td>0</td><td>0.0000</td><td>0.2500</td><td>0.2500</td></tr>
    <tr>
    <td>2008-04-15</td><td>Saludes</td><td>Nila</td><td>IDEARC/VER</td><td>YELLOW</td><td>2</td><td>0.0000</td><td>5.0833</td><td>5.0833</td></tr>
    <tr>
    <td>2008-04-14</td><td>Agustin, J</td><td>Noel</td><td>IDEARC/VER</td><td>YELLOW</td><td>1.5</td><td>4.0000</td><td>8.6833</td><td>12.6833</td></tr>
    Last edited by homer.favenir; 04-15-08 at 04:58.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do us a favour and give us a hint

    what are you trying to do?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    do us a favour and give us a hint

    what are you trying to do?
    i have 4 tables (table1, table2, table3 and table4)
    i need to know what are the employees doing for the whole day?
    are they in table1, table2, table3 or in table4?
    i need to consolidate all tables using union, to get 1 report for all tables.

    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    i have 4 tables (table1, table2, table3 and table4)
    no, you do not, you have three tables: coding_log, `user`, batch_log

    and you don't need to UNION them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    no, you do not, you have three tables: coding_log, `user`, batch_log

    and you don't need to UNION them
    sorry the code above is not complete coz its a long code actually.
    but i have 4 main tables
    coding_log, key_entry_log, scan_log and qc_log.
    and the other tables is JOIN to link them to the main tables.
    what function rathern than union?
    how can i consolidate the 4 main tables?
    Last edited by homer.favenir; 04-15-08 at 08:35.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by homer.favenir
    how can i consolidate the 4 main tables?
    Code:
    SELECT x.source_table
         , x.time_start
         , x.time_end
         , x.last_name
         , u.t1
      FROM users AS u
    INNER
      JOIN (
           SELECT 'coding_log' AS source_table
                , time_start
                , time_end
                , last_name
             FROM coding_log
           UNION ALL
           SELECT 'key_entry_log'
                , time_start
                , time_end
                , last_name
             FROM key_entry_log
           UNION ALL
           SELECT 'scan_log'
                , time_start
                , time_end
                , last_name
             FROM scan_log
           UNION ALL
           SELECT 'qc_log'
                , time_start
                , time_end
                , last_name
             FROM qc_log
           ) AS x
        ON x.last_name = u.last_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    Code:
    SELECT x.source_table
         , x.time_start
         , x.time_end
         , x.last_name
         , u.t1
      FROM users AS u
    INNER
      JOIN (
           SELECT 'coding_log' AS source_table
                , time_start
                , time_end
                , last_name
             FROM coding_log
           UNION ALL
           SELECT 'key_entry_log'
                , time_start
                , time_end
                , last_name
             FROM key_entry_log
           UNION ALL
           SELECT 'scan_log'
                , time_start
                , time_end
                , last_name
             FROM scan_log
           UNION ALL
           SELECT 'qc_log'
                , time_start
                , time_end
                , last_name
             FROM qc_log
           ) AS x
        ON x.last_name = u.last_name
    many thanks!
    but how about
    Code:
    batch_log.`client`,
    batch_log.service,
    above?

    i cant join them to your code.

    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  8. #8
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    hi,
    the query above takes 23 minutes to execute.
    its too slow.
    it is simplified but it doesnt cut the time from executing.

    please advice

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am sorry, i cannot help you

    i have no idea what you are doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    i am sorry, i cannot help you

    i have no idea what you are doing
    i have figured out the solutions to the problem of joining the

    Code:
    batch_log.`client`,
    batch_log.service,
    but i have still a problem, could you help me with this?
    i encountered "LOST CONNECTION TO MYSQL SERVER DURING QUERY".
    thus the connection timed out during execution?

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Read this.
    George
    Home | Blog

Posting Permissions

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