Very hard question..for me anyway

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Very hard question..for me anyway

Author

Topic

mericlese
Starting Member

11 Posts

Posted - 2007-06-06 : 18:00:58

Alright, I'm not exactly sure how to explain this one but I'll give it a shot. Starting out with this query:

select count(*) from Calls Inner Join Phone.DBO.Extensions as ext on ext.ID = Calls.AnalystID
where Calls.OpenDate >= '5/21/2007' and Calls.OpenDate <= '5/25/2007 11:59:59 PM' and ext.class = 'Bank'

This returns '302' as the count, which is the correct number, what I'm looking for. What its doing is going and getting all the calls we have received within the date range. The whole point of what I'm trying to do is present a report on how long it takes to Assign those calls to an Analyst. Next query:

select count(*) from Calls inner join CallActions on Calls.CallID = CallActions.CallID
Inner Join Phone.DBO.Extensions as ext on ext.ID = Calls.AnalystID
where Calls.OpenDate >= '5/21/2007' and Calls.OpenDate <= '5/25/2007 11:59:59 PM'
and CallActions.ActionTaken = 'Assign' and ext.class = 'Bank'
and DateDiff(n, Calls.OpenDate, CallActions.ActionDateTime) >= 0
and DateDiff(n, Calls.OpenDate, CallActions.ActionDateTime) < 15

Using the above query I hoped to be able to get the number of calls that were assigned between 0 and 15 minutes. Unfortunately this is flawed and that's where it gets complicated. To determine when a call is assigned, CallActions.ActionTaken must be 'Assigned' and there are a certain number of calls everyday that are resolved, but not assigned, so we're missing that information. To further complicate matters, there are some calls that are assigned multiple times. The end result is that if one were slightly adjust the query to get the total number of calls for the day the result is 320. Slight modification of the above query:

select count(distinct CallAction.CallID) from Calls inner join CallActions on Calls.CallID = CallActions.CallID
Inner Join Phone.DBO.Extensions as ext on ext.ID = Calls.AnalystID
where Calls.OpenDate >= '5/21/2007' and Calls.OpenDate <= '5/25/2007 11:59:59 PM'
and CallActions.ActionTaken = 'Assign' and ext.class = 'Bank'
and DateDiff(n, Calls.OpenDate, CallActions.ActionDateTime) >= 0
and DateDiff(n, Calls.OpenDate, CallActions.ActionDateTime) < 15

I came up with this to solve the problem of more than one result per CallAction.CallID that had an ActionTaken of 'Assign', and while this does eliminate those extras, I'm not lacking those calls that were resolved without being assigned and querying to find the total number of calls answered is now '287'. Now, I'd be willing to just write the ones that I'm skipping off and put them into a special 'Unassigned' column in my report, but the above query still isn't working like I need it to. Lets say that I query for 0 to 15, and then 15 to 30 minutes, there are several instances in the CallActions table where the CallID duplicates are more than 15 minutes apart, so I'm still not getting accurate results. I need to get 1 result per CallID and only one result per CallID. I'm sorry for this being so long, but if anyone knows what's going wrong, or could help me out I'd be very grateful. Thanks in advance.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-06 : 20:18:22

I think I see what you're trying to do.

I put together a big query to illustrate how to get your report the way you want. Let me know if this works for you.


Select 'Total Calls' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
Union All
Select 'Not Assigned Calls' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
Left join	(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa
				where aa.ActionTaken = 'Assign'
					
			) b
on			a.CallID = b.CallID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
and			b.ActionTaken is null
Union All
Select '0-15 Minutes' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
Inner join	(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
					
			) b
on			a.CallID = b.CallID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
and			DateDiff(n, a.OpenDate, b.ActionDateTime) <= 15
Union all
Select '16-30 Minutes' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
Inner join	(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
					
			) b
on			a.CallID = b.CallID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
and			DateDiff(n, a.OpenDate, b.ActionDateTime) between 16 and 30
Union all
Select '31-45 Minutes' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
Inner join	(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
					
			) b
on			a.CallID = b.CallID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
and			DateDiff(n, a.OpenDate, b.ActionDateTime) between 31 and 45
union all
Select '> 45 Minutes' as [Desc], Count(*) as [Count]
from		Calls a
Inner Join	Phone.DBO.Extensions ext 
on			ext.ID	= a.AnalystID 
Inner join	(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
					
			) b
on			a.CallID = b.CallID 
where		a.OpenDate between '5/21/2007' and	'5/25/2007 11:59:59 PM' 
and			ext.class = 'Bank'
and			DateDiff(n, a.OpenDate, b.ActionDateTime) > 45

mericlese
Starting Member

11 Posts

Posted - 2007-06-07 : 10:32:53

First off, thanks. Unfortunately that returns pretty much the same numbers as mine does. The initial total is right (302) but when you add up all the subtotals it still comes out to 321. The reason its doing this is because in the CallActions table we have this.

CallID ActionDateTime ActionTaken
123686 2007-05-21 09:20:30.480 Assign
123686 2007-05-21 09:09:43.910 Assign
123686 2007-05-21 10:03:25.773 Assign
123686 2007-05-21 10:03:37.510 Assign
123686 2007-05-21 10:09:16.437 Assign

So, I have one CallID with five different Assign Actions, all taken at different times. (Please note, I didn't design this system)
So, I need to count just one of these. Lets say CallID.OpenDate = '2007-05-21 09:00:00.000' I only want this to be counted in the 0-15 minute subtotal, not the 15-30 and 45+ subtotals as well. Does that make any sense.

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-07 : 12:01:12

try this:



with ActionTimes(CallID, ActionDateTime) as
(
select Callid, min(ActionDateTime) as ActionDateTime
from CallActions
where Actiontaken='Assign'
group by Callid
) 
select count(*) from Calls inner join ActionTimes on Calls.CallID = ActionTimes.CallID
Inner Join Phone.DBO.Extensions as ext on ext.ID = Calls.AnalystID
where Calls.OpenDate >= '5/21/2007' and Calls.OpenDate <= '5/25/2007 11:59:59 PM'
and ActionTimes.ActionTaken = 'Assign' and ext.class = 'Bank'
and DateDiff(n, Calls.OpenDate, ActionTimes.ActionDateTime) >= 0
and DateDiff(n, Calls.OpenDate, ActionTimes.ActionDateTime) < 15

mericlese
Starting Member

11 Posts

Posted - 2007-06-07 : 16:19:42

Alright! Success. Thanks for all you help! Here's the query that ended up doing exactly what I needed:

select count(*) as Result from Calls inner join Phone.DBO.Extensions as ext on ext.ID = Calls.AnalystID
inner join (
select Callid, min(ActionDateTime) as ActionDateTime from CallActions where Actiontaken='Assign' group by Callid) a
on Calls.CallID = a.CallID where Calls.OpenDate between '05/21/2007' and '05/25/2007 11:59:59 PM'
and ext.class = 'Bank' and DateDiff(n, Calls.OpenDate, a.ActionDateTime) between 0 and 15

Thanks to both of you for your help!

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-07 : 16:24:57

it's a easy modification, but if there are 5 assign actions, which one do you want to associate with the record, the one with the latest action time? If I use the one that associates with that record (0-15,16-30,etc) it will count each assign as a new call. If I take the latest that will reflect the total time it took from the call recieved to the lastassign time.



(
				select distinct aa.CallId, aa.CallActions,aa.ActionTime 
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
					
			) b

--with

(
				select aa.CallId, aa.CallActions,Max(aa.ActionTime )
				From CallActions aa 
				where aa.ActionTaken = 'Assign'
group by aa.CallID,aa.CallActions

					
			) b

This will make the totals equal your total count, if you want to use the earliest assign time, just change the word MAX to MIN in the above query.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-07 : 16:30:35

I see we were typeing at the same time..

I'm glad you figured it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources