iggydarsa
Well-known Member
- Joined
- Jun 28, 2005
- Messages
- 1,780
- Office Version
- 2019
- Platform
- Windows
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
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