Carrie
Active Member
- Joined
- Nov 20, 2002
- Messages
- 418
Once we find only the first outbound event for each SR (call). I will then link this query to another table that holds the actual call the customer made.
Here is how it works. A customer calls and we log their call (SR) then when the tech returns thier call and tries to fix this issue they document the call in an activity.
So, when I find the first outbound activity (we know it is the first because of the time it was created) I will link that to the table with all of the SR's.
I then need to somehow find the first response time. That is to say, how long did it take from the time the customer logged the call to the time the tech made thier first outbound activity.
I guess if we can go straight to this last part that would be beneficial.
I hope I haven't made things more confusing. I was actually trying to make them more clear.
This is the query I have so far:
SELECT [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #], Min([Tbl - Activities].Created) AS MinOfCreated
FROM [Tbl - Activities] INNER JOIN [Tbl - SR's] ON [Tbl - Activities].[SR #] = [Tbl - SR's].[SR #]
GROUP BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #]
ORDER BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #];
Please let me know if you need more information.
Oh, I almost forgot. The response time needs to be calculated in business hours (8:00a-5:00a; a 9 hour business day). Also, you should keep in mind that clients are able to log a call after business hours. This means that if a call is logged after business hours then the first response time start counting when the business day starts.
Sorry, I know this is long but I really appreciate your help.
ray:
Here is how it works. A customer calls and we log their call (SR) then when the tech returns thier call and tries to fix this issue they document the call in an activity.
So, when I find the first outbound activity (we know it is the first because of the time it was created) I will link that to the table with all of the SR's.
I then need to somehow find the first response time. That is to say, how long did it take from the time the customer logged the call to the time the tech made thier first outbound activity.
I guess if we can go straight to this last part that would be beneficial.
I hope I haven't made things more confusing. I was actually trying to make them more clear.
This is the query I have so far:
SELECT [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #], Min([Tbl - Activities].Created) AS MinOfCreated
FROM [Tbl - Activities] INNER JOIN [Tbl - SR's] ON [Tbl - Activities].[SR #] = [Tbl - SR's].[SR #]
GROUP BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #]
ORDER BY [Tbl - SR's].[Functional Area], [Tbl - SR's].[Functional Sub Area], [Tbl - SR's].Priority, [Tbl - SR's].[SR #];
Please let me know if you need more information.
Oh, I almost forgot. The response time needs to be calculated in business hours (8:00a-5:00a; a 9 hour business day). Also, you should keep in mind that clients are able to log a call after business hours. This means that if a call is logged after business hours then the first response time start counting when the business day starts.
Sorry, I know this is long but I really appreciate your help.
ray: 
