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

    Unanswered: explode decilmal number

    gud day to all,
    i would like to ask how can i explode decimal number in mysql?
    e.g. 1.25, 1.50, 1.75.
    i would like to separate the whole number from the decimal.

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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the floor/round functions
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks, i extracted the whole numbers, but how about the decimal?
    i cant find function to extract decimal numbers.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    original number - integer part = decimal part

    mind you does this need to be done at the SQL level, or should it be done in the presentational level. it could be either, depending on what you are planning to do with the data.. if its needed for sorting, grouping or other data manipulation purposes then do it a the SQL level, otherwise Id consider woing it in the presentation level
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    what do you mean by presentation level?
    i think i have to do it in sql.

    i have 3 fields
    break_hours, reg_hours, idle hours

    some break_hours is greater than 1, like 1.5, 1.25, 1.75.
    and i would like to add the decimal to reg_hours, not the whole number 1.

    first i will split the break_hour from whole to decimal number.
    i will add the reg_hours to decimal number and sum the result.
    i will place the whole number to idle hours and sum it.

    i wil try your suggestions.

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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you dont need to do masses of calcualtions in the SQL layer... often you can do simple mathmatics in the presentation layer (ie what ever is the front end your SQL is supplying.. be that ASP, PHP, VB, Delphi whatever).
    granted I would not do this if the information had to be presented in several different areas .. Id do it in a query
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    often you can do simple mathmatics in the presentation layer
    like in this case, where he wants to use SUM?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    my front end is php,
    if i will do the calculations, formulas and operations in php,
    that would be too difficult, case like too many fields.
    im making a query and put it in php.
    i would like to show you my query, but its working, i just need your advice in making a stadard code. its not yet finish, and my final work of art to be done is the separation of whole number from decimal.
    Code:
    select
        q.`date`
        ,q.last_name
        ,q.firstname
        ,q.tl
        ,q.`group`
        ,q.`client`
        ,q.service
        ,sum(`Total Reg Idle`) as 'Total Reg Idle'
        ,sum(`Total Reg Meeting`) as 'Total Reg Meeting'
        ,sum(`Total Reg Orientation`) as 'Total Reg Orientation'
        ,sum(`Reg Hrs`) as 'Total Reg Hrs'
        ,sum(`Total OT Idle`) as 'Total OT Idle'
        ,sum(`OT Meeting`) as 'Total OT Meeting'
        ,sum(`OT Orientation`) as 'OT Orientation'
        ,sum(`Over Time`) as 'Total OT Hrs'
        ,sum(`Total Man Hours`) as 'Total Man Hours'
    
    
    from(
       SELECT
        coding_log.`date`,
        coding_log.last_name,
        `user`.tl,
        `user`.firstname,
        `user`.`group`,
        batch_log.`client`,
        batch_log.service,
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start < '16:00'
                    THEN floor(coding_log.break_hours) ELSE 0 END
                    ),
                0) +
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start < '17:00' && coding_log.adjustment = 'Idle (personal)'
                    THEN time_to_sec(
                                     coding_log.adjustment_time
                                       )/3600 ELSE 0 END
                      ),
                  0) +
    
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'Idle (work related)'
                    THEN time_to_sec(
                                      coding_log.adjustment_time
                                     )/3600 ELSE 0 END
                    ),
                  0) as 'Total Reg Idle',
    
          time_to_sec(
                      SUM(
                          CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'meeting'
                          THEN coding_log.adjustment_time ELSE 0 END
                          )
                       )/3600 as 'Total Reg Meeting',
    
          time_to_sec(
                          SUM(
                              CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'orientation'
                              THEN coding_log.adjustment_time ELSE 0 END
                               )
                           )/3600
    
            as 'Total Reg Orientation',
    
    time_to_sec(
                subtime(
                        SEC_TO_TIME(
                                    SUM(
                                        TIME_TO_SEC(
                                                    SUBTIME(
                                                            coding_log.time_end,coding_log.time_start
                                                            )
                                                    )
                                        )
                                    ),
                sec_to_time(
                            sum(
                                 if(
                                    coding_log.time_start and coding_log.time_end <= '16:00', '',
                                      if(
                                          coding_log.time_start <= '16:00' and coding_log.time_end >= '16:00',
                                          TIME_TO_SEC(
                                                      subtime(
                                                              coding_log.time_end,'16:00'
                                                              )
                                                      ),
                                       if(
                                          coding_log.time_start and coding_log.time_end > '16:00',
                                          TIME_TO_SEC(
                                                      subtime(
                                                              coding_log.time_end, coding_log.time_start
                                                                                        )
                                                               ),
                                                       '')
                                             )
                                       )
                                                        )
                                    )
                                )
                     )/3600 -
          (
               (SUM(
                   CASE WHEN coding_log.time_start < '16:00'
                   THEN coding_log.break_hours ELSE 0 END
                   ) -
                SUM(
                    CASE WHEN coding_log.time_start < '16:00'
                    THEN floor(coding_log.break_hours) ELSE 0 END
                    )) +
    
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start < '17:00' && coding_log.adjustment = 'Idle (personal)'
                    THEN time_to_sec(
                                     coding_log.adjustment_time
                                       )/3600 ELSE 0 END
                      ),
                  0) +
    
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'Idle (work related)'
                    THEN time_to_sec(
                                      coding_log.adjustment_time
                                     )/3600 ELSE 0 END
                    ),
                   0)) -
    
          time_to_sec(
                      SUM(
                          CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'meeting'
                          THEN coding_log.adjustment_time ELSE 0 END
                          )
                       )/3600 -
    
          time_to_sec(
                          SUM(
                              CASE WHEN coding_log.time_start < '16:00' && coding_log.adjustment = 'orientation'
                              THEN coding_log.adjustment_time ELSE 0 END
                               )
                           )/3600 as 'Reg Hrs',
    
        coalesce(
                  SUM(
                      CASE WHEN coding_log.time_start >= '16:00'
                      THEN floor(coding_log.break_hours) ELSE 0 END
                      ),
                  0) +
    
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'Idle (personal)'
                    THEN time_to_sec(
                                      coding_log.adjustment_time
                                      )/3600 ELSE 0 END
                    ),
                 0) +
    
        coalesce(
                SUM(
                    CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'Idle (work related)'
                    THEN time_to_sec(
                                     coding_log.adjustment_time
                                     )/3600 ELSE 0 END
                    ),
                  0)as 'Total OT Idle',
    
         time_to_sec(
                     SUM(
                         CASE WHEN coding_log.time_start >= '16:00' && coding_log.adjustment = 'meeting'
                         THEN coding_log.adjustment_time ELSE 0 END
                          )
                     )/3600 as 'OT Meeting',
    
         time_to_sec(
    sorry, i cant paste the whole query cause the limit of this forum is only 10,000 characters, and my code is 38485 characters, but im on my 1/4 way from my code.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    homer, have you ever defined a VIEW?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Create View?
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's right, CREATE VIEW

    have you ever? because your query is just screaming to be simplified
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    ok, thanks,
    ill try it now, whew, its a long way back again in coding.
    i think view will help me a lot, but i have to figure out how to use it.

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

  13. #13
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks!
    my code now is
    Code:
    select
        q.`date`
        ,q.last_name
        ,q.firstname
        ,q.tl
        ,q.`group`
        ,q.`client`
        ,q.service
        ,sum(`Total Reg Idle`) as 'Total Reg Idle'
        ,sum(`Total Reg Meeting`) as 'Total Reg Meeting'
        ,sum(`Total Reg Orientation`) as 'Total Reg Orientation'
        ,sum(`Reg Hrs`) as 'Total Reg Hrs'
        ,sum(`Total OT Idle`) as 'Total OT Idle'
        ,sum(`OT Meeting`) as 'Total OT Meeting'
        ,sum(`OT Orientation`) as 'OT Orientation'
        ,sum(`Over Time`) as 'Total OT Hrs'
        ,sum(`Total Man Hours`) as 'Total Man Hours'
    
    from(
        SELECT *
        FROM coding_view
    union
        SELECT *
        FROM key_entry_view
    union
        SELECT *
        FROM scan_view
    union
        SELECT *
        FROM qc_view) as q
    
        group by
        month(q.`date`),
        q.last_name
       ,q.`client`
       ,q.service
    
    
        order by
        monthname(q.`date`)
        ,q.last_name;

    thanks guys.
    however it takes 50seconds to execute.
    how am i going to shorten the execution time? i already indexed my tables.
    any advice?
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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