Time Calculation

Following Example shows how we calculate the time.

CREATING THE TABLE

----------------------------------------

CREATE TABLE KMH_1

(  PKKEY  NUMBER CONSTRAINT PK_PKEY PRIMARY KEY,

   ID     NUMBER,

   NAME   VARCHAR2(20 BYTE),

   DATE1  DATE);

DML STATEMENTS FOR TABLE

-----------------------------------------------------

INSERT INTO KMH_1

VALUES

(1,1,'A',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(2,1,'A',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(3,2,'B',TO_DATE('12/03/2013 07:30','DD/MM/RRRR HH24:MI')); 

INSERT INTO KMH_1

VALUES

(4,2,'B',TO_dATE('12/03/2012  16:00','DD/MM/RRRR HH24:MI')); 

/

QUERY

------------

SELECT A.ID,A.NAME,MAX(A.START_TIME) ST,MAX(A.END_TIME) ET,

TRUNC(REPLACE(MAX(A.END_TIME),':','.')  - REPLACE(MAX(A.START_TIME),':','.'),0) NO_HRS

FROM 

(SELECT ID,NAME, 

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 0 AND 12 THEN TO_CHAR(DATE1,'HH24:MI') END START_TIME,

CASE WHEN TO_NUMBER(TO_CHAR(DATE1,'HH24'))BETWEEN 13 AND 23 THEN TO_CHAR(DATE1,'HH24:MI') END END_TIME

FROM KMH_1

ORDER BY PKKEY) A

GROUP BY A.ID,A.NAME