Home » SQL & PL/SQL » SQL & PL/SQL » Complex Timeline / Calendar Question (Oracle 10g)
| Complex Timeline / Calendar Question [message #420402] |
Fri, 28 August 2009 19:40  |
DeReP
Messages: 2
Registered: August 2009
|
Junior Member |
|
|
Hello experts.
I'm having a bit ofa problem getting my head clear for this query.
I'm trying to build a timeline in wich columns are week numbers, and rows represent project phases.
Now for each project I have up to 7 phases, each with a certain date.
The thing is, I have phases stored as rows in another table, so, for each project there are 7 rows with each phase in another table (Wich references the project by the project ID, all project phases are stored there)
The point of this is to have a list of projects with its phases correctly placed under the corresponding week number for each phase.
So for instance I have this project table:
PROJECT_ID | PROJECT_NAME
001 | Project 1
002 | Project 2
And then I have this phases table:
PROJECT_ID | PHASE_NAME | DATE
001 | Phase 1 | 01-03-2009
001 | Phase 1 | 20-03-2009
001 | Phase 1 | 13-04-2009
001 | Phase 1 | 11-05-2009
001 | Phase 1 | 27-05-2009
001 | Phase 1 | 22-06-2009
001 | Phase 1 | 25-06-2009
002 | Phase 1 | 13-05-2009
002 | Phase 2 | 30-05-2009
002 | Phase 3 | 15-06-2009
002 | Phase 4 | 28-06-2009
002 | Phase 5 | 11-07-2009
002 | Phase 6 | 18-07-2009
002 | Phase 7 | 25-08-2009
And the result should be like this:
Project | 13 | 14 | 15 | 16 |
001 | Phase 1| |Phase 3 | |
Get the idea?
I was trying a calendar like approach but Im having trouble decifering how to build de decodes correspondly.
Any pointers are great help!
Sorry for my english, and thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Complex Timeline / Calendar Question [message #420410 is a reply to message #420402] |
Sat, 29 August 2009 01:33  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
drop table phases;
create table phases (id varchar2(3), phase varchar2(3), dt date);
insert into phases values ('001', 'Ph1', to_date('01-03-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('20-03-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('13-04-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('11-05-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('27-05-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('22-06-2009', 'DD-MM-YYYY'));
insert into phases values ('001', 'Ph1', to_date('25-06-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph1', to_date('13-05-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph2', to_date('30-05-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph3', to_date('15-06-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph4', to_date('28-06-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph5', to_date('11-07-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph6', to_date('18-07-2009', 'DD-MM-YYYY'));
insert into phases values ('002', 'Ph7', to_date('25-08-2009', 'DD-MM-YYYY'));
commit;
SQL> desc phases
Name Null? Type
-------------------------------- -------- ----------------------
ID VARCHAR2(3 CHAR)
PHASE VARCHAR2(3 CHAR)
DT DATE
SQL> select * from phases order by 1, 3;
ID PHA DT
--- --- -----------
001 Ph1 01-MAR-2009
001 Ph2 20-MAR-2009
001 Ph3 13-APR-2009
001 Ph4 11-MAY-2009
001 Ph5 27-MAY-2009
001 Ph6 22-JUN-2009
001 Ph7 25-JUN-2009
002 Ph1 13-MAY-2009
002 Ph2 30-MAY-2009
002 Ph3 15-JUN-2009
002 Ph4 28-JUN-2009
002 Ph5 11-JUL-2009
002 Ph6 18-JUL-2009
002 Ph7 25-AUG-2009
14 rows selected.
SQL> select id,
2 max(decode(to_char(dt,'WW'), 9,phase)) " 9",
3 max(decode(to_char(dt,'WW'),10,phase)) " 10",
4 max(decode(to_char(dt,'WW'),11,phase)) " 11",
5 max(decode(to_char(dt,'WW'),12,phase)) " 12",
6 max(decode(to_char(dt,'WW'),13,phase)) " 13",
7 max(decode(to_char(dt,'WW'),14,phase)) " 14",
8 max(decode(to_char(dt,'WW'),15,phase)) " 15",
9 max(decode(to_char(dt,'WW'),16,phase)) " 16",
10 max(decode(to_char(dt,'WW'),17,phase)) " 17",
11 max(decode(to_char(dt,'WW'),18,phase)) " 18",
12 max(decode(to_char(dt,'WW'),19,phase)) " 19",
13 max(decode(to_char(dt,'WW'),20,phase)) " 20",
14 max(decode(to_char(dt,'WW'),21,phase)) " 21",
15 max(decode(to_char(dt,'WW'),22,phase)) " 22",
16 max(decode(to_char(dt,'WW'),23,phase)) " 23",
17 max(decode(to_char(dt,'WW'),24,phase)) " 24",
18 max(decode(to_char(dt,'WW'),25,phase)) " 25",
19 max(decode(to_char(dt,'WW'),26,phase)) " 26",
20 max(decode(to_char(dt,'WW'),27,phase)) " 27",
21 max(decode(to_char(dt,'WW'),28,phase)) " 28",
22 max(decode(to_char(dt,'WW'),29,phase)) " 29",
23 max(decode(to_char(dt,'WW'),30,phase)) " 30",
24 max(decode(to_char(dt,'WW'),31,phase)) " 31",
25 max(decode(to_char(dt,'WW'),32,phase)) " 32",
26 max(decode(to_char(dt,'WW'),33,phase)) " 33",
27 max(decode(to_char(dt,'WW'),34,phase)) " 34"
28 from phases
29 group by id
30 order by id
31 /
ID 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
001 Ph1 Ph2 Ph3 Ph4 Ph5 Ph6 Ph7
002 Ph1 Ph2 Ph3 Ph4 Ph5 Ph6 Ph7
2 rows selected.
Regards
Michel
[Updated on: Sat, 29 August 2009 01:33] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Oct 13 17:31:55 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
