Home » SQL & PL/SQL » SQL & PL/SQL » 360-day overview
360-day overview [message #247130] Sun, 24 June 2007 10:29 Go to next message
nightfly
Messages: 3
Registered: June 2007
Location: Sweden
Junior Member
Hello Forum!

Here's one of the problems that make me (being a newbie) go nuts:

I need to create a 360-day overview over all employees and the work order numbers they are scheduled to for each day of a 360 day period, starting from a certain date.

The tables and columns i have are:

table              columns
-----------------------------------
employee           emp_no, name, org_code
work_order_role    wo_no, sign, date_from, date_to
calendar_tab       account_date (unsure if I need this, though)


data types are:
emp_no, name, org_code: varchar2
wo_no, sign: varchar2
date_from, date_to: date
account_date: varchar2

data relation: employee.emp_no is the same as work_order_role.sign.
Database is 10g.


Now, I would like the result of the query look like this:

emp_no  name  org_code  07-11-01  07-11-02  07-11-03 07-11-03 ...
-----------------------------------------------------------------
001     aaa   563       555       555       555      777      ...
002     bbb   571       333       333                         ...
003     ccc   552                                             ...
004     ddd   563       111       222       222               ...
005     eee   572       444       222                555      ...
...


Hope, this isn't too cryptic.
All employees must appear in the list, even if they are not scheduled to a work order. The day-columns must contain the work order number an employee is scheduled to between date_from and date_to.
emp_no, name, and the days don't have to be column names. Works even if they make the first row of the result.

Note that I am not allowed to create my own tables in the database. Views, functions, etc are ok.
The data must be accessible from the front-end application via plain SQL.

I thought it should be possible to create a view in the db that is queried from the application by a simple select * from ... with the data in the view being generated at runtime.

I had an idea how to do this but that would mean I'd have to "manually" create a view with 363 columns.
I do suspect that PL/SQL can "do that for me". Unfortunately, as a beginner, I do not have a clue...

Anyone??
Thanks in advance!!
Andreas
Re: 360-day overview [message #247134 is a reply to message #247130] Sun, 24 June 2007 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you really need an ouput of 363 columns?
Is this possible to read that?

You can search for "calendar" and "pivot" in "- SQL & PL/SQL" forum group.
If you have posted a small test case (create table and insert statements) I'd show you how to do it but I'm too lazy to build it.

Regards
Michel
Re: 360-day overview [message #247159 is a reply to message #247130] Sun, 24 June 2007 15:25 Go to previous messageGo to next message
nightfly
Messages: 3
Registered: June 2007
Location: Sweden
Junior Member
Thanks for helping me Michel!

Here comes the test case:

-- create calendar_tab
create table calendar_tab (account_date varchar2(10));


-- create data
declare 
  i integer := 0;
begin
  loop
    insert into calendar_tab (account_date)
      values (to_char(to_date('1995-01-01','yyyy-mm-dd') + i,'yyyy-mm-dd'));
    i := i + 1;
    exit when i > 364;
  end loop;
  commit;
end;


-- create employee
create table employee (emp_no varchar2(4) ,name varchar2(40) ,org_code varchar2(7));


-- create employee data
INSERT INTO employee (emp_no, name, org_code) VALUES ('111', 'Johnson', '563');
INSERT INTO employee (emp_no, name, org_code) VALUES ('222', 'Miller', '554');
INSERT INTO employee (emp_no, name, org_code) VALUES ('333', 'Caine', '572');
INSERT INTO employee (emp_no, name, org_code) VALUES ('444', 'Newman', '551');


-- create work_order_role (I changed the date columns to varchar2)
create table work_order_role (wo_no varchar2(4) ,sign varchar2(4) ,date_from varchar2(10) ,date_to varchar2(10));


-- create work_order_role data
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1008' ,'111' ,'1995-03-20' ,'1995-04-14');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1009' ,'222' ,'1995-06-23' ,'1995-07-03');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1002' ,'333' ,'1995-05-17' ,'1995-06-30');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1007' ,'444' ,'1995-04-10' ,'1995-04-29');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1011' ,'111' ,'1995-05-03' ,'1995-05-22');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1007' ,'222' ,'1995-07-20' ,'1995-07-30');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1011' ,'333' ,'1995-07-02' ,'1995-07-18');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1008' ,'444' ,'1995-05-12' ,'1995-06-01');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1002' ,'111' ,'1995-04-19' ,'1995-04-30');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1007' ,'222' ,'1995-04-28' ,'1995-05-16');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1009' ,'333' ,'1995-03-31' ,'1995-04-22');
INSERT INTO work_order_role (wo_no ,sign ,date_from ,date_to) VALUES ('1009' ,'444' ,'1995-05-03' ,'1995-05-09');


This will generate just a little amount of data. My real-life employee and work_order_role tables are MUCH bigger.

And yes, it has to be that many columns. I need one year to be displayed. The data will the be exported to Excel and juggled around.

Thanks again
Andreas
Re: 360-day overview [message #247209 is a reply to message #247159] Mon, 25 June 2007 01:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  cal as (
    select to_date('1995-01-01','yyyy-mm-dd')+level-1 dt
    from dual 
    connect by level <= 365
  )
select e.emp_no, max(e.name) name, max(e.org_code) org_code, 
       max(decode(c.dt,to_date('1995-01-01','yyyy-mm-dd'),e.org_code)) "05-01-01",
       max(decode(c.dt,to_date('1995-01-02','yyyy-mm-dd'),e.org_code)) "05-01-02",
...
       max(decode(c.dt,to_date('1995-12-30','yyyy-mm-dd'),e.org_code)) "05-12-30",
       max(decode(c.dt,to_date('1995-12-31','yyyy-mm-dd'),e.org_code)) "05-12-31"
from (select e.emp_no, e.name, e.org_code, w.date_from, w.date_to
      from employee e, work_order_role w
      where w.sign = e.emp_no) e
     partition by (emp_no)
     right outer join cal c 
     on (c.dt between e.date_from and e.date_to)
group by e.emp_no
order by 1
/

Regards
Michel
Re: 360-day overview [message #247222 is a reply to message #247130] Mon, 25 June 2007 02:23 Go to previous messageGo to next message
nightfly
Messages: 3
Registered: June 2007
Location: Sweden
Junior Member
Ok, but now there are two issues left:
I still have to write one SELECT-row for one day. Yes, it works, but it's not fun to do.
Secondly, the date column names should be dynamic, as the query must be able to return one year starting with any date.
Re: 360-day overview [message #247227 is a reply to message #247222] Mon, 25 June 2007 02:52 Go to previous message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I still have to write one SELECT-row for one day. Yes, it works, but it's not fun to do.

As you want 363 columns, you have to define 363 fields in your SELECT. No way to workaround this.

Quote:
Secondly, the date column names should be dynamic

Search on the site or on AskTom, I already posted several ways to do it.

Regards
Michel
Previous Topic: Mutating error
Next Topic: dbms_job submit - problem with date
Goto Forum:
  


Current Time: Wed Dec 07 04:54:25 CST 2016

Total time taken to generate the page: 0.06706 seconds