Query Help

[Nick]

Hello - I have a query that modifies a dataset that I have, and it is ALMOST
working the way I want it, however it needs a small change and I can't figure
out how to do it on my own.

The best way to explain is to show examples of what the table shows now,
what the results of my query are currently, and what I want the results TO
BE.

What the dataset displays originally (EXAMPLE):

ID CRDATTIM BEGDATTIM ENDDATIM ENDSTATCD
1 9999 8:00AM 8:02AM SCANNED
2 9999 8:05AM 8:09AM INDEX
3 9999 8:11AM 8:13AM COMPLETE
4 5555 8:00AM 8:02AM CREATED
5 5555 8:05AM 8:09AM INDEX
6 5555 8:11AM 8:13AM COMPLETE

What my query displays currently (SQL at bottom):

ID CRDATTIM BEGDATTIM ENDDATIM ENDSTATCD BEGSTATCD
2 9999 8:05AM 8:09AM INDEX
SCANNED
3 9999 8:11AM 8:13AM COMPLETE INDEX
5 5555 8:05AM 8:09AM INDEX
CREATED
6 5555 8:11AM 8:13AM COMPLETE INDEX

As you can see, what I am trying to do is create a new field called
BEGSTATCD that grabs the ENDSTATCS from the previous transaction. How I am
grab that is based on the CRDATTIM being equal and then I take ID -1

The problem with this is I lose the first record. I would LIKE to display
the following as a result:

ID CRDATTIM BEGDATTIM ENDDATIM ENDSTATCD BEGSTATCD
1 9999 8:05AM 8:09AM SCANNED (NULL)
2 9999 8:05AM 8:09AM INDEX
SCANNED
3 9999 8:11AM 8:13AM COMPLETE INDEX
4 5555 8:05AM 8:09AM CREATED (NULL)
5 5555 8:05AM 8:09AM INDEX
CREATED
6 5555 8:11AM 8:13AM COMPLETE INDEX

Here is the current SQL:
select w03_w20_hybrid.PRIMDATE AS CRTDATTIM, w03_w20_hybrid.INXFLD01 AS
POLICYNUMBER,dupe.ENDWRKTYPE AS BEGWRKTYPE,w03_w20_hybrid.ENDWRKTYPE AS
ENDWRKTYPE,w03_w20_hybrid.BEGDATTIM AS BEGDATTIM,w03_w20_hybrid.ENDDATTIM AS
ENDATTIM,dupe.ENDSTATCD AS BEGSTATCD,w03_w20_hybrid.ENDSTATCD AS
ENDSTATCD,dupe.ENDQUEUECD AS BEGQUEUECD,w03_w20_hybrid.ENDQUEUECD AS
ENDQUEUECD,w03_w20_hybrid.ENDUSERID AS
USERID,left(w03_w20_hybrid.BEGDATTIM,10) AS CONCAT_DATE from (w03_w20_hybrid
Dupe join w03_w20_hybrid on((dupe.PRIMDATE = w03_w20_hybrid.PRIMDATE))) where
(dupe.id = (w03_w20_hybrid.id - 1))

Can anyone help? I would really appreciate it!!!!

 

[Dale Fye]

One way to approach this is to create a query like:

SELECT Hybrid.CRDATTIM, Hybrid.EndDatim,
(SELECT MAX(ENDDATIM)
FROM yourTable T1
WHERE T1.CRDATTIM = Hybrid.CRDATTIM
AND T1.ENDDATIM < Hybrid.ENDDATIM) as PrevEnd
FROM w03_w20_hybrid as Hybrid
GROUP BY Hybrid.CRDATTIM, Hybrid.EndDatim

This should give you the crdattim, enddatim, and the previous records
enddtim for each record.

Then you could use this query as a link between the two copies of your main
table. Link on crdattim and enddatim to one table, and on crdattim and
PrevEnd to the other table.

HTH

Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.