Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Gordan.

Asked: February 20, 2018 - 11:00 am UTC

Last updated: April 24, 2019 - 1:38 pm UTC

Version: 11g Release 2 Express Edition

Viewed 1000+ times

You Asked

I am sorry for asking this seemingly trivial question, but I have been struggling with it for some time, my deadline is approaching and I can't find any answers for it.

I have 3 tables:
Calendar table:
CREATE TABLE  "CJ_CAL" 
   ( "CAL_ID" NUMBER NOT NULL ENABLE, 
 "CAL_DATE" DATE, 
  CONSTRAINT "CJ_CALENDAR_PK" PRIMARY KEY ("CAL_ID")
   )


Employee table
CREATE TABLE  "CJ_EMP" 
   ( "EMP_ID" NUMBER NOT NULL ENABLE, 
 "FIRST_NAME" VARCHAR2(40), 
 "LAST_NAME" VARCHAR2(40), 
 "DEPARTMENT" VARCHAR2(10), 
 "STATION" VARCHAR2(10), 
 "MANAGER" VARCHAR2(40), 
 "WORK_HOURS" NUMBER, 
 "SCHEDULE" VARCHAR2(400), 
  CONSTRAINT "CJ_EMP_PK" PRIMARY KEY ("EMP_ID")
   )


and

Attendance table
CREATE TABLE  "CJ_EMP_CAL" 
   ( "EMP_CAL_ID" NUMBER NOT NULL ENABLE, 
 "CAL_ID" NUMBER NOT NULL ENABLE, 
 "EMP_ID" NUMBER NOT NULL ENABLE, 
 "STATUS" NUMBER, 
 "OVERTIME" NUMBER, 
 "REMARK" VARCHAR2(400), 
  CONSTRAINT "CJ_EMP_CAL_PK" PRIMARY KEY ("EMP_CAL_ID")
   )


cj_emp_cal references cj_cal and cj_emp using a foreign key.

I need to create an interactive report where I will display all the employees in separate rows and have columns for each day of a selected month. All the employees have been 'assigned' a working day in the CJ_EMP_CAL table and I need to display the status (A - Absent, P - Present, OT - Overtime, V - vacation) in the report for each day and each employee respectively.
I also need to add two more columns as data for each employee (cj_emp.department and cj_emp.station).

Thank you for being a wonderful resource!

Best regards,
Gordan

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/gawd2m8otv97euvdkjuafmjnd

and Chris said...

So, if an employee has no entry in CJ_EMP_CAL, does that mean they should be shown as absent? Or something else?

In any case, it sounds like you need to show all the rows from CJ_CAL in the timeframe. Then any related rows in the other tables. So you need to outer join them all together.

Which gives a query like:

select c.cal_date, s.stat_name, ec.emp_id, e.department , e.station
from   CJ_CAL c
left join CJ_EMP_CAL ec 
on    c.cal_id = ec.cal_id
and   ec.emp_id in (1, 2, 3, 4)
left join  CJ_EMP e
on    e.emp_id = ec.emp_id
left join CJ_STATUS s
on    ec.status = s.stat_id
where  c.cal_date >= date'2018-02-01'
and    c.cal_date < date'2018-02-08'
order  by ec.emp_id, c.cal_date;

CAL_DATE               STAT_NAME   EMP_ID   DEPARTMENT   STATION   
01-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
02-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
04-FEB-2018 06:08:04   ABSENT             1 GSE          YHM       
05-FEB-2018 06:08:04   OVERTIME           1 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
07-FEB-2018 06:08:04   PRESENT            1 GSE          YHM       
01-FEB-2018 06:08:04   VACATION           2 GSE          YHM       
02-FEB-2018 06:08:04   ABSENT             2 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
04-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
05-FEB-2018 06:08:04   ABSENT             2 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            2 GSE          YHM       
07-FEB-2018 06:08:04   OVERTIME           2 GSE          YHM       
01-FEB-2018 06:08:04   ABSENT             3 GSE          YHM       
02-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
03-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
04-FEB-2018 06:08:04   ABSENT             3 GSE          YHM       
05-FEB-2018 06:08:04   OVERTIME           3 GSE          YHM       
06-FEB-2018 06:08:04   PRESENT            3 GSE          YHM       
07-FEB-2018 06:08:04   VACATION           3 GSE          YHM       
01-FEB-2018 06:08:04   ABSENT             4 GSE          YVR       
02-FEB-2018 06:08:04   VACATION           4 GSE          YVR       
03-FEB-2018 06:08:04   PRESENT            4 GSE          YVR       
04-FEB-2018 06:08:04   OVERTIME           4 GSE          YVR       
05-FEB-2018 06:08:04   PRESENT            4 GSE          YVR       
06-FEB-2018 06:08:04   OVERTIME           4 GSE          YVR       
07-FEB-2018 06:08:04   ABSENT             4 GSE          YVR

Rating

  (4 ratings)

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

Comments

hmmm

Gordan, February 20, 2018 - 2:47 pm UTC

Hey Chris,

it's not quite what I'm looking for.
The report has a predefined format:
employee_id, employee_name, department, station, 1, 2, 3, 4,... 31

the number columns being the day in a given month.

There are records for calendar dates in cj_cal
and for each record from cj_emp there is data inserted in cj_emp_cal.

The administrator will then need to mark attendance for each day (based on cj_emp_cal) and I will need to provide the report, so an example record in the repord would be:

11, John, GSE, YHM, P, P, P, A, P, OT ....
(P - present, A - Absent etc.)
Regardless of the definition of the statuses I still have to provide the status for each day from CJ_EMP_CAL for that employee.
Chris Saxon
February 21, 2018 - 2:24 pm UTC

See the review below

PIVOT by another other name...

Duke Ganote, February 20, 2018 - 6:09 pm UTC

So it's just a pivot and adding in a few more columns, right? 

select e.emp_id, e.last_name
     , max(case extract(day from cal_date) when 1 then s.stat_name end) as "1"
     , max(case extract(day from cal_date) when 2 then s.stat_name end) as "2"
     , max(case extract(day from cal_date) when 3 then s.stat_name end) as "3"
     , max(case extract(day from cal_date) when 4 then s.stat_name end) as "4"
     , max(case extract(day from cal_date) when 5 then s.stat_name end) as "5"
     , max(case extract(day from cal_date) when 6 then s.stat_name end) as "6"
     , max(case extract(day from cal_date) when 7 then s.stat_name end) as "7"
     , max(case extract(day from cal_date) when 8 then s.stat_name end) as "8"
     , max(case extract(day from cal_date) when 9 then s.stat_name end) as "9"
---
     , max(case extract(day from cal_date) when 28 then s.stat_name end) as "28"
     , max(case extract(day from cal_date) when 29 then s.stat_name end) as "29"
     , max(case extract(day from cal_date) when 30 then s.stat_name end) as "30"
     , max(case extract(day from cal_date) when 31 then s.stat_name end) as "31"
  from cj_emp_cal EC
  join cj_cal C
    on c.cal_id = ec.cal_id
  join cj_emp e
    on e.emp_id = ec.emp_id
  join ( select stat_id, substr(stat_name,1,1) as stat_name from cj_status ) s
    on s.stat_id = ec.status
 where TRUNC(cal_date,'MM') = date'2018-02-01' 
 and status is not null
group by e.emp_id, e.last_name order by 1;

EMP_ID LAST_NAME 1 2 3 4 5 6 7 8 9 28 29 30 31
1 Hand A P O V A O A A A  -   -   -   - 
2 Bulthuis A V P A O O A V A  -   -   -   - 
3 Setchell O O O A O P O O O  -   -   -   - 
4 Espada A A A A A O A O O  -   -   -   - 
5 Santiago V A P O O A V A O  -   -   -   - 
6 Gillespie V A V A O A P A A  -   -   -   - 
7 Westerterp O A A V O O P P O  -   -   -   - 
8 Franklin P V A A O P O O A  -   -   -   - 
9 Perry O O A A O O O P P  -   -   -   - 
10 Matt A O P A A V P O P  -   -   -   - 
11 Jodilynn O P O O P P O O P  -   -   -   - 
12 Mackenzie O P O A A A P V O  -   -   -   - 
13 Brent O O A P O O P P A  -   -   -   - 
14 Robert O A O A O P V P P  -   -   -   - 
15 Ross O V O O A V A P O  -   -   -   - 
16 Smerilli O P P A A O O A O  -   -   -   - 
17 Kevin V O A A O A V O V  -   -   -   - 
18 Beam P O O O O A V O P  -   -   -   - 
19 Linger O A O P A O O A A  -   -   -   -



Chris Saxon
February 21, 2018 - 2:24 pm UTC

Seems so, may as well use regular pivot:

with rws as (
  select e.emp_id, e.last_name, extract(day from cal_date) dy, s.stat_name
  from   cj_emp_cal EC
  join   cj_cal C
  on     c.cal_id = ec.cal_id
  join   cj_emp e
  on     e.emp_id = ec.emp_id
  join   ( 
    select stat_id, substr(stat_name,1,1) as stat_name from cj_status 
  ) s
  on     s.stat_id = ec.status
  where  TRUNC(cal_date,'MM') = date'2018-02-01' 
  and    status is not null
)
  select * from rws
  pivot (
    min(stat_name) for dy in (1,2,3,4,5) --etc.
  );

EMP_ID   LAST_NAME    1   2   3   4   5   
       3 Setchell     A   O   O   A   O   
      17 Kevin        P   A   O   O   A   
       8 Franklin     V   O   A   A   P   
      13 Brent        P   A   P   O   P   
      16 Smerilli     O   A   O   A   P   
       9 Perry        O   V   P   A   O   
      11 Jodilynn     O   O   O   V   O   
      15 Ross         A   O   O   O   A   
      19 Linger       P   P   A   A   P   
       1 Hand         O   P   O   A   O   
      12 Mackenzie    A   V   O   A   A   
       6 Gillespie    A   O   O   V   O   
       7 Westerterp   P   A   O   V   A   
      10 Matt         O   A   O   P   O   
      14 Robert       A   O   O   V   O   
       4 Espada       A   V   P   O   P   
       5 Santiago     P   A   V   O   P   
      18 Beam         O   O   O   V   O   
       2 Bulthuis     V   A   O   O   A

AWESOME!

Gordan, February 23, 2018 - 8:05 pm UTC

Hey Chris,

I ended up 'bruteforcing' it with cases, but I will definitely switch it to the pivot. It is just what I needed!

Thank you for the introduction to pivots in Oracle SQL!
Thank you for being such a wonderful team!

Gordan
Connor McDonald
February 24, 2018 - 1:19 am UTC

Glad we could help

In, out, and lunch break time

kuttikumar, April 23, 2019 - 1:21 pm UTC

EMP_ID LAST_NAME 1 2 3 4 5 6 7 8 9 28 29 30 31
1 Hand A P O V A O A A A - - - -
2 Bulthuis A V P A O O A V A - - - -
3 Setchell O O O A O P O O O - - - -
4 Espada A A A A A O A O O - - - -
5 Santiago V A P O O A V A O - - - -
6 Gillespie V A V A O A P A A - - - -
7 Westerterp O A A V O O P P O - - - -
8 Franklin P V A A O P O O A - - - -
9 Perry O O A A O O O P P - - - -
10 Matt A O P A A V P O P - - - -
11 Jodilynn O P O O P P O O P - - - -
12 Mackenzie O P O A A A P V O - - - -
13 Brent O O A P O O P P A - - - -
14 Robert O A O A O P V P P - - - -
15 Ross O V O O A V A P O - - - -
16 Smerilli O P P A A O O A O - - - -
17 Kevin V O A A O A V O V - - - -
18 Beam P O O O O A V O P - - - -
19 Linger O A O P A O O A A - - - -

i want (P) in time, out time and lunch break time details.

for example
EMP_ID LAST_NAME 1 2 3 4 5 6 7 8 9 28 29 30 31
1 Hand P O V A O A A A ..........
in 9.00 Am
breakin 1.30 PM
breakout 2.30 PM
out 6.00 PM

Chris Saxon
April 24, 2019 - 1:38 pm UTC

And how exactly would we infer those details from the source data?
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.