SQL statement needed ASAP :(

[iggydarsa]

I have to run a query but not sure how to do it...

I have a table called ProcessTable and the table looks like this:

ProcessID......ElementID.....QueueID.....StatusID....Date
----------------------------------------------------------------------
.....1................1001..............10..............1..........1/1/2000
.....2................1001..............10..............2..........1/1/2000
.....3................1001..............10..............3..........1/2/2000

.....4................1002..............10..............1..........3/3/2000
.....5................1002..............10..............2..........3/4/2000
.....6................1002..............20..............1..........3/7/2000

.....7................1003..............30..............1..........5/5/2000
.....8................1003..............30..............3..........6/6/2000

.....9................1004..............50..............1..........7/7/2000
....10...............1004..............10..............1..........8/8/2000
....11...............1004..............10..............3..........9/9/2000


ProcessID: auto-incremented
StatusID: 1-Not Started, 2- In Progress, 3-Closed

In my query I want to see all the ElementIDs that QueueID is 10 and are Closed (StatusID=3) or moved to another queue (ie: line 5 -> the queue changed from 10 to 20).

Also I want to see the beginning date and the end date...
Beginning date is the first action that QueueID=10 and
Ending that is either QueueID=10 and StatusID=3 or QueueID WAS 10 and sent to another queue;

so it will look like this:
ElementID...........StartDate..........EndDate
--------------------------------------------------
1001..................1/1/2000.............1/2/2000
1002..................3/3/2000.............3/7/2000
1004..................8/8/2000.............9/9/2000


1003 is not in the list because QueueID is not 10
1004's StartDate is the first appearance of 10 in QueueID


At this point I am clueless, any help is greatly appreciated

 

[VicRauch]

What I called a rule change is that you can have the same element "go through the system" at least twice, rather than just once as your data showed in the first request. So, with this explaination, my question still stands. What else can be different than we currently know. For example, can the same elementID "go through the system" more than twice? Or ???

 

[iggydarsa]

Yes Vic, ElementID can go through the system as many as the user wants. Besides that, as far as I know for now, there are no rule changes.

Thanks

 

[giacomo]

can an element enter queue 10 with a status > 1 or will it always enter at 1?

assuming it will always start at statusID = 1 this should work for you:

SELECT 
PT1.ElementID
, PT2.Date AS bgn_dt
, Min(IIf([PT1].[Date]>[pt2].[date],[pt1].[date],Null)) AS end_dt
FROM 
ProcessTable AS PT1 INNER JOIN ProcessTable AS PT2 ON PT1.ElementID = PT2.ElementID
WHERE (((PT1.QueueID)=10) AND ((PT1.StatusID)=3) AND ((PT2.QueueID)=10) AND ((PT2.StatusID)=1)) OR (((PT1.QueueID)<>10) AND ((PT2.QueueID)=10) AND ((PT2.StatusID)=1))
GROUP BY PT1.ElementID, PT2.Date;

hth,
Giacomo

 

[iggydarsa]

status always starts with 1, so as you said it should work

thank you very much