Skip to Main Content
  • Questions

  • calculate how many minutes employee worked for a day in hourly breakup

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, sudheer.

Asked: February 28, 2018 - 8:41 am UTC

Last updated: March 02, 2018 - 12:38 am UTC

Version: 10

Viewed 1000+ times

You Asked

I am having data like
Empcode attdate intime outtime
12345 03-JAN-2018 00:00:00 03-JAN-2018 10:56:00 03-JAN-2018 16:44:00
12345 03-JAN-2018 00:00:00 03-JAN-2018 20:00:00 03-JAN-2018 23:00:00

Now i need output like
Empcode 0-1 1-2 2-3 3-4 4-5 5-6 6-7 7-8 8-9 9-10 10-11 11-12 12-13 13-14 14-15 15-16 16-17 upto 23-24
12345 0 0 0 0 0 0 0 0 0 0 4 60 60 60 60 60 44

I need working minutes by hourly breakup. Like above sometimes employee will have 2 or more rows for the same day.

and Connor said...

SQL> create table t
  2  ( emp int,
  3    in_time date,
  4    out_time date );

Table created.

SQL>
SQL>
SQL> insert into t values (1, trunc(sysdate)+1.5/24,   trunc(sysdate)+3.5/24);

1 row created.

SQL> insert into t values (1, trunc(sysdate)+5.4/24,   trunc(sysdate)+9.5/24);

1 row created.

SQL> insert into t values (1, trunc(sysdate)+10/24,   trunc(sysdate)+11/24);

1 row created.

SQL>
SQL> insert into t values (2, trunc(sysdate)+2.2/24,   trunc(sysdate)+3.5/24);

1 row created.

SQL> insert into t values (2, trunc(sysdate)+5/24,   trunc(sysdate)+7/24);

1 row created.

SQL> insert into t values (2, trunc(sysdate)+10.7/24,   trunc(sysdate)+11.2/24);

1 row created.

SQL> insert into t values (2, trunc(sysdate)+9.2/24,   trunc(sysdate)+9.4/24);

1 row created.

SQL> select * from t;

       EMP IN_TIME             OUT_TIME
---------- ------------------- -------------------
         1 01/03/2018 01:30:00 01/03/2018 03:30:00
         1 01/03/2018 05:24:00 01/03/2018 09:30:00
         1 01/03/2018 10:00:00 01/03/2018 11:00:00
         2 01/03/2018 02:12:00 01/03/2018 03:30:00
         2 01/03/2018 05:00:00 01/03/2018 07:00:00
         2 01/03/2018 10:42:00 01/03/2018 11:12:00
         2 01/03/2018 09:12:00 01/03/2018 09:24:00


So I'm assuming entries do not span a day. First I'll extract some useful information to avoid having to re-type it.

SQL>
SQL> select
  2       emp,
  3       in_time,
  4       out_time,
  5       to_number(to_char(in_time,'HH24')) as in_hr,
  6       to_number(to_char(out_time,'HH24')) as out_hr,
  7       to_number(to_char(in_time,'MI')) as in_min,
  8       to_number(to_char(out_time,'MI')) as out_min
  9    from t;

       EMP IN_TIME             OUT_TIME                 IN_HR     OUT_HR     IN_MIN    OUT_MIN
---------- ------------------- ------------------- ---------- ---------- ---------- ----------
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          1          3         30         30
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          5          9         24         30
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         10         11          0          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          2          3         12         30
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          5          7          0          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         10         11         42         12
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          9          9         12         24

7 rows selected.


Now with that, I'll join to a 24row set to get 1 row for every hour of the day. Using this I can compare each start/end period with the particular hour of the day, covering the various possbilities that start/end and the current hour have. (eg, contained within, not contained within, etc)


SQL>
SQL> with t_extended as
  2  ( select
  3       emp,
  4       in_time,
  5       out_time,
  6       to_number(to_char(in_time,'HH24')) as in_hr,
  7       to_number(to_char(out_time,'HH24')) as out_hr,
  8       to_number(to_char(in_time,'MI')) as in_min,
  9       to_number(to_char(out_time,'MI')) as out_min
 10    from t
 11  ),
 12  hr_of_day as
 13    ( select level-1 hr from dual connect by level <= 24 )
 14  select
 15    e.emp,
 16    e.in_time,
 17    e.out_time,
 18    h.hr,
 19    case
 20      -- totally outside range
 21      when in_hr > hr then 0
 22      when out_hr < hr then 0
 23      -- less than 1 hour
 24      when in_hr = hr and out_hr = hr then  out_min - in_min
 25      -- ends on hour
 26      when in_hr < hr and out_hr = hr then  out_min
 27      -- start on hour
 28      when in_hr = hr and out_hr > hr then 60 - in_min
 29      -- contained
 30      when in_hr < hr and out_hr > hr then 60
 31    end dur
 32  from t_extended e, hr_of_day h
 33  order by 1,2,4;

       EMP IN_TIME             OUT_TIME                    HR        DUR
---------- ------------------- ------------------- ---------- ----------
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          0          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          1         30
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          2         60
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          3         30
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          4          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          5          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          6          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          7          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          8          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00          9          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         10          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         11          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         12          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         13          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         14          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         15          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         16          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         17          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         18          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         19          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         20          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         21          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         22          0
         1 01/03/2018 01:30:00 01/03/2018 03:30:00         23          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          0          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          1          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          2          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          3          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          4          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          5         36
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          6         60
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          7         60
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          8         60
         1 01/03/2018 05:24:00 01/03/2018 09:30:00          9         30
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         10          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         11          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         12          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         13          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         14          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         15          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         16          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         17          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         18          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         19          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         20          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         21          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         22          0
         1 01/03/2018 05:24:00 01/03/2018 09:30:00         23          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          0          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          1          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          2          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          3          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          4          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          5          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          6          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          7          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          8          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00          9          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         10         60
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         11          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         12          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         13          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         14          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         15          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         16          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         17          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         18          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         19          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         20          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         21          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         22          0
         1 01/03/2018 10:00:00 01/03/2018 11:00:00         23          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          0          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          1          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          2         48
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          3         30
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          4          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          5          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          6          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          7          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          8          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00          9          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         10          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         11          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         12          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         13          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         14          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         15          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         16          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         17          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         18          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         19          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         20          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         21          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         22          0
         2 01/03/2018 02:12:00 01/03/2018 03:30:00         23          0

       EMP IN_TIME             OUT_TIME                    HR        DUR
---------- ------------------- ------------------- ---------- ----------
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          0          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          1          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          2          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          3          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          4          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          5         60
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          6         60
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          7          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          8          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00          9          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         10          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         11          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         12          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         13          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         14          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         15          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         16          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         17          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         18          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         19          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         20          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         21          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         22          0
         2 01/03/2018 05:00:00 01/03/2018 07:00:00         23          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          0          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          1          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          2          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          3          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          4          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          5          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          6          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          7          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          8          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00          9         12
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         10          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         11          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         12          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         13          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         14          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         15          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         16          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         17          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         18          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         19          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         20          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         21          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         22          0
         2 01/03/2018 09:12:00 01/03/2018 09:24:00         23          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          0          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          1          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          2          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          3          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          4          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          5          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          6          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          7          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          8          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00          9          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         10         18
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         11         12
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         12          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         13          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         14          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         15          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         16          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         17          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         18          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         19          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         20          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         21          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         22          0
         2 01/03/2018 10:42:00 01/03/2018 11:12:00         23          0

168 rows selected.


Now it just a case of pivoting and summing up to get the totals


SQL>
SQL> with t_extended as
  2  ( select
  3       emp,
  4       in_time,
  5       out_time,
  6       to_number(to_char(in_time,'HH24')) as in_hr,
  7       to_number(to_char(out_time,'HH24')) as out_hr,
  8       to_number(to_char(in_time,'MI')) as in_min,
  9       to_number(to_char(out_time,'MI')) as out_min
 10    from t
 11  ),
 12  hr_of_day as
 13    ( select level-1 hr from dual connect by level <= 24 ),
 14  raw_data as (
 15  select
 16    e.emp,
 17    e.in_time,
 18    e.out_time,
 19    h.hr,
 20    case
 21      -- totally outside range
 22      when in_hr > hr then 0
 23      when out_hr < hr then 0
 24      -- less than 1 hour
 25      when in_hr = hr and out_hr = hr then  out_min - in_min
 26      -- ends on hour
 27      when in_hr < hr and out_hr = hr then  out_min
 28      -- start on hour
 29      when in_hr = hr and out_hr > hr then 60 - in_min
 30      -- contained
 31      when in_hr < hr and out_hr > hr then 60
 32    end dur
 33  from t_extended e, hr_of_day h
 34  --order by 1,2,4
 35  )
 36  select *
 37  from  ( select emp, hr, dur from raw_data )
 38  pivot ( sum(dur) as t for (hr) in ( 0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23))
 39  /

       EMP        0_T    1_T    2_T    3_T    4_T    5_T    6_T    7_T    8_T    9_T   10_T   11_T   12_T   13_T   14_T   15_T   16_T   17_T   18_T   19_T   20_T   21_T   22_T   23_T
---------- ---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
         1          0     30     60     30      0     36     60     60     60     30     60      0      0      0      0      0      0      0      0      0      0      0      0      0
         2          0      0     48     30      0     60     60      0      0     12     18     12      0      0      0      0      0      0      0      0      0      0      0      0

2 rows selected.

SQL>
SQL>

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

sudheer ravipati, March 01, 2018 - 11:54 am UTC

Hi
How to change the column name in the final output from
0_T 1_T 2_T to H1, H2, H3?

if i am having rows for same employee with different dates like following
empid Date in_time out_time
10086 05-JAN-2018 00:00:00 05-JAN-2018 09:45:52 05-JAN-2018 10:06:30
10086 05-JAN-2018 00:00:00 05-JAN-2018 10:18:51 05-JAN-2018 11:17:34
10086 08-JAN-2018 00:00:00 08-JAN-2018 10:00:00 08-JAN-2018 20:03:00

Then i need output in separate row for each date.
for one date one row i need.

have you got a minute

Racer I., March 01, 2018 - 2:19 pm UTC

Hi,


insert into t values (1, trunc(sysdate)+1.5/24,   trunc(sysdate)+3.5/24);
insert into t values (1, trunc(sysdate)+5.4/24,   trunc(sysdate)+9.5/24);
insert into t values (2, trunc(sysdate)+10.7/24,  trunc(sysdate)+11.2/24);
insert into t values (2, trunc(sysdate)+9.2/24,   trunc(sysdate)+9.4/24);
insert into t values (2, trunc(sysdate)+23.2/24,  trunc(sysdate+1)+0.4/24);
commit;

WITH
TRange AS (
  SELECT MIN(trunc(in_time)) fd, MAX(trunc(out_time)) - MIN(trunc(out_time)) + 1 days
  FROM t),
Mins AS (
  SELECT fd + ((ROWNUM - 1) / (60 * 24)) TMt 
  FROM  TRange
  CONNECT BY ROWNUM <= (days * 60 * 24)),
Pres AS (
  SELECT t.emp, m.TMt, CASE WHEN TMt >= t.in_time AND TMt < t.out_time THEN 1 ELSE 0 END IsP  
  FROM   Mins m
    CROSS JOIN t),
PData AS (
  select emp, TRUNC(TMt) PDay, TO_CHAR(TMt, 'HH24') DH, SUM(IsP) PMt
  from Pres
  GROUP BY emp, TRUNC(TMt), TO_CHAR(TMt, 'HH24')
  --HAVING SUM(IsP) > 0
  )
select *
from  PData
  pivot (sum(PMt) for (DH) in ('00' H1 ,'01' H2 ,'02' H3 ,'03' H4 ,'04' H5 ,'05' H6 ,'06' H7 ,'07' H8 ,'08' H9 ,'09' H10 ,'10' H11 ,'11' H12 ,'12' H13 ,'13' H14 ,'14' H15 ,'15' H16 ,'16' H17 ,'17' H18 ,'18' H19 ,'19' H20 ,'20' H21 ,'21' H22 ,'22' H23 ,'23' H24))

EMP PDAY H1 H2 H3 H4 H5 H6 H7 H8 H9 H10 H11 H12 H13 H14 H15 H16 H17 H18 H19 H20 H21 H22 H23 H24
1 01.03.2018 0 30 60 30 0 36 60 60 60 30 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1 02.03.2018 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 01.03.2018 0 0 0 0 0 0 0 0 0 12 18 12 0 0 0 0 0 0 0 0 0 0 0 48
2 02.03.2018 24 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0


pro : works with midnight spanning ranges
cons : expensive (1440 mins * days * emps)

for the pivot-line :

select listagg(x, ' ,') WITHIN GROUP (ORDER BY pos) line from (
select rownum pos, '''' || LPAD(TO_CHAR(rownum - 1), 2, '0') || ''' H' || (rownum) x 
from dual connect by rownum <= 24)

regards,
Connor McDonald
March 02, 2018 - 12:38 am UTC

love your title :-)

Awesome answer

sudheer ravipati, March 02, 2018 - 4:39 am UTC

Thank you very much for the answer. I am able to develope a report with this logic.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.