SQL Help

[Nick]

Hello,

I have a Transaction history table that records transactions and the
information that goes along with them line by line. The records I have are as
follows

ID (Primary Key)
BEGDATTIM
ENDSTATCD
ENDQUEUE
ENDWORKTYPE
ENDDATTIM

Some sample data would go like this:

ID BEGDATTIM ENDSTATCD ENDQUEUE ENDWORKTYPE ENDDATTIM
1 8:00AM SCANNED START APP
8:15 AM
1 8:30 AM ISSUED NEXT APP
8:45 AM
1 8:50 AM PASSED FINAL APPCWA
9:00 AM

What I'm trying to do is write a query that will add 3 additional fields to
this table: BEGSTATCD, BEGQUEUE, and BEGWORKTYPE. These fields are to contain
whatever the ENDSTATCD, ENDQUEUE, and ENDWORKTYPE fields contained in the
transaction that occured previous to it (time wise).

For example I would like to return as the result of my query (Say just for
STATCD):

ID BEGDATTIM ENDSTATCD BEGSTATCD
1 8:00 AM SCANNED SCANNED
1 8:30 AM ISSUED SCANNED
1 8:50 AM PASSED ISSUED

For the first record, as you can see I would like the BEGSTATCD = ENDSTATCD.
The same applies to WORKTYPE and QUEUE.

Anyone have any ideas on how I can do this??? I KNOW this can be done but I
am completely stumped.....Thank you so much!!!

Nick

 

[Nick]

smartin - I'm still having issues....here is how the history is loaded into
the table:

ID ENDWRKTYPE ENDSTATCD
1 APP SCANNED
1 APP ISSUED
1 APP PASSED

I WANT to return:

ID ENDWRKTYPE ENDSTATCD BEGSTATCD
1 APP SCANNED <BLANK>
1 APP ISSUED SCANNED
1 APP PASSED ISSUED

the SQL I have composed:
SELECT
w20_detailinfo.ID,
w20_detailinfo.ENDDATTIM,
w20_detailinfo.ENDUNITCD,
w20_detailinfo.ENDWRKTYPE,
w20_detailinfo.ENDSTATCD,
w20_detailinfo.ENDUSERID,
w20_detailinfo.ENDQUEUECD,
w20_detailinfo.BEGDATTIM,
(SELECT TOP 1 Dupe.ENDSTATCD
FROM
w20_detailinfo AS Dupe
WHERE
Dupe.ID = w20_detailinfo.ID
AND
(Dupe.ENDDATTIM <= w20_detailinfo.BEGDATTIM))
AS
BEGSTATCD
FROM
w20_detailinfo
WHERE
w20_detailinfo.ID = '1'

This SQL returns:
ID ENDWRKTYPE ENDSTATCD BEGSTATCD
1 APP SCANNED SCANNED
1 APP ISSUED SCANNED
1 APP PASSED SCANNED

What am I doing wrong?