Help with Sub-Query

[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

Im ok with producing most of it, however 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!

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cantona]

Hi PHV,

I havent got very far at all, what i came up with is the following. which doesnt quite give me what i want;

SELECT progress_records.person_id,
IIF([STAGE]="AA",[DATE]) As AA,
IIF([STAGE]="AACK",[DATE]) AS AACK,
IIF([STAGE]="INT",[DATE]) AS INT
FROM PROGRESS_RECORDS
GROUP BY person_id,
IIF([STAGE]="AA",[DATE]) As AA,
IIF([STAGE]="AACK",[DATE]) AS AACK,
IIF([STAGE]="INT",[DATE]) AS INT

What i really need is one row per person_id, then columns called AA, AACK, INT, with the date entered under each column. If there is more than one date for a particular stage then i would want to only display the date with the highest sequence number.

Does that make sense??

 

[PHV]

Create a query named, say, getMaxSequence

SELECT Person_ID, Stage, Max(Sequence_Num) AS MaxSequence
FROM Progress_Records
GROUP BY Person_ID, Stage;

Create a query named, say, qryProgress

SELECT A.Person_ID, A.Stage, A.Date
FROM Progress_Records AS A INNER JOIN getMaxSequence AS B
ON A.Person_I = B.Person_ID AND A.Stage = B.Stage AND A.Sequence_Num = B.MaxSequence;

And now, your final query:

TRANSFORM Max([Date]) AS MaxDate
SELECT Person_ID
FROM qryProgress
GROUP BY Person_ID
PIVOT Stage;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cantona]

Hi PHV,

Thanks for your help. Ive managed to create what i need based on your queries. Got to go and translate into oracle sql as well now!!

Thanks!

 

[PHV]

Why asking in the JetSQL forum if you want Oracle sql ?

 

[cantona]

I actually needed both! we operate two systems here. one group of people will require the query in oracle. The rest will use access! strange i know!