June 27, 2016 at 5:11 am
Hi All, trying to get my head around this one and failing somewhat! Any help would be much appreciated. I'm trying to retrieve value from a call counts table based on service levels in a threshold table based on join using ReportQueue.
From the query below I'm trying to retrieve values 135 (Finance) and 210 (Marketing). This is based on a Finance service level of 60 and therefore relating to Threshold 6 and Marketing service level of 30 and so relating to Threshold 3.
Any ideas greatly received!
Thanks, Gary
DROP TABLE #Thresholds
DROP TABLE #CallCounts
CREATE TABLE #Thresholds
(
CallQueue VARCHAR(20)
,ReportQueue VARCHAR(10)
,ServiceLevel INT
,Threshold1 INT
,Threshold2 INT
,Threshold3 INT
,Threshold4 INT
,Threshold5 INT
,Threshold6 INT
)
INSERT #Thresholds VALUES ('Finance', 'P200', 60, 10, 20, 30, 40, 50, 60)
INSERT #Thresholds VALUES ('Marketing', 'P200', 30, 10, 20, 30, 40, 50, 60)
CREATE TABLE #CallCounts
(
CallQueue VARCHAR(20)
,ReportQueue VARCHAR(10)
,Segment1 INT
,Segment2 INT
,Segment3 INT
,Segment4 INT
,Segment5 INT
,Segment6 INT
)
INSERT #CallCounts VALUES ('Finance', 'P200', 25, 52, 87, 99, 120, 135)
INSERT #CallCounts VALUES ('Marketing', 'P200', 50, 123, 210, 268, 334, 393)
SELECT * FROM #Thresholds
SELECT * FROM #CallCounts
June 27, 2016 at 10:34 am
SELECT
T.CallQueue, T.ReportQueue, T.ServiceLevel,
CASE WHEN T.ServiceLevel = T.Threshold1 THEN C.Segment1
WHEN T.ServiceLevel = T.Threshold2 THEN C.Segment2
WHEN T.ServiceLevel = T.Threshold3 THEN C.Segment3
WHEN T.ServiceLevel = T.Threshold4 THEN C.Segment4
WHEN T.ServiceLevel = T.Threshold5 THEN C.Segment5
WHEN T.ServiceLevel = T.Threshold6 THEN C.Segment6
ELSE NULL END AS SegmentValue
FROM #Thresholds T
INNER JOIN #CallCounts C ON C.CallQueue = T.CallQueue AND C.ReportQueue = T.ReportQueue
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 28, 2016 at 4:22 am
That's great, many thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply