Previous Record Query

[roelandkim]

Hi all,

I'm in a bit of a bind trying to figure out how to accomplish this query, any help would be appreciated. I have a table called ApprovedCertification. This table contains the following fields:

PK, ParticipantPK, CertificationDate, CertificationType

I want to be able to compare the current CertificationDate to the previous CertificationDate for a ParticipantPK. In this table a Participant may have multiple records in the ApprovedCertification table.

I need to take this data:

PK, ParticipantPK, CertificationDate, CertificationType
1, 12, 1/2/2004, Annual
4, 12, 2/3/2003, Annual
5, 12, 1/18/2002, Annual
9, 12, 1/25/2001, Annual

And break it down into something like this:

PK, ParticipantPK, CertificationDate, CertificationType, PreviousCertification
1, 12, 1/2/2004, Annual, 2/3/2003
4, 12, 2/3/2003, Annual, 1/18/2002
5, 12, 1/18/2002, Annual, 1/25/2001
9, 12, 1/25/2001, Annual, NoPreviousCert

Any help pointing me in the right direction would be much appreciated.

Thanks,
Roeland Kim

 

[ByteMyzer]

The following query should give you what you're looking for:

SELECT AC1.PK, AC1.ParticipantPK, AC1.CertificationDate, AC1.CertificationType, (SELECT MAX(AC2.CertificationDate) FROM ApprovedCertification AS AC2 WHERE AC2.ParticipantPK=AC1.ParticipantPK AND AC2.CertificationDate<AC1.CertificationDate AS PreviousCertification
FROM ApprovedCertification AS AC1;

 

[roelandkim]

Perfect! Thanks a ton that saved me a lot of trouble.

Thank you,
Roeland Kim

 

[roelandkim]

It doesn't seem to be working correctly. I'm not sure if it is becuase of multiple participants, but here are some actual values from the table.

PK fkosParticipant ActionType EffectiveDate
181314 105999 Other 6/1/2003
164655 105999 Annual 4/1/2003
153307 105999 New 11/1/2002
210000 106009 Annual 4/1/2004
162679 106009 Annual 4/1/2003
119005 106009 Annual 4/1/2002
211058 106010 Annual 3/1/2004
163077 106010 Annual 3/1/2003
100022 106010 Annual 11/1/2001
206500 106011 Annual 3/1/2004
155130 106011 Annual 2/1/2003
108947 106011 New 6/1/2000

Using your query modified slightly for field names I came up with this:

SELECT AC1.PK, AC1.fkosParticipant, AC1.EffectiveDate, AC1.ActionType, (SELECT MAX(AC2.effectivedate) FROM Tbl_ApprovedCertification AS AC2 WHERE AC2.fkosParticipant=fkosParticipant AND AC2.pk<AC1.pk AS PreviousCert
FROM Tbl_ApprovedCertification AS AC1
ORDER BY AC1.fkosParticipant;

The problem is it doesn't seem to isolate the correct date, I get this as a result:

PK fkosParticipant EffectiveDate ActionType PreviousCert
181314 105999 6/1/2003 Other 4/1/2003
164655 105999 4/1/2003 Annual 4/1/2003
153307 105999 11/1/2002 New 4/1/2002
210000 106009 4/1/2004 Annual 3/1/2004
162679 106009 4/1/2003 Annual 2/1/2003
119005 106009 4/1/2002 Annual 11/1/2001
211058 106010 3/1/2004 Annual 4/1/2004
163077 106010 3/1/2003 Annual 4/1/2003
100022 106010 11/1/2001 Annual
206500 106011 3/1/2004 Annual 6/1/2003
155130 106011 2/1/2003 Annual 11/1/2002
108947 106011 6/1/2000 New 11/1/2001

What I was hoping to achieve is something along these lines:

PK fkosParticipant EffectiveDate ActionType PreviousCert
181314 105999 6/1/2003 Other 4/1/2003
164655 105999 4/1/2003 Annual 11/1/2002
153307 105999 11/1/2002 New No Previous
210000 106009 4/1/2004 Annual 4/1/2003
162679 106009 4/1/2003 Annual 4/1/2002
119005 106009 4/1/2002 Annual No Previous
211058 106010 3/1/2004 Annual 3/1/2003
163077 106010 3/1/2003 Annual 11/1/2001
100022 106010 11/1/2001 Annual No Previous
206500 106011 3/1/2004 Annual 2/1/2003
155130 106011 2/1/2003 Annual 6/1/2000
108947 106011 6/1/2000 New No Previous

In this result, for Participant 105999, each record grabs the previous records date for that participant, and if no previous record is found, it give a result of "No Previous" or something to that effect.
Once again thanks for any help.

Roeland Kim

 

[roelandkim]

Anyone who could point me in the right direction?

 

[Jon K]

SELECT AC1.PK, AC1.fkosParticipant, AC1.EffectiveDate, AC1.ActionType,
Nz((SELECT MAX(AC2.EffectiveDate) FROM Tbl_ApprovedCertification AS AC2 WHERE AC2.fkosParticipant =AC1.fkosParticipant AND AC2.EffectiveDate < AC1.EffectiveDate),"No Previous") AS PreviousCert
FROM Tbl_ApprovedCertification AS AC1;


Note
When you put "No Previous" in PreviousCert, PreviousCert becomes a text field.

 

[roelandkim]

Thanks a ton Jon K, it works perfectly.

Roeland Kim