Problem with SQL query - Merging similar/duplicate rows
-
SELECTstarttime AS Time_Period,
SUM(incalls) AS [Inbound Calls],
SUM(acdcalls) AS [ACD Calls],
SUM(abncalls) AS [Abandoned Calls],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE 100 * SUM(abncalls) / SUM(incalls) END
AS [% Abandoned],
CASE WHEN SUM(acdcalls) = 0 THEN 0
ELSE (SUM(anstime) / SUM(acdcalls)) END
AS [Avg Speed Answer],
CASE WHEN SUM(abntime) = 0 THEN 0
ELSE (SUM(abntime) / SUM(abncalls)) END
AS [Avg Abandoned Time],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE ((SUM(ansconncalls1) + SUM(ansconncalls2) + SUM(ansconncalls3) + SUM(ansconncalls4)) / SUM(incalls) * 100) END
AS [% Calls Ans in 30 Sec],
SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)
AS [Aban calls in 30s],
CASE WHEN SUM(incalls) = 0 THEN 0
ELSE ((SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)) / SUM(incalls) * 100) END
AS [% Aban in 30s]
FROM hvdn
WHERE
(acd = 1)
AND
(row_date = CONVERT(DATETIME, '2009-07-23 00:00:00', 102))
GROUP BY
starttime
ORDER BY
Time_Period
The above code DOES work and produces the expected results. A summary of the data returned is displayed below:
This image is also attached in case the link breaks.
My problem is the "similar/duplicate" row at 1000. I need the respective values (while taking into account the existing calculations performed in the query) merged. The desired result is that each half-hourly time period has a single row of data each.
If anyone can help me, I would really, really appreciate it. [:)]
I have posted this query on a few discussion forums about SQL so if/when I get an answer, I will update this post accordingly.
I am using SQL Server 2005 Enterprise.
-
Can you post your table definition? What data type is starttime?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
The data type for starttime is "smallint"
-
Can you post the table definition (Create table) and some sample data? Maybe just the "1000" rows. I'm thinking it may have something to do with the way you are setting the 0's using case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
SELECT starttime AS Time_Period, SUM(incalls) AS [Inbound Calls], SUM(acdcalls) AS [ACD Calls], SUM(abncalls) AS [Abandoned Calls],CASE WHEN SUM(incalls) = 0 THEN 0 ELSE 100 * SUM(abncalls) / SUM(incalls) END AS [% Abandoned], CASE WHEN SUM(acdcalls)
= 0 THEN 0 ELSE (SUM(anstime) / SUM(acdcalls)) END AS [Avg Speed Answer], CASE WHEN SUM(abntime) = 0 THEN 0 ELSE (SUM(abntime)
/ SUM(abncalls)) END AS [Avg Abandoned Time], CASE WHEN SUM(incalls) = 0 THEN 0 ELSE ((SUM(ansconncalls1) + SUM(ansconncalls2)
+ SUM(ansconncalls3) + SUM(ansconncalls4)) / SUM(incalls) * 100) END AS [% Calls Ans in 30 Sec], SUM(abncalls1) + SUM(abncalls2)
+ SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6) AS [Aban calls in 30s], CASE WHEN SUM(incalls)
= 0 THEN 0 ELSE ((SUM(abncalls1) + SUM(abncalls2) + SUM(abncalls3) + SUM(abncalls4) + SUM(abncalls5) + SUM(abncalls6)) / SUM(incalls) * 100)
END AS [% Aban in 30s]
FROM hvdn
WHERE (acd = 1) AND (row_date = CONVERT(DATETIME, '2009-07-23 00:00:00', 102))
GROUP BY vdn, starttime
HAVING (vdn IN ('3344', '3339', '3173', '4517'))
ORDER BY Time_Period
Apologies, I missed a vital piece of code in my copy-pasting. Please note the addition of:
GROUP BY vdn , starttime
HAVING (vdn IN ('3344', '3339', '3173', '4517'))
-
What is vdn? I don't see it in the select list anywhere.
I think you'll be better off by moving the filtering on vdn to the WHERE clause, and I think this could fix your problem. The HAVING clause is applied AFTER the grouping, while the WHERE clause is applied BEFORE the grouping.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Thank you.
I knew it would be something straight-forward like that.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply