Create timesheet table by select query in Oracle

Question

I have a table in this form (this is just the partial view, the table contains more columns):

| USER     | date_of        | duration |  
|----------|----------------|----------|  
| John     |   01.08.2017   |   5      |  
| Anna     |   01.08.2017   |   6      |  
| John     |   03.08.2017   |   10     |      
| Anna     |   05.08.2017   |   7      |     
| Eugene   |   30.08.2017   |   16     |

I need one query (select) that will return the data in the form:

|USER    | 01| 02| 03| 04| 05| 06| ... | 29| 30| 31|Total|Count| 
|--------|---|---|---|---|---|---|-----|---|---|---|-----|-----|  
|Anna    |  6|   |   |   |  7|   |     |   |   |   |  13 |   2 |  
|John    |  5|   | 10|   |   |   |     |   |   |   |  15 |   1 |  
|Eugene  |   |   |   |   |   |   |     |   | 16|   |  16 |   1 |

So, I need to fill timesheet with actual data in columns 01..31 (day of month, 28/29 for February, 31 for August, etc.) with null in empty days, calculate total sum of hrs for each User in column 'Total' and count days with Duration more than 5 into 'Count' column. In the full version, the query will be much more complicated, but for now I need to start with described above.

Ok, what have you tried? No one is going to write this for you. — , Aug 28 '17 at 19:57
You could use Oracle's `pivot` feature as demonstrated here https://stackoverflow.com/questions/7730111/pivoting-rows-into-columns-dynamically-in-oracle/7731169#7731169 — dave, Aug 28 '17 at 20:16
Another nice example from oracle docs: [using pivot and unpivot](https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#CHDFIIDD) — wolfrevokcats, Aug 28 '17 at 20:19
LOL - the correction is still not OK, if you changed from Ann to Anna you should also change the output. — , Aug 28 '17 at 20:31
dave, I tried pivot without success, because I need aggregations, conditional count and more... — Sergey Sytnik, Aug 28 '17 at 20:51

score 1 · Answer 1 · answered Aug 28 '17 at 20:18

This sounds like PIVOT, except you also want a few aggregates. This can be done with GROUP BY ROLLUP or other similar extensions to aggregation, although it's more complicated because you want both a TOTAL and a conditional COUNT.

Everything is simple (but tedious) using "manual" pivoting - what everyone had to do before the PIVOT operator was introduced in Oracle 11.1. This has the advantage that it will work in earlier versions, and - while there is more to write - it is easier to modify in the future, if needed.

with
     inputs ( usr, date_of, duration ) as ( 
       select 'John'  , to_date('01.08.2017', 'dd.mm.yyyy'),  5 from dual union all
       select 'Ann'   , to_date('01.08.2017', 'dd.mm.yyyy'),  6 from dual union all
       select 'John'  , to_date('03.08.2017', 'dd.mm.yyyy'), 10 from dual union all
       select 'Ann'   , to_date('05.08.2017', 'dd.mm.yyyy'),  7 from dual union all
       select 'Eugene', to_date('30.08.2017', 'dd.mm.yyyy'), 16 from dual
     )
-- End of simulated data (for testing only, not part of the solution).
-- Use your own table and column names in the SQL query below.
select   usr,
         sum(case extract(day from date_of) when  1 then duration end) as "01",
         sum(case extract(day from date_of) when  2 then duration end) as "02",
         sum(case extract(day from date_of) when  3 then duration end) as "03",
 --        .............
 --        .............
         sum(case extract(day from date_of) when 31 then duration end) as "31",
         sum(duration) as total_duration,
         count(case when duration > 5 then 1 end) as cnt
from     inputs
group by usr
;

Partial output (with the columns included so far in the illustration):

USR     01  02  03  31  TOTAL_DURATION  CNT
------  --  --  --  --  --------------  ---
John     5      10                  15    1
Eugene                              16    1
Ann      6                          13    2

USER and COUNT are reserved words, do not use them as column names, in the input and in the output. It is also best not to use double-quoted column names (which means 01 and the like shouldn't be used as column names).

@SergeySytnik **NOTE**: This assumes that all the dates are in August 2017. If they aren't, you will start mixing up values from different months. To avoid that, add a WHERE condition to make sure only dates from August 2017 are considered in the rest of the query. — , Aug 28 '17 at 20:46
I understand it. Thank you. In my case input table always filtered to one month — Sergey Sytnik, Aug 28 '17 at 20:58

Create timesheet table by select query in Oracle

1 Answers1