If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating the difference between multiple dates in multiple queu
I am trying to calculate the total # of days between start and end dates for
multiple events when they go back and forth from different queues. Example: Date - Queue1 Pending Date - Queue1 In Process Date - Queue1 Complete Date - Queue2 Pending Date - Queue2 In Process Date - Queue2 Complete I can calculate if each event has a pending, in process and complete event but there are cases where an item is being tracked and is in process in queue2 then goes to a research queue with an eventy type of in process then back to queue2 as in process. My question is if a queue doesn't have an event of pending to begin an event and an event of complete to end an event, how to calculate the difference between dates if an event only has a pending and in process. I need to calculate the number of days in each queue even if there is only a pending event and an in process event or an in process event and a complete event. The dates are from a tracking system that tracks new documents from receipt through completion. |
#2
|
|||
|
|||
Calculating the difference between multiple dates in multiple queu
I noticed that nobody answered.
Whether or not such was intended, this is a structure question. In order to help on those, we need a clear description of the key items of the situaiton that you want to database. You used a lot of terms (queue, queue1, queue2, event, in process event, pending, complete, which have important YOU-SPECIFIC definitions that you've not told us about. Taking a guess, I'm thinking that you have a table where a record constitutes a document that has come into your system. I'm guessing that you have two main sequences that these run through, and that you are using the word "queue" to loosely refer to that sequence, and not the more traditional definition which would be the set of documents that are "waiting". And that you the dates of three "mileposts" for a document flowing through each of these sequences. Assuming that these are date/time fields, your can calculate differences between any of the entered dates at will, as well as the differences from the current date. If the above guesses are right, you could use the date diff function (on Pending ande Complete fields) to calculate the total time in the Queue for completed items, and it on "Pending" and now() for those where such has not been entered. with an iif function to automatically decide which gets calculated. "Greg" wrote: I am trying to calculate the total # of days between start and end dates for multiple events when they go back and forth from different queues. Example: Date - Queue1 Pending Date - Queue1 In Process Date - Queue1 Complete Date - Queue2 Pending Date - Queue2 In Process Date - Queue2 Complete I can calculate if each event has a pending, in process and complete event but there are cases where an item is being tracked and is in process in queue2 then goes to a research queue with an eventy type of in process then back to queue2 as in process. My question is if a queue doesn't have an event of pending to begin an event and an event of complete to end an event, how to calculate the difference between dates if an event only has a pending and in process. I need to calculate the number of days in each queue even if there is only a pending event and an in process event or an in process event and a complete event. The dates are from a tracking system that tracks new documents from receipt through completion. |
#3
|
|||
|
|||
Calculating the difference between multiple dates in multiple
Hi Fred,
The table structure contains a unique identifier field, an event name field (queue name) and a date field which contains the date that the document is assigned to each queue. Those events with a pending, in progress and complete date I can calculate the # of days correctly using DateDiff function but not all events have a pending, in progress and complete date associated with them. Anytime in the process the document can be sent to a research queue. When someone sends to the research queue, the 1st event is In Process and after the research is completed usually doesn't have a complete event. The document can be sent to the research queue multiple times during the process. The problem I am having is calculating the # of days each time the document is sent to a research queue. What is happening is I am using min and max in the query, the result I receive is from the beginning of the 1st time in research to the last time was in research rather than being able to have the total # of days each time the document goes to research. "Fred" wrote: I noticed that nobody answered. Whether or not such was intended, this is a structure question. In order to help on those, we need a clear description of the key items of the situaiton that you want to database. You used a lot of terms (queue, queue1, queue2, event, in process event, pending, complete, which have important YOU-SPECIFIC definitions that you've not told us about. Taking a guess, I'm thinking that you have a table where a record constitutes a document that has come into your system. I'm guessing that you have two main sequences that these run through, and that you are using the word "queue" to loosely refer to that sequence, and not the more traditional definition which would be the set of documents that are "waiting". And that you the dates of three "mileposts" for a document flowing through each of these sequences. Assuming that these are date/time fields, your can calculate differences between any of the entered dates at will, as well as the differences from the current date. If the above guesses are right, you could use the date diff function (on Pending ande Complete fields) to calculate the total time in the Queue for completed items, and it on "Pending" and now() for those where such has not been entered. with an iif function to automatically decide which gets calculated. "Greg" wrote: I am trying to calculate the total # of days between start and end dates for multiple events when they go back and forth from different queues. Example: Date - Queue1 Pending Date - Queue1 In Process Date - Queue1 Complete Date - Queue2 Pending Date - Queue2 In Process Date - Queue2 Complete I can calculate if each event has a pending, in process and complete event but there are cases where an item is being tracked and is in process in queue2 then goes to a research queue with an eventy type of in process then back to queue2 as in process. My question is if a queue doesn't have an event of pending to begin an event and an event of complete to end an event, how to calculate the difference between dates if an event only has a pending and in process. I need to calculate the number of days in each queue even if there is only a pending event and an in process event or an in process event and a complete event. The dates are from a tracking system that tracks new documents from receipt through completion. |
#4
|
|||
|
|||
Calculating the difference between multiple dates in multiple
Greg,
I mean this in a manner-of-fact (not negative) way, but I think that your second post inadvertently ignored my first response and so I got nowhere to go with trying to help. Not know the key info, my wild guess is that you need a structural change, with a table for documents, and a (linked) table of instances of the document entering new statuses, and that such would support what you are trying to do. Sorry I could not be of more help. |
Thread Tools | |
Display Modes | |
|
|