Stuck producing a query that needs sub queries

[cantona]

Hi

I have a database table that logs the progress of a persons application form from receiving the form in the office through to attending an interview. What I would like to do is produce a query that will show me the dates when each stage was reached and the number of days between the stages so that I can monitor service standards. For example, acknowledgment of receiving a form should be sent within 7 days of receiving the form. The data is held in a table like follows;

Sequence_Num, Stage, Date, Person_ID
1 AA 20/01/2008 231
2 AACK 25/01/2008 231
3 AACK 23/01/2008 231
4 INT 27/01/2008 231

What I would like to do is group the records by person_ID, then display the date each stage was reached. Like follows;

Person_id AA AACK INT
231 20/01/2008 23/01/2008 27/01/2008

The problem i have is that there may be cases where there are two entries for a particular stage. AACK in the example above. It may be that it is logged on the system that an acknowledgment was sent on 25th Jan, this however might have been entered in error. The correct date was the 23rd. I would only want to show the one entry in my query. If there are two rows with the same stage, code ‘AACK’ for example, then I would like to display the date with the highest sequence number. This is where I get a bit stuck! Can anyone help?

I guess I need to include a sub query that displays the date for the max of sequence number for that stage. Im struggling to come up with anything though

 

[Dagon]

You're really trying to do two things:

a) denormalize/pivot the table to convert rows to columns
b) select maximum date within each stage

For that, you'd need something like:

select app.person_id, app.stage_date, ack.stage_date
from
(select a1.person_id, a1.stage_date from application a1
where stage_date = 
(select max(stage_date)
from application a2
where a2.person_id = a1.person_id
and   a2.stage = a1.stage)
and stage = 'AA') app,
(select a1.person_id, a1.stage_date 
from application a1
where stage_date = 
(select max(stage_date)
from application a2
where a2.person_id = a1.person_id
and   a2.stage = a1.stage)
and stage = 'AACK') ack
where ack.person_id(+) = app.person_id

Just extend it to do all the other stages (INT etc). You could also use rank/row_number functions to get the highest date instead of a subquery.

 

[cantona]

Hi Dagon,

Thanks for your help, thats nearly what im after! Its slightly different in that i dont especially want the max of stage_date. If the date has been edited retrospectively then the date i want may be earlier. What i need to display is the stage_date that goes with the highest sequence number. For Example;

Person_id Stage Stage Date Sequence
231 AA 25/01/2008 1
231 AACK 27/01/2008 2
231 AACK 26/01/2008 3

I would want to return,

person_id AA AACK
231 25/01/2008 26/01/2008

Does that make sense?

 

[Dagon]

Same principle:

select app.person_id, app.stage_date, ack.stage_date
from
(select a1.person_id, a1.stage_date from application a1
where sequence_number = 
(select max(a2.sequence_number)
from application a2
where a2.person_id = a1.person_id
and   a2.stage = a1.stage)
and stage = 'AA') app,
(select a1.person_id, a1.stage_date 
from application a1
where sequence_number = 
(select max(a2.sequence_number)
from application a2
where a2.person_id = a1.person_id
and   a2.stage = a1.stage)
and stage = 'AACK') ack
where ack.person_id(+) = app.person_id

 

[cmmrfrds]

Is this really an Oracle SQL question?
How does it relate to this thread701-1446243?
Please check rules on cross posting.

 

[cantona]

Hi Cmmrfrds,

It is indeed an Oracle SQL question. As it happens i have to produce a similar thing in access as well. The majority of people that need the info use a system that requires queries to be written as pass through queries, hence the oracle question. The others use access where the sql and code is obviously different and not always easily translated. But, thanks for your concern. your obviously very busy.....

 

[cantona]

Hi Dagon,

Thanks for your help. Ive managed to accomplish what i was after by investigating the use of RANK(). I produced a query that ranked each row using the following;

RANK() OVER (PARTITION BY person_id,app_num,stage ORDER BY sequence DESC) As Rnk. I then placed criteria on the report to only show records where Rnk=1

I then produced a second query to pivot the information using DECODE,

MAX(DECODE(stage,'AA',to_char(date,'dd/mm/yyyy'),NULL)) AA

The problem i have is that ive not actually had any form of training in oracle sql. Ive just picked up bits as ive gone along. I tend to struggle with queries that require sub queries etc..and wasnt very familiar with using RANK().

Thanks for your suggestions