SQL re: Group By - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

SQL re: Group By

Author

Topic

qdaddyo
Starting Member

7 Posts

Posted - 2008-01-25 : 15:40:59

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-26 : 11:16:27

If you do want values without splitting up on STATUS_DETAILED column, then may i know why are you including it in GROUP BY?

qdaddyo
Starting Member

7 Posts

Posted - 2008-01-28 : 11:44:03

quote:
Originally posted by visakh16

If you do want values without splitting up on STATUS_DETAILED column, then may i know why are you including it in GROUP BY?

Yeah, I figured it out by removing the STATUS group by. Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 11:52:27

Also replace the nested ISNULL statements with the COALESCE function.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 12:07:42

[code]SELECT F.DATE_RCVD_KEY AS DATE_KEY,
T.TEAM AS REPORTING_TEAM,
F.UID,
'Assigned' AS STATUS,
'Executive' AS REG_EXEC,
'Letter' AS CONTACT_TYPE,
COUNT(F.DATE_RCVD_KEY) AS AMOUNT
FROM (
SELECT DATE_RCVD_KEY,
CASE
WHEN STATUS_DETAILED = 'Closed' THEN COALESCE(LETTERWRITER_UID, CALLER_UID, RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Pending Research' THEN COALESCE(RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Letter - Not Ready' THEN COALESCE(LETTERWRITER_UID, RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Letter - Proof Ready' THEN COALESCE(LETTERWRITER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Letter - Edit II Required' THEN COALESCE(LETTERWRITER_UID, RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Letter - Edit III Required' THEN COALESCE(LETTERWRITER_UID, RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Letter - Print Ready' THEN COALESCE(LETTERWRITER_UID, RESEARCHER_UID, 'To Be Assigned')
WHEN STATUS_DETAILED = 'Call Pending' THEN COALESCE(CALLER_UID, 'To Be Assigned')
ELSE 'To Be Assigned'
END AS UID
FROM F_COMPLAINT_TRACKING
WHERE REGULATOR IS NULL
AND CUSTOMER_CONTACT_TYPE IN ('Letter', 'Email-Customer')
AND DATE_RCVD_KEY = 21090
) AS F
LEFT JOIN dbo.TEAM_BREAKOUT AS T ON T.UID = F.UID
AND F.DATE_RCVD_KEY BETWEEN T.Start_Key AND T.End_Key
GROUP BY F.DATE_RCVD_KEY,
T.TEAM,
F.UID
ORDER BY F.DATE_RCVD_KEY,
T.TEAM,
F.UID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources