January 25, 2008 at 1:46 pm
Using SQL2K.
I have the following query (copy and paste into Query Analyzer for proper formatting):
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(
SELECT
T.Team
FROM
dbo.TEAM_BREAKOUT T
WHERE
CASE
WHEN F.STATUS_DETAILED = 'Closed' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END = T.UID
AND
F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
) AS REPORTING_TEAM,
CASE
WHEN F.STATUS_DETAILED = 'Closed' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID,
'Assigned' AS STATUS,
'Executive' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
AND
F.DATE_RCVD_KEY = 21090
GROUP BY
F.DATE_RCVD_KEY,
CASE
WHEN F.STATUS_DETAILED = 'Closed' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END,
STATUS
ORDER BY
F.DATE_KEY,
REPORTING_TEAM,
UID,
STATUS,
REG_EXEC,
CONTACT_TYPE
The results give me the following:
DATE_KEY REPORTING_TEAM UID STATUS REG_EXEC CONTACT_TYPE AMOUNT
21090 ERT-LC u108876 Assigned Executive Letter 1
21090 ERT-LC u108876 Assigned Executive Letter 1
The problem is, I want these 2 rows combined so the amount will be 2. I know they are being separated, b/c the values for the STATUS_DETAILED column are different. However, I have to include that huge CASE statement in the GROUP BY clause.
Anyone have other options for me?
Thanks!
January 25, 2008 at 1:54 pm
Not an elegant solution, but what happens if you wrap the whole thing in an outer query and select the sum of that column?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 1:57 pm
If you add all the columns listed in the group by to the select, what's the result set?
Basically, there's something in the group by that shouldn't be there for the results you want. Question is, what.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2008 at 1:59 pm
kind of what I did here...when I run this query, it drops one of the counts, so I'm left with 1...it seems to be skipping over one of them.
21090ERT-LCu108876ExecutiveLetter011
SELECT
DRV.DATE_KEY,
DRV.REPORTING_TEAM,
DRV.UID,
DRV.REG_EXEC,
DRV.CONTACT_TYPE,
SUM(CASE WHEN DRV.STATUS = 'Opened' THEN AMOUNT ELSE 0 END) AS OpenedCount,
SUM(CASE WHEN DRV.STATUS = 'Assigned' THEN AMOUNT ELSE 0 END) AS AssignedCount,
SUM(CASE WHEN DRV.STATUS = 'Closed' THEN AMOUNT ELSE 0 END) AS ClosedCount
FROM
(
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.ENTERED_BY_UID = T.UID AND F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.ENTERED_BY_UID, 'u999999') AS UID,
'Opened' AS STATUS,
'Executive' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_RCVD_KEY,
F.ENTERED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(
SELECT
T.Team
FROM
dbo.TEAM_BREAKOUT T
WHERE
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END = T.UID
AND
F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
) AS REPORTING_TEAM,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID,
'Assigned' AS STATUS,
'Executive' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_RCVD_KEY,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END,
STATUS
UNION
SELECT
F.DATE_CASE_CLOSED_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.RESOLVED_BY_UID = T.UID AND F.DATE_CASE_CLOSED_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.RESOLVED_BY_UID, 'u999999') AS UID,
'Closed' AS STATUS,
'Executive' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_CASE_CLOSED_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.DATE_CASE_CLOSED_KEY <> -2
AND
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_CASE_CLOSED_KEY,
F.RESOLVED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.ENTERED_BY_UID = T.UID AND F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.ENTERED_BY_UID, 'u999999') AS UID,
'Opened' AS STATUS,
'Regulator' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_RCVD_KEY,
F.ENTERED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(
SELECT
T.Team
FROM
dbo.TEAM_BREAKOUT T
WHERE
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END = T.UID
AND
F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
) AS REPORTING_TEAM,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID,
'Assigned' AS STATUS,
'Regulator' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_RCVD_KEY,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END,
STATUS
UNION
SELECT
F.DATE_CASE_CLOSED_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.RESOLVED_BY_UID = T.UID AND F.DATE_CASE_CLOSED_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.RESOLVED_BY_UID, 'u999999') AS UID,
'Closed' AS STATUS,
'Regulator' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_CASE_CLOSED_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.DATE_CASE_CLOSED_KEY <> -2
AND
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
GROUP BY
F.DATE_CASE_CLOSED_KEY,
F.RESOLVED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.ENTERED_BY_UID = T.UID AND F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.ENTERED_BY_UID, 'u999999') AS UID,
'Opened' AS STATUS,
'Executive' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_RCVD_KEY,
F.ENTERED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(
SELECT
T.Team
FROM
dbo.TEAM_BREAKOUT T
WHERE
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END = T.UID
AND
F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
) AS REPORTING_TEAM,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID,
'Assigned' AS STATUS,
'Executive' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_RCVD_KEY,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END,
STATUS
UNION
SELECT
F.DATE_CASE_CLOSED_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.RESOLVED_BY_UID = T.UID AND F.DATE_CASE_CLOSED_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.RESOLVED_BY_UID, 'u999999') AS UID,
'Closed' AS STATUS,
'Executive' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_CASE_CLOSED_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.DATE_CASE_CLOSED_KEY <> -2
AND
F.REGULATOR IS NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_CASE_CLOSED_KEY,
F.RESOLVED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.ENTERED_BY_UID = T.UID AND F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.ENTERED_BY_UID, 'u999999') AS UID,
'Opened' AS STATUS,
'Regulator' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_RCVD_KEY,
F.ENTERED_BY_UID,
STATUS
UNION
SELECT
F.DATE_RCVD_KEY AS DATE_KEY,
(
SELECT
T.Team
FROM
dbo.TEAM_BREAKOUT T
WHERE
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END = T.UID
AND
F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
) AS REPORTING_TEAM,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID,
'Assigned' AS STATUS,
'Regulator' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_RCVD_KEY,
CASE
WHEN F.STATUS_DETAILED = 'Closed' AND F.DATE_CASE_CLOSED_KEY > -2 THEN
CASE
WHEN F.METHOD_OF_RESOLUTION = 'Letter' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.CALLER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
WHEN F.METHOD_OF_RESOLUTION = 'Call' THEN ISNULL(F.CALLER_UID, ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned')))
END
WHEN F.STATUS_DETAILED = 'Pending Research' THEN ISNULL(F.RESEARCHER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Not Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Proof Ready' THEN ISNULL(F.LETTERWRITER_UID, 'To Be Assigned')
WHEN F.STATUS_DETAILED = 'Letter - Edit II Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Edit III Required' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Letter - Print Ready' THEN ISNULL(F.LETTERWRITER_UID, ISNULL(F.RESEARCHER_UID, 'To Be Assigned'))
WHEN F.STATUS_DETAILED = 'Call Pending' THEN ISNULL(F.CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END,
STATUS
UNION
SELECT
F.DATE_CASE_CLOSED_KEY AS DATE_KEY,
(SELECT T.Team FROM dbo.TEAM_BREAKOUT T WHERE F.RESOLVED_BY_UID = T.UID AND F.DATE_CASE_CLOSED_KEY BETWEEN T.Start_Key AND T.End_Key) AS REPORTING_TEAM,
ISNULL(F.RESOLVED_BY_UID, 'u999999') AS UID,
'Closed' AS STATUS,
'Regulator' AS REG_EXEC,
'Call' AS CONTACT_TYPE,
COUNT(F.DATE_CASE_CLOSED_KEY) AS AMOUNT
FROM
dbo.F_COMPLAINT_TRACKING F
WHERE
F.DATE_CASE_CLOSED_KEY <> -2
AND
F.REGULATOR IS NOT NULL
AND
F.CUSTOMER_CONTACT_TYPE IN ('Call', 'Email-Internal')
GROUP BY
F.DATE_CASE_CLOSED_KEY,
F.RESOLVED_BY_UID,
STATUS
) DRV
GROUP BY
DRV.DATE_KEY,
DRV.REPORTING_TEAM,
DRV.UID,
DRV.REG_EXEC,
DRV.CONTACT_TYPE
January 25, 2008 at 2:02 pm
I'm looking at the query, and it looks like it could be simplified. Can you provide the table structure and a couple of rows of sample data, for the tables referenced in the query? If so, I may be able to simplify the query and make it do what you want. It's a little rough without the underlying tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 25, 2008 at 2:17 pm
Here is the table structure; working on some data.
January 25, 2008 at 2:31 pm
Here is the data...
Thanks!!!
January 25, 2008 at 5:31 pm
Got it! By removing the "STATUS" reference in the GROUP BY I was able to get it to work. Thanks for the help everyone!!!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply