I have a query which calculates the timestamp difference as Duration in hours & minutes and Then Rounds it to the closest hour based on the rule:

For ex: If minutes > 29, then Duration= Hours+1 Else Duration=Hours

 SELECT OC.BU_ID   ,
        OC.ORD_NBR ,
        OC.STRT_DTS,
        OC.END_DTS ,
        CASE
               WHEN CAST(EXTRACT(MINUTE FROM ((OC.END_DTS - OC.STRT_DTS) DAY(4) TO SECOND)) AS     DECIMAL) > 29
               THEN CAST(EXTRACT(DAY FROM ((OC.END_DTS    - OC.STRT_DTS) DAY(4) TO SECOND)) *24 AS DECIMAL)+ CAST(EXTRACT(HOUR FROM ((OC.END_DTS - OC.STRT_DTS) DAY(4) TO SECOND)) AS DECIMAL) + 1
               ELSE CAST(EXTRACT(DAY FROM ((OC.END_DTS    - OC.STRT_DTS) DAY(4) TO SECOND)) *24 AS DECIMAL)+ CAST(EXTRACT(HOUR FROM ((OC.END_DTS - OC.STRT_DTS) DAY(4) TO SECOND)) AS DECIMAL)
        END                                                                                                                                                                                                        AS ROUNDED_DURATION,
        CAST(EXTRACT(DAY FROM((OC.END_DTS - OC.STRT_DTS DAY(4) TO SECOND))) *24 AS DECIMAL(18,2)) +(EXTRACT(HOUR FROM ((OC.END_DTS - OC.STRT_DTS) DAY(4) TO SECOND))) +(CAST(EXTRACT(MINUTE FROM ((OC.END_DTS - OC.STRT_DTS) DAY(4) TO SECOND)) AS DECIMAL(18,2)) /60 ) AS "HOUR + % MINUTES"
 FROM   MFG_INC.OC_ENTRY_ROLLUP_AMER_STEP1 OC
 WHERE  OC.ORD_NBR = ''CAST(EXTRACT(YEAR FROM ((OC.END_DTS - OC.STRT_DTS) YEAR(4) TO MONTH)) AS DECIMAL) <=27
 AND
        (
               OC.END_DTS  IS NOT NULL
        AND    OC.STRT_DTS IS NOT NULL
        )

Sample Results for one order:

BU_ID   ORD_NBR ORD_CYCLE_STG_CD    ORD_CYCLE_SUBSTG_CD STRT_DTS    END_DTS CYCLE_DRTN_HRS  HOUR + % MINUTES
1212    83038398    ORDER   ENTRY   5/28/2010 12:28 5/28/2010 12:38 0   0.17
1212    83038398    ORDER   ENTRY   5/28/2010 12:28 5/28/2010 12:38 0   0.17
1212    83038398    ORDER   PROCESS 5/28/2010 12:38 5/28/2010 12:39 0   0.02
1212    83038398    ORDER   PROCESS 5/28/2010 12:38 5/28/2010 12:39 0   0.02
1212    83038398    PLAN    NETWORK 5/28/2010 12:39 5/29/2010 9:24  21  20.73
1212    83038398    PLAN    NETWORK 5/28/2010 12:39 5/29/2010 9:24  21  20.73
1212    83038398    PLAN    FACILITY    5/29/2010 9:24  5/31/2010 1:53  40  40.47
1212    83038398    PLAN    FACILITY    5/29/2010 9:24  5/31/2010 1:53  40  40.47
1212    83038398    BUILD   BUILD   5/31/2010 1:53  6/1/2010 4:50   27  26.93
1212    83038398    BUILD   BUILD   5/31/2010 1:53  6/1/2010 4:50   27  26.93
**1212  83038398    DELIVER DELIVER 6/1/2010 4:50   6/9/2010 8:15   194 195.4
1212    83038398    DELIVER DELIVER 6/1/2010 4:50   6/9/2010 8:15   194 195.4**

The last two lines are strange. Instead of Rounding it 195, it has subtracted 1 (195 - 1) and shows 194. Can someone please throw light on what is the error with the above calculation?