| Complex Query Help [message #371291] |
Thu, 28 September 2000 11:12  |
Rob
Messages: 70
Registered: January 2000
|
Member |
|
|
Hello,
We are going thru a conversion process concerning the way our Projects go thru the Project Lifecyle. What had been 2 Phases, is now 1 Phase. So I need to pick and choose from old Phases to create 1 new Phase.
I would like to create a SELECT statement (or group of sub-SELECTS) that could perform the following. I really don't want to use cursors, and I would like it to be 1 statement....since the query results need to be slapped in Crystal Reports.
Table Structure:
ProjectNumber
Phase
StartDate
EndDate
Possible Phases for a given Project: "A" or "B" or both "A" and "B"
Start Date Criteria:
If Phase "A" exists then
StartDate = Phase "A" StartDate
else
StartDate = Phase "B" StartDate
End Date Criteria:
If Phase "B" exists then
EndDate = Phase "B" EndDate
else
EndDate = Phase "A" EndDate
Thanks for your help! Rob
|
|
|
|
| Re: Complex Query Help [message #371292 is a reply to message #371291] |
Fri, 29 September 2000 02:33  |
Thierry Van der Auwera
Messages: 44
Registered: January 2000
|
Member |
|
|
Hallo Rob,
If I understand your problem correctly, you can doit like this :
SELECT x.projectnumber
,'A' phase
,DECODE(x.Astartdate,NULL,x.Bstartdate,x.Astartdate) startdate
,DECODE(x.Benddate ,NULL,x.Aenddate ,x.Benddate) enddate
FROM (SELECT z.projectnumber
,MAX(DECODE(z.phase,'A',z.startdate,NULL)) Astartdate
,MAX(DECODE(z.phase,'A',z.enddate,NULL)) Aenddate
,MAX(DECODE(z.phase,'B',z.startdate,NULL)) Bstartdate
,MAX(DECODE(z.phase,'B',z.enddate,NULL)) Benddate
FROM the_table z
GROUP BY z.projectnumber
) x
;
Greetings,
Thierry.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
