Subquery Help

[Nick]

Hello - I have a table that I want to write a subquery to return a value
froma previous record. I have the following data

ID ENDWRKTYPE ENDSTATCD BEGDATTIM ENDDATTIM
1 APP SCANNED 8:00 AM 8:15 AM
1 APP ISSUED 8:17 AM 8:30 AM
1 APP PASSED 8:45 AM 8:55 AM

I WANT to return:

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

(with the ENDDATTIM and BEGDATTIM being the same)

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

Can somebody help me finish off this query???? It is much appreciated!!!

Thanks!!

Nick

 

[Lord Kelvan]

as a note there is no such thing as ORDER in database records also you
cannot do that in a sub query you need to actually have two different
queries and then compare them together

what would be a better note is are you wanting to have it display
scanned wherever it is issued and issued wherever it is passed and
blank wherever it is scanned or can it sometimes be scanned where it
is passed and plank where it is issued

ie

will is always be
ID ENDWRKTYPE ENDSTATCD BEGSTATCD
1 APP SCANNED <BLANK>
1 APP ISSUED SCANNED
1 APP PASSED ISSUED

or can it also be

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

please tell me ebcause there may be an eaiser way if you are saying
just the top option

 

[Nick]

The top option would only be correct.

To try and further clarify, I am dealing with a historical table of
transactions. BEGSTATCD stands for "Begin Status Code" which is the status
the transaction is in prior to the current transaction. ENDSTATCD is "End
Status Code" which the is the status after the current trasnaction.

In the data I am provided, I am given the ENDSTATCD. What I am trying to
display is the BEGSTATCD (which is defined as the ENDSTATCD in the
transaction that occured previously) in another column. How I am trying to
get that is by taking the Max ENDDATTIM from the record before the current
record and pulling the ENDSTATCD from the previous record as the BEGSTATCD
for the current record (hope this makes sense).

If it takes two queries to do this, I am fine with that....as long as I get
the correct results.

Thanks for your help,
Nick

 

[Lord Kelvan]

mmm i tested your query with some test data based on what you gave me
and the problem isnt what you think

your query works fine for all records that are not the first group

id endwrktype endstatcd begdattim enddattim
1 app SCANNED 08:00 a.m. 08:15 a.m.
1 app ISSUED 08:17 a.m. 08:30 a.m.
1 app PASSED 08:45 a.m. 08:55 a.m.
2 app PASSED 09:11 a.m. 09:12 a.m.
2 app ISSUED 09:13 a.m. 09:14 a.m.
2 app SCANNED 09:00 a.m. 09:10 a.m.

SELECT w20_detailinfo.id, w20_detailinfo.endwrktype,
w20_detailinfo.endstatcd, w20_detailinfo.begdattim,
w20_detailinfo.enddattim, (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
ORDER BY w20_detailinfo.id, w20_detailinfo.begdattim;

returned

id endwrktype endstatcd begstatcd
1 app SCANNED
1 app ISSUED SCANNED
1 app PASSED SCANNED
2 app SCANNED
2 app ISSUED SCANNED
2 app PASSED ISSUED

in a effort of trying to diaganose the problem i came accrose this the
problem seems to be that they are in squentional order if you mees
with the order of the records and then use order by in the query to
order the records the query works fin enow this makes no sence what do
ever to me in why this is happening the more i muddle up the records
(ie the time for oen records is inputed in the databse after the time
in another record) the more it works ok after re reading your query
this is the problem

BEGSTATCD: (SELECT TOP 1 Dupe.ENDSTATCD FROM w20_detailinfo AS Dupe
WHERE Dupe.ID = w20_detailinfo.ID AND (Dupe.ENDDATTIM <=
w20_detailinfo.BEGDATTIM))

because the top 1 record is time is less than the begin time of the
other ones

8:15 is less than 8:17 and 8:45

so if the record that si SCANNED is moved after the other records it
works which explains the problem

note i am sorry for the chatter i used this to make notes as i was
working on it

because the gaps in the time can be random in length a second query
wont work i was looking for a stable control source to match the two
sub queries up but i couldnt find one

potentionally you could just do

BEGSTATCD: Switch([endstatcd]="ISSUED","SCANNED",
[endstatcd]="PASSED","ISSUED")

which would simply put the value scanned where the value issued exists
and issued where passed exists

i am sorry i cannot think of another method without a stable control
source

 

[Nick]

Lord Kelvan,

I can't hardcode a switch for those statuses. The example data I gave you is
just a small set of the table I am working with. There are way too many
different ENDSTATCD values for me to hard code.

The ID number is the only control source that can be used to match. Because
the BEGDATTIM occurs a little after the ENDDATTIM chronologically speaking
(but not necessarily at the saem time) you can't match on that
unforunately.....so what I was doing was trying to take the MAX value of the
ENDDATIM that is still LESS OR EQUAL TO the current record's BEGDATTIM, and
return the ENDSTATCD for that is the BEGSTATCD for the current record. It
makes sense to me logically speaking, but I just can't get the results....it
keeps just returning the very first ENDSTATCD of 'SCANNED'. It's like the MAX
is working as a MIN or something...

mmm i tested your query with some test data based on what you gave me
and the problem isnt what you think

your query works fine for all records that are not the first group

id endwrktype endstatcd begdattim enddattim
1 app SCANNED 08:00 a.m. 08:15 a.m.
1 app ISSUED 08:17 a.m. 08:30 a.m.
1 app PASSED 08:45 a.m. 08:55 a.m.
2 app PASSED 09:11 a.m. 09:12 a.m.
2 app ISSUED 09:13 a.m. 09:14 a.m.
2 app SCANNED 09:00 a.m. 09:10 a.m.

SELECT w20_detailinfo.id, w20_detailinfo.endwrktype,
w20_detailinfo.endstatcd, w20_detailinfo.begdattim,
w20_detailinfo.enddattim, (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
ORDER BY w20_detailinfo.id, w20_detailinfo.begdattim;

returned

id endwrktype endstatcd begstatcd
1 app SCANNED
1 app ISSUED SCANNED
1 app PASSED SCANNED
2 app SCANNED
2 app ISSUED SCANNED
2 app PASSED ISSUED

in a effort of trying to diaganose the problem i came accrose this the
problem seems to be that they are in squentional order if you mees
with the order of the records and then use order by in the query to
order the records the query works fin enow this makes no sence what do
ever to me in why this is happening the more i muddle up the records
(ie the time for oen records is inputed in the databse after the time
in another record) the more it works ok after re reading your query
this is the problem

BEGSTATCD: (SELECT TOP 1 Dupe.ENDSTATCD FROM w20_detailinfo AS Dupe
WHERE Dupe.ID = w20_detailinfo.ID AND (Dupe.ENDDATTIM <=
w20_detailinfo.BEGDATTIM))

because the top 1 record is time is less than the begin time of the
other ones

8:15 is less than 8:17 and 8:45

so if the record that si SCANNED is moved after the other records it
works which explains the problem

note i am sorry for the chatter i used this to make notes as i was
working on it

because the gaps in the time can be random in length a second query
wont work i was looking for a stable control source to match the two
sub queries up but i couldnt find one

potentionally you could just do

BEGSTATCD: Switch([endstatcd]="ISSUED","SCANNED",
[endstatcd]="PASSED","ISSUED")

which would simply put the value scanned where the value issued exists
and issued where passed exists

i am sorry i cannot think of another method without a stable control
source

 

[Lord Kelvan]

ok let me show you step by step why it is logically wrong

you have these times

8:00 a.m. 08:15 a.m.
8:17 a.m. 08:30 a.m.
8:45 a.m. 08:55 a.m.

you query gets the top 1 record where the end time of record with id 1
is less than the begin time of another record with id 1

so what it says is the top 1 record is 8:15 because it is less than
8:17 and 8:45 your problem is that all three records have the id of 1
it wouldnt be a problem if you had another id field that was completly
unique...

and on that not go into the table and make a new field called statid
or something and make it auto number and then it shoudl auto fill all
the values into that field

you can then use that to compare as a note are all records in the
table in sequential order because that wont work if they are not
becuase all you have to do is say where statid-1 kinda thing

if thevalues are not in sequentional order then you will have to look
at doing some data cleaning before applying the auto number

just do a make table query that orders on the id number and the begin
time then add the auto number field then it should be possible

hope this makes sence

Regards
Kelvan

 

[Lord Kelvan]

i stumbled on a way to create a control source

so you will thenneed two queries to do this

query1


SELECT w20_detailinfo.id, w20_detailinfo.endwrktype,
w20_detailinfo.endstatcd, w20_detailinfo.begdattim,
w20_detailinfo.enddattim, (SELECT Count(id) + 1 FROM w20_detailinfo AS
w20 WHERE w20.begdattim < w20_detailinfo.begdattim and w20.id =
w20_detailinfo.id) AS recordnum
FROM w20_detailinfo
ORDER BY w20_detailinfo.id, w20_detailinfo.begdattim;

save this as w20_detailinfosort

and

query2

SELECT w20_detailinfosort.id, w20_detailinfosort.endwrktype,
w20_detailinfosort.endstatcd, (select endstatcd from
w20_detailinfosort as w20 where w20_detailinfosort.recordnum-1 =
w20.recordnum and w20_detailinfosort.id = w20.id) AS begstatcd
FROM w20_detailinfosort;

save this as whatever this is the query you run

query 1 addes a control course and then query2 uses that control
source to match the records up

how this is what you want

Regards
Kelvan

 

[Lord Kelvan]

i stumbled on a way to create a control source

so you will thenneed two queries to do this


query1


SELECT w20_detailinfo.id, w20_detailinfo.endwrktype,
w20_detailinfo.endstatcd, w20_detailinfo.begdattim,
w20_detailinfo.enddattim, (SELECT Count(id) + 1 FROM w20_detailinfo
AS
w20 WHERE w20.begdattim < w20_detailinfo.begdattim and w20.id =
w20_detailinfo.id) AS recordnum
FROM w20_detailinfo
ORDER BY w20_detailinfo.id, w20_detailinfo.begdattim;


save this as w20_detailinfosort


and


query2


SELECT w20_detailinfosort.id, w20_detailinfosort.endwrktype,
w20_detailinfosort.endstatcd, (select endstatcd from
w20_detailinfosort as w20 where w20_detailinfosort.recordnum-1 =
w20.recordnum and w20_detailinfosort.id = w20.id) AS begstatcd
FROM w20_detailinfosort;


save this as whatever this is the query you run


query 1 addes a control source and then query2 uses that control
source to match the records up


hope this is what you want


Regards
Kelvan