Decode fields with dates
Posted on 2004-08-30
Sorry if this is two much of a newbie question........yes i am only started with oracle and sql.
If its in the wrong area then please let me know and I am sorry for the mixup
I have created the following script with the intent to retrieve informaton relating to date periods.
As you can see from the script below after decode;
Basically if JO.DATE_FROM does not have a value (DATE format or null) then check the JO.DATE_TO field and if its null as well place in Vacant to the JOB_STATUS field otherwise check the JO.DATE_TO field, now its a little difficult..........
If the JO.DATE_FROM does have a date ( eg; 16/05/2003 )and JO.DATE_TO field is null then add occupied to the JOB_STATUS field, however if the JO.DATE_TO is before sysdate (eg; last month) then add vacant to the JOB_STATUS field.
As you can see J.JOB_ID is the primary key........so one key may have two entries eg; the first one is from 01/01/2004 to 31/05/2004 and the second with the same key may be as follows 01/06/2004 to 31/07/2004 (both JOB_STATUS field's should show vacant for the 2 entries). Would it be possible to filter the first one out and keep the second entry or in this case the latest one?........The J.JOB_ID field is a number
The tables are stored on an 9i server.
Select J.JOB_ID, J.CAT_CD, J.DATE_FROM JOB_DATE_FROM, J.ESTABL_PLAN_ONLY_FLAG, J.FUNC_ORG_CHART_FLAG, J.HEAD_OF_ORG_UNIT_FLAG, J.MGMT_JOB_FLAG, J.ORG_CHART_FLAG, J.SENS_DATE_FROM, J.SENS_JOB_FLAG,
U.ACR_COMPL, JO.DATE_FROM OCCP_DATE_FROM, JO.DATE_TO OCCP_DATE_TO,
decode(JO.DATE_FROM, null, 'VACANT' , 'OCCUPIED') as JOB_STATUS,
From SP2_JOBS J,
Where J.DATE_TO is null
AND J.JOB_ID = JO.JOB_ID (+)
AND JO.PER_ID = P.PER_ID (+)
AND J.OU_ID = U.OU_ID
AND U.ACR_COMPL LIKE 'RTD%'
AND (U.DATE_TO is null OR U.DATE_TO >= sysdate)
AND (JO.DATE_FROM is null or JO.DATE_FROM <= sysdate)
AND J.DATE_FROM is not null
Order by U.ACR_COMPL
Thanks very much in advance and I hope that someone may be able to help.