Case sub query assistance required

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)

Case sub query assistance required

Author

Topic

katalystguy
Starting Member

20 Posts

Posted - 2012-02-27 : 12:51:29

Hi,

I am trying to search through a table of records and build up three counts depending on three conditions. I'm using a case statement but one of the counts is determined by checking if a value in the original table also exists in a second table. My statment does not like the sub query, see blow:

Query
SELECT
SUM (CASE WHEN i.Transferred = 'Y' THEN 1 ELSE 0 END) AS Transfered_Abs,
SUM (CASE WHEN i.Assist = 'Y' THEN 1 ELSE 0 END) AS Escalated_Abs,
SUM (CASE WHEN (SELECT Count(*) from Archive_ACD_Call_Details a WHERE a.Segment = 1) > 0 THEN 1 ELSE 0 END) AS AHT_Count_3_Minutes
from Import_ACD_Call_Details i WHERE i.Segment = 1 AND Convert(varchar(10), i.SEGStart, 103) = '16/04/2002' AND i.ANSLogin = 10016

Error
Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

All assistance appreciated

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-27 : 13:11:01

You have no Group BY, and your 3rd SELECT Column can only return 1 value anyway

Can you post a description in terms of a Business Requirements Document?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-02-27 : 13:36:39

Hi,

The business requirements can be summarised as follows:

Search through table one (containing todays call records only) counting how many records are classed as transferred, assisted or repeats. There is an indicator column in table one set to Y/N for transferred and assisted, so straight forward. However to determine that a record in table one is a repeat requires a lookup on the second table (containing past call records), if there is one or more records in table two matching the Calling_PTY value (not shown in example query) in table one then it is classed as a repeat and added to the count.

So for 100 records in table one I might end up with the following

10 Transfered
20 Assisted
10 Repeats

(the other 60 are neither of these)

Hope this helps.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-27 : 14:02:41

What's the relationship between Archive_ACD_Call_Details and Import_ACD_Call_Details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-02-27 : 14:11:12

The foramts of the two tables are identical. The Import table contains todays records only, while the Archive table contains previous days records.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-27 : 14:36:23

what are the keys between the 2 tables?

How about


SELECT * FROM 
		( SELECT DISTINCT Convert(varchar(10), i.SEGStart, 103) AS Call_Date 
			FROM Import_ACD_Call_Details) AS d
LEFT JOIN 
		( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Tranfer_Calls
			FROM Import_ACD_Call_Details WHERE Transferred = 'Y') AS t
	   ON d.Call_Date  = t.Call_Date 
LEFT JOIN 
		( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Escalated_Calls 
			FROM Import_ACD_Call_Details WHERE Assist = 'Y') AS a
	   ON d.Call_Date  = a.Call_Date 
LEFT JOIN 
		( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Repeat_Calls 
			FROM Import_ACD_Call_Details o
		   WHERE EXISTS (SELECT * FROM Archive_ACD_Call_Details i
						  WHERE o.<key> = i.<key>)) r
	   ON d.Call_Date  = r.Call_Date

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

katalystguy
Starting Member

20 Posts

Posted - 2012-02-27 : 17:16:51

Wow, theres a statement, looks good I will try it out tomorrow. The key to Import_ACD_Call_Details is an automatic identified (int) set on the table. The key to the Archive_ACD_Call_Details is an int only as the records are copied in from Import_ACD_Call_Details at the end of each night.

katalystguy
Starting Member

20 Posts

Posted - 2012-02-28 : 06:32:00

Hi,

Ok I tried the statement below (notice where you had <key> I have replaced with the column name to match on for repeated calls):

SELECT * FROM
( SELECT DISTINCT Convert(varchar(10), i.SEGStart, 103) AS Call_Date
FROM Import_ACD_Call_Details) AS d
LEFT JOIN
( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Tranfer_Calls
FROM Import_ACD_Call_Details WHERE Transferred = 'Y') AS t
ON d.Call_Date = t.Call_Date
LEFT JOIN
( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Escalated_Calls
FROM Import_ACD_Call_Details WHERE Assist = 'Y') AS a
ON d.Call_Date = a.Call_Date
LEFT JOIN
( SELECT Convert(varchar(10), i.SEGStart, 103) AS Call_Date, COUNT(*) AS Repeat_Calls
FROM Import_ACD_Call_Details o
WHERE EXISTS (SELECT * FROM Archive_ACD_Call_Details i
WHERE o.Calling_PTY = i.Calling_PTY)) r
ON d.Call_Date = r.Call_Date

and I get the following error message:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.SEGStart" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.SEGStart" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.SEGStart" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "i.SEGStart" could not be bound.

So it does not seem to be able to reference the alias 'i' table.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 06:54:18

what about this:


SELECT
	SUM(dt.[Transfered_ABS]) AS [Transfered_ABS]
	, SUM(dt.[Escalated_Abs]) AS [Escalated_Abs]
	, SUM(dt.[AHT_Count_3_Minutes]) AS [AHT_Count_3_Minutes]
FROM
	(
		SELECT
			CASE WHEN i.[Transferred] <> 'Y' THEN 1 ELSE 0 END AS [Transfered_ABS]
			
			, CASE WHEN i.[Assist] = 'Y' THEN 1 ELSE 0 END AS [Escalated_Abs]
			
			, CASE
				WHEN EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a WHERE a.[Calling_PTY] = i.[Calling_PTY]) THEN 1
				ELSE 0
				END AS [AHT_Count_3_Minutes]
		FROM
			Import_ACD_Call_Details AS i
		WHERE
			i.[Segment] = 1
			AND i.[SEGStart] >= '20020416'
			AND i.[SEGStart] < '20020417'
			AND i.[ANSLogin] = 10016		
	)
	AS dt

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

katalystguy
Starting Member

20 Posts

Posted - 2012-02-28 : 07:13:55

Oh man that worked a treat, I made some very subtle changes but it worked, see below. I liked the join queery from X002548 as I have never put one together before and looks like it might be very useful for other needs. Many thanks.

SELECT
SUM(dt.[Transfered_ABS]) AS [Transfered_ABS]
, SUM(dt.[Escalated_Abs]) AS [Escalated_Abs]
, SUM(dt.[Repeat_Abs]) AS [Repeat_Abs]
FROM
(
SELECT
CASE WHEN i.[Transferred] = 'Y' THEN 1 ELSE 0 END AS [Transfered_ABS]

, CASE WHEN i.[Assist] = 'Y' THEN 1 ELSE 0 END AS [Escalated_Abs]

, CASE
WHEN EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a WHERE a.[Calling_PTY] = i.[Calling_PTY]) THEN 1
ELSE 0
END AS [Repeat_Abs]
FROM
Import_ACD_Call_Details AS i
WHERE
i.[Segment] = 1
AND i.[SEGStart] >= '20020416'
AND i.[SEGStart] < '20020417'
AND i.[ANSLogin] = 10016
)
AS dt

katalystguy
Starting Member

20 Posts

Posted - 2012-02-28 : 07:21:00

Charlie,

I have one more feature to add to the requirement, now I have the transfered, escerlated and repeated calls, the last thing to add in is "normal" calls. A normal call is simple one that is not an transferred, escerlated or repeated call.

It's not quite as simple as counting the number of rown in the Import_ACD_Call_Details table and subtracting the three totals we have generated as a call might be both a transfer, escerlated and repeat, so this would not allow for this double counting.

I know its a pig as it means putting in an expression to test in inverse of the three previous conditions.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 08:46:02

Easy:


SELECT
	SUM(dt.[Transfered_ABS]) AS [Transfered_ABS]
	, SUM(dt.[Escalated_Abs]) AS [Escalated_Abs]
	, SUM(dt.[Repeat_Abs]) AS [Repeat_Abs]
	, SUM(dt.[Normal_ABS]) AS [Normal_ABS]
FROM
	(
		SELECT
			CASE WHEN i.[Transferred] = 'Y' THEN 1 ELSE 0 END AS [Transfered_ABS]
			
			, CASE WHEN i.[Assist] = 'Y' THEN 1 ELSE 0 END AS [Escalated_Abs]
			
			, CASE
				WHEN EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a WHERE a.[Calling_PTY] = i.[Calling_PTY]) THEN 1
				ELSE 0
				END AS [Repeat_Abs]
				
			, CASE
				WHEN
					i.[Transfered] <> 'Y'
					AND i.[Assist] <> 'Y'
					AND NOT EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a2 WHERE a2.[Calling_PTY] = i.[Calling_PTY])
					THEN 1
					ELSE 0
				END AS [Normal_ABS]
		FROM
			Import_ACD_Call_Details AS i
		WHERE
			i.[Segment] = 1
			AND i.[SEGStart] >= '20020416'
			AND i.[SEGStart] < '20020417'
			AND i.[ANSLogin] = 10016		
	)
	AS dt

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

katalystguy
Starting Member

20 Posts

Posted - 2012-02-28 : 08:53:12

Yep easy, it worked a treat. Just one spelling error on column i.[Transfered], missing second r!

Thanks v m

katalystguy
Starting Member

20 Posts

Posted - 2012-02-28 : 09:15:32

Forgot to mention, I will probably index the Calling_PTY column in both tables to help speed things up.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-28 : 09:45:36

Also with the rewrite to the SEGStart check you can now use an index on that as well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

katalystguy
Starting Member

20 Posts

Posted - 2012-04-03 : 06:58:20

Charlie,

I have another change to make to this statement. As well as capturing the number of calls for each of the four types (normal, transferred, escalated and repeats) I also need to get the average AHT for each type as well. To do that, first we need to calculate the AHT for each valid call from field TalkTime using the following expression:

SUM (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime))

Somehow I need to get the average AHT for all matching calls for each of the four types. I'm not sure how to add the logic in to the existing if then else section of the statement? Assuming thats the correct place!

Appreciate your help again.

katalystguy
Starting Member

20 Posts

Posted - 2012-04-03 : 12:59:14

Ok, I got it working (see below) but I would not say it is the most elegant method, you will see I have simply duplicated each case statement and changed the THEN expression to i.[TalkTime] and saved in a new variable.

I would be impressed if anyone can improve on this.

SELECT
SUM(dt.[Transfers_Abs]) AS [Transfers_Abs]
, SUM(dt.[Escalations_Abs]) AS [Escalations_Abs]
, SUM(dt.[Repeats_Abs]) AS [Repeats_Abs]
, SUM(dt.[Normal_Abs]) AS [Normal_Abs]
, SUM (DATEPART(hour, dt.[Transfers_TalkTime]) * 3600 + DATEPART(minute, dt.[Transfers_TalkTime]) * 60 + DATEPART(second, dt.[Transfers_TalkTime])) AS [Transfers_AHT]
, SUM (DATEPART(hour, dt.[Escalations_TalkTime]) * 3600 + DATEPART(minute, dt.[Escalations_TalkTime]) * 60 + DATEPART(second, dt.[Escalations_TalkTime])) AS [Escalations_AHT]
, SUM (DATEPART(hour, dt.[Repeats_TalkTime]) * 3600 + DATEPART(minute, dt.[Repeats_TalkTime]) * 60 + DATEPART(second, dt.[Repeats_TalkTime])) AS [Repeats_AHT]
, SUM (DATEPART(hour, dt.[Normal_TalkTime]) * 3600 + DATEPART(minute, dt.[Normal_TalkTime]) * 60 + DATEPART(second, dt.[Normal_TalkTime])) AS [Normal_AHT]
FROM
(
SELECT
CASE WHEN i.[Transferred] = 'Y' THEN 1 ELSE 0 END AS [Transfers_Abs]
, CASE WHEN i.[Transferred] = 'Y' THEN i.[TalkTime] ELSE 0 END AS [Transfers_TalkTime]

, CASE WHEN i.[Assist] = 'Y' THEN 1 ELSE 0 END AS [Escalations_Abs]
, CASE WHEN i.[Assist] = 'Y' THEN i.[TalkTime] ELSE 0 END AS [Escalations_TalkTime]

, CASE
WHEN EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a WHERE a.[Calling_PTY] = i.[Calling_PTY] AND (Datediff(second, i.[SEGStart], a.[SEGStart]) between -1209600 AND -1)) THEN 1
ELSE 0
END AS [Repeats_Abs]
, CASE
WHEN EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a WHERE a.[Calling_PTY] = i.[Calling_PTY] AND (Datediff(second, i.[SEGStart], a.[SEGStart]) between -1209600 AND -1)) THEN i.[TalkTime]
ELSE 0
END AS [Repeats_TalkTime]

, CASE
WHEN
i.[Transferred] <> 'Y'
AND i.[Assist] <> 'Y'
AND NOT EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a2 WHERE a2.[Calling_PTY] = i.[Calling_PTY] AND (Datediff(second, i.[SEGStart], a2.[SEGStart]) between -1209600 AND -1))
THEN 1
ELSE 0
END AS [Normal_Abs]
, CASE
WHEN
i.[Transferred] <> 'Y'
AND i.[Assist] <> 'Y'
AND NOT EXISTS (SELECT 1 FROM Archive_ACD_Call_Details AS a2 WHERE a2.[Calling_PTY] = i.[Calling_PTY] AND (Datediff(second, i.[SEGStart], a2.[SEGStart]) between -1209600 AND -1))
THEN i.[TalkTime]
ELSE 0
END AS [Normal_TalkTime]
FROM
Import_ACD_Call_Details AS i
WHERE
i.[Segment] = 1
AND Convert(varchar(10), i.[SEGStart], 103) = Convert(varchar(10), ?, 103)
AND i.[ANSLogin] = ?
AND i.[FirstVDN] IN(SELECT v.VDN from VDN_List v WHERE v.LoB = ?)
)
AS dt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources