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 Go to next message
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 #420403 is a reply to message #420402] Fri, 28 August 2009 19:56 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Get the idea?
no

why does Project 001 Phase 1 appear under a column labeled "13"?
from where did Project 001 Phase 3 materialize?

>I was trying a calendar like approach
Why?
What determines the starting & ending "dates" that get reported?
Re: Complex Timeline / Calendar Question [message #420407 is a reply to message #420402] Fri, 28 August 2009 23:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Can you explain what is the reason to select only 13,14,15 and 16.
What about other days?

Quote:
I'm trying to build a timeline in wich columns are week numbers...

does not match with required output.

regards,
Delna
Re: Complex Timeline / Calendar Question [message #420408 is a reply to message #420402] Sat, 29 August 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Complex Timeline / Calendar Question [message #420410 is a reply to message #420402] Sat, 29 August 2009 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Complex Timeline / Calendar Question [message #420446 is a reply to message #420402] Sat, 29 August 2009 18:33 Go to previous messageGo to next message
DeReP
Messages: 2
Registered: August 2009
Junior Member
Michael that was exactly what I needed, Im not sure why I always try to approach with a complex query. Thanks, and I'll try to work with your example!
Re: Complex Timeline / Calendar Question [message #420457 is a reply to message #420446] Sat, 29 August 2009 23:55 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For your next question, please post the same kind of test case we will then be able to work with.

Regards
Michel
Previous Topic: Function won't return correct results
Next Topic: How to insert records in Mutilevel nested table
Goto Forum:
  


Current Time: Thu Sep 29 09:01:44 CDT 2016

Total time taken to generate the page: 0.09810 seconds