SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Horizontal to Vertical Results

santhosh TAug 21 2019 — edited Aug 22 2019

Hi All,

WITH t1 AS
     (SELECT '31669' ID, '2019-08-18 07:31:48' TIME, 1 in_out, 'In' descr
        FROM DUAL
      UNION ALL
      SELECT '31669' ID, '2019-08-18 07:41:18' TIME, 2 in_out, 'Out' descr
        FROM DUAL
      UNION ALL
      SELECT '31669' ID, '2019-08-18 07:44:18' TIME, 1 in_out, 'In' descr
        FROM DUAL
      UNION ALL
      SELECT '31669' ID, '2019-08-18 07:55:06' TIME, 1 in_out, 'In' descr
        FROM DUAL
      UNION ALL
      SELECT '31669' ID, '2019-08-18 13:57:21' TIME, 2 in_out, 'Out' descr
        FROM DUAL
      UNION ALL
      SELECT '31669' ID, '2019-08-18 15:31:48' TIME, 2 in_out, 'Out' descr
        FROM DUAL)
SELECT *
  FROM t1

Using the above, could you please suggest me the query to display the out put in single row. I need to select min of IN time and Max of Out time. If there is no Out Time, then it should be null or zero. And also calculate the TIME_DIFF

This is to calculate the employee working hours.

ID TIME_IN IN_OUT DESCR TIME_OUT IN_OUT DESCR TIME_DIFF

31669 2019-08-18 07:31:48 1 In 2019-08-18 15:31:48 2 Out 8:00

Please suggest.

Thanks

This post has been answered by mathguy on Aug 21 2019

Jump to Answer

Added on Aug 21 2019

3 comments

1,098 views