1 2 Previous Next 25 Replies Latest reply: May 29, 2012 12:55 PM by GMoney Go to original post RSS
      • 15. Re: Convert a number representing "Working Day" to hours
        GMoney
        Solomon - Yes :) that is exactly what i needed, with 1 exception.

        In the case of a result being only hrs such as this 4:0:00 is there a way to format so the result will actually be 4:00:00 and yet still remain as example 20:15:00 in other instances?

        Silly perhaps, but output (format) is important.

        Thank you!
        • 16. Re: Convert a number representing "Working Day" to hours
          Solomon Yakobson
          with sample_table as (
                                select  2 Completion_Days,
                                        4 Completion_Hours,
                                        15 Completion_Minutes
                                  from  dual union all
                                select  0 Completion_Days,
                                        4 Completion_Hours,
                                        5 Completion_Minutes
                                  from  dual
                               )
          select  case
                    when Completion_Days * 8 + Completion_Hours < 10 then '0'
                  end || (Completion_Days * 8 + Completion_Hours) || ':' || to_char(Completion_Minutes,'FM00') || ':00' res
            from  sample_table
          /
          
          RES
          ------------
          20:15:00
          04:05:00
          
          SQL> 
          SY.
          • 17. Re: Convert a number representing "Working Day" to hours
            GMoney
            Solomon - thank you very much, everything you have posted is what I am using. I really do appreciate you taking the time to provide answers.

            I have one last thing that I am trying to do with this. Now that the 3 columns are combined into column called PLAN_HRS. Using this version of Solomon's code:
            (COMPLETION_DAYS * 8 + COMPLETION_HOURS) || ':' || COMPLETION_MINUTES || ':00' AS PLAN_HRS,
            I have another column COMP_DURATION - using Solomon's code from another post.
            trunc((cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)) *24)
                    || to_char(trunc(sysdate) + abs(cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)),':MI:SS') AS COMP_DURATION
            I need to know how to subtract COMP_DURATION from PLAN_HRS? I tried combining both statements into 1 and adding a "-" between but that gave me an Invalid Number error.


            So my result would be something like

            40:15:00 - 20:15:00 = 20:00:00 (OR it could be a negative number as a result)
            • 18. Re: Convert a number representing "Working Day" to hours
              Solomon Yakobson
              GMoney wrote:
              it could be a negative number as a result
              Assuming ACTUAL_COMPLETION_DATE > ACTUAL_RELEASE_DATE:
              trunc(
                    (
                     cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                    ) * 24 - COMPLETION_MINUTES / 60
                   ) ||
              to_char(
                      trunc(sysdate) + abs(
                                           (
                                            cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                                           ) * 24 - COMPLETION_MINUTES / 60
                                          ),
                      ':MI:SS'
                     ) AS DIFF
              SY.
              • 19. Re: Convert a number representing "Working Day" to hours
                GMoney
                Assuming ACTUAL_COMPLETION_DATE > ACTUAL_RELEASE_DATE - Not always, it could be a negative number

                My results are not coming out correctly.
                <Competion hrs+mi+ss>     (actl_comp - actl_rel)          (SQL results)     (expected results)
                     0:15:00          0:00:16               0:53:36          00:14:44
                     0:15:00          <null>                                       <null>                       <null>
                     8:0:00          16:48:37               -7:33:12          -08:48:37
                     0:15:00          0:24:30               0:48:00          -00:09:30
                     0:15:00          1:06:15               0:30:00          -00:51:15
                     8:0:00          0:44:58               -23:00:48          07:15:02
                Is it missing the Hours in the query? Meqaning dealing with the Completion Hours.
                • 20. Re: Convert a number representing "Working Day" to hours
                  GMoney
                  To make this clearer, what I am trying to show is either a task completed under the time or went over the planned time by xx:xx:xx .


                  thanks again for looking.

                  G
                  • 21. Re: Convert a number representing "Working Day" to hours
                    Solomon Yakobson
                    GMoney wrote:

                    Is it missing the Hours in the query?
                    Yeap, it does:
                    trunc(
                          (
                           cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                          ) * 24 - COMPLETION_HOURS - COMPLETION_MINUTES / 60
                         ) ||
                    to_char(
                            trunc(sysdate) + abs(
                                                 cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                                                 - COMPLETION_HOURS / 24 - COMPLETION_MINUTES / 24 / 60
                                                ),
                            ':MI:SS'
                           ) AS DIFF
                    SY.
                    • 22. Re: Convert a number representing "Working Day" to hours
                      Solomon Yakobson
                      This might be easier to understand:
                      PLAN_HRS_HOURS  = REGEXP_SUBSTR(PLAN_HRS,'^[^:]+')
                      PLAN_HRS_MIN    = REGEXP_SUBSTR(PLAN_HRS,'[^:]+',1,2)
                      PLAN_HRS_IN_MIN = REGEXP_SUBSTR(PLAN_HRS,'^[^:]+') * 60 + REGEXP_SUBSTR(PLAN_HRS,'[^:]+',1,2)
                      Same way:
                      COMP_DURATION_HOURS  = REGEXP_SUBSTR(COMP_DURATION,'^[^:]+')
                      COMP_DURATION_MIN    = REGEXP_SUBSTR(COMP_DURATION,'[^:]+',1,2)
                      COMP_DURATION_IN_MIN = REGEXP_SUBSTR(COMP_DURATION,'^[^:]+') * 60 + REGEXP_SUBSTR(COMP_DURATION,'[^:]+',1,2)
                      Now:
                      CASE
                        WHEN ABS(TRUNC((PLAN_HRS_IN_MIN - COMP_DURATION_IN_MIN) / 60)) < 10
                          THEN TO_CHAR(TRUNC((PLAN_HRS_IN_MIN - COMP_DURATION_IN_MIN) / 60),'FM00')
                        ELSE TRUNC((PLAN_HRS_IN_MIN - COMP_DURATION_IN_MIN) / 60)
                      END || TO_CHAR(MOD(ABS(PLAN_HRS_IN_MIN - COMP_DURATION_IN_MIN),60),'FM00')
                      SY.
                      • 23. Re: Convert a number representing "Working Day" to hours
                        GMoney
                        Solomon - sorry you lost me here in this section, for the most part.

                        PLAN_HRS_HOURS  = REGEXP_SUBSTR(PLAN_HRS,'^[^:]+')
                        PLAN_HRS_MIN    = REGEXP_SUBSTR(PLAN_HRS,'[^:]+',1,2)
                        PLAN_HRS_IN_MIN = REGEXP_SUBSTR(PLAN_HRS,'^[^:]+') * 60 + REGEXP_SUBSTR(PLAN_HRS,'[^:]+',1,2)
                        I understand (I think) that you are defining the display format for PLAN_HRS and COMP_DURATION and the CASE statement is defining the formating based on the
                        subtraction results.

                        If the above is correct, then my problem is that I am not putting it into the code correctly then, as I am receiving the ORA-00923: FROM keyword not found where expected.

                        I am also making the assumption that this no longer is included in the script:
                              trunc((cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)) *24)
                                || to_char(trunc(sysdate) + abs(cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)),':MI:SS') Task_Comp_Duration, --ORDER_HRS
                                trunc(
                              (
                               cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                               * 24 + COMPLETION_HOURS) - COMPLETION_MINUTES / 60
                              ) ||
                                to_char(
                                trunc(sysdate) + abs(
                                                     (
                                                      cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                                                      * 8 + COMPLETION_HOURS /24 ) - COMPLETION_MINUTES /24/ 60
                                                    ),
                                ':MI:SS'
                               ) AS Task_Comp_ToPlan_Duration_Diff,
                        Again thank you for sticking with me on this - as you can tell I am learning.

                        G

                        Edited by: GMoney on May 29, 2012 6:43 AM
                        • 24. Re: Convert a number representing "Working Day" to hours
                          GMoney
                          Here is what I have in my script as it stands now:, and getting ORA-00923: FROM keyword not found where expected. Which I understand means I have a "," either in the wrong place or missing. I am just not sure where.
                          PROV_PLAN_HRS_HOURS  = REGEXP_SUBSTR(PROV_PLAN_HRS,'^[^:]+')
                                  PROV_PLAN_HRS_MIN    = REGEXP_SUBSTR(PROV_PLAN_HRS,'[^:]+',1,2)
                                  PROV_PLAN_HRS_IN_MIN = REGEXP_SUBSTR(PROV_PLAN_HRS,'^[^:]+') * 60 + REGEXP_SUBSTR(PROV_PLAN_HRS,'[^:]+',1,2)
                          
                                  TASK_COMP_DURATION_HOURS  = REGEXP_SUBSTR(TASK_COMP_DURATION,'^[^:]+')
                                  TASK_COMP_DURATION_MIN    = REGEXP_SUBSTR(TASK_COMP_DURATION,'[^:]+',1,2)
                                  TASK_COMP_DURATION_IN_MIN = REGEXP_SUBSTR(TASK_COMP_DURATION,'^[^:]+') * 60 + REGEXP_SUBSTR(TASK_COMP_DURATION,'[^:]+',1,2)
                          
                                  CASE
                                    WHEN ABS(TRUNC((PROV_PLAN_HRS_IN_MIN - TASK_COMP_DURATION_IN_MIN) / 60)) < 10
                                    THEN TO_CHAR(TRUNC((PROV_PLAN_HRS_IN_MIN - TASK_COMP_DURATION_IN_MIN) / 60),'FM00')
                                    ELSE TRUNC((PROV_PLAN_HRS_IN_MIN - TASK_COMP_DURATION_IN_MIN) / 60)
                                  END || TO_CHAR(MOD(ABS(PROV_PLAN_HRS_IN_MIN - TASK_COMP_DURATION_IN_MIN),60),'FM00')AS Task_Comp_ToPlan_Duration_Diff,
                          • 25. Re: Convert a number representing "Working Day" to hours
                            GMoney
                            After looking through this multiple times, I believe the bright light switched on.
                              trunc((cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)) *24)
                                    || to_char(trunc(sysdate) + abs(cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date)),':MI:SS') TASK_COMP_DURATION, --ORDER_HRS
                                   trunc(
                                  (
                                   cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                                  ) * 24 - COMPLETION_HOURS - COMPLETION_MINUTES / 60
                                 ) ||
                            to_char(
                                    trunc(sysdate) + abs(
                                                         cast(ACTUAL_COMPLETION_DATE as date) - cast(ACTUAL_RELEASE_DATE as date) - COMPLETION_DAYS
                                                         - COMPLETION_HOURS / 24 - COMPLETION_MINUTES / 24 / 60
                                                        ),
                                    ':MI:SS'
                                   ) AS Task_Comp_ToPlan_Duration_Diff,
                            Which yields the correct results and format -

                            Thanks Solomon! :)
                            1 2 Previous Next