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

 

[smartin]

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

Hi Nick,

One way uses a subquery. This is untested:

SELECT A.BEGDATTIM, A.ENDSTATCD,
(
SELECT NZ(LAST(B.ENDSTATCD),A.ENDSTATCD)
FROM Nick B
WHERE B.BEGDATTIM < A.BEGDATTIM
ORDER BY B.BEGDATTIM
) AS BEGSTATCD
FROM Nick A
ORDER BY A.BEGDATTIM;

Repeat the subquery for other metrics.

The other way involves adding the table twice to a query and creating a
join on BEGDATTIM. Then you edit the SQL so that instead of something like
ON A.BEGDATTIM = B.BEGDATTIM
you change it to be
ON A.BEGDATTIM > B.BEGDATTIM


Allen Browne has lots of good information on subqueries:
http://www.allenbrowne.com/subquery-01.html

You can also google "non equi join" for information on the latter method.

Hope this helps!

 

[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?

 

[smartin]

Hi Nick, see inline

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))

/* This looks suspicious. '<=' will match
ENDDATTIM and BEGDATTIM did you mean '<' ? */

AS
BEGSTATCD
FROM
w20_detailinfo
WHERE
w20_detailinfo.ID = '1'

Also, you are not handling nulls here, so the first row 'SCANNED' will
be dropped if you adopt my suggestion above.