question

LtDan avatar image
LtDan asked

Case SUM and Detail

I think this is a simple syntex issue but it has me stumped. In one report I use a CTE/Select then my SUM(CASE when.. That works fine. Now, I need to run this same CTE in another report to get the detail (not Sum) so I type my CTE/Select then CASE When... and it requires me to add the values from the wcase when to the group by. Now my results are zero. Bottom line how do I change this from Sum to Detail.. Here is the syntex (sorry I know there is a lot to look through but I eliminated what I could). The issue is in the Second CTE under --Risk. I currently show the Sum(Case... which works fine 318 records. Below that, commented out is what I need, the detail. It fails unless I add the variables to the group by. Then when I add them my results are 51 records instead of the desired 318. ;with CTE(Executive, RiskHierarchy) AS ( select isnull(cast(DivisionExec3Dot as varchar),'Unknown') DivisionExec3Dot ,isnull(hierarchy,'Unknown') hierarchy from metview_owner.tblRisk a (nolock) Where RBtype = 'Proposed Self Identified Audit Issue' and DRAFT = '0' union select isnull(cast(IssueExec as varchar),'Unknown') IssueExec ,isnull(divisionhierarchy,'Unknown') divisionhierarchy from metview_owner.tblAudit (nolock) Where LOB_1DOT = 'GLOBAL TECHNOLOGY AND OPERATIONS' ) select Executive ,cast(null as varchar) Division ,cast(null as varchar)DivExec ,cast(null as varchar)AuditLOB ,cast(null as int) SIAIProp ,RiskHierarchy into #Rpt from CTE group by Executive, RiskHierarchy update #Rpt set #Rpt.Division = case when RiskHierarchy LIKE 'F%' THEN 'ET&D' when RiskHierarchy LIKE 'AL%' THEN 'GTQ&CD' when RiskHierarchy LIKE 'P%' THEN 'Corporate Workplace' when RiskHierarchy LIKE 'AK%' THEN 'ERCS' when RiskHierarchy LIKE 'HT%' THEN 'CSBBT&O & HLIT' when RiskHierarchy LIKE 'QZ%' THEN 'CSBBT&O & HLIT' when RiskHierarchy LIKE 'TG%' THEN 'GBMWM' when RiskHierarchy LIKE 'TH%' THEN 'GBMWM' when RiskHierarchy LIKE 'NT%' then 'GBMWM' when RiskHierarchy LIKE 'NM%' then 'GBMWM' else 'Unknown' end from #Rpt a inner join tblRisk b (nolock) on a.Executive = isnull(b.DivisionExec3Dot,'Unknown') and RID = (SELECT MAX(RID) from tblRisk (nolock) where DivisionExec3Dot = isnull(b.DivisionExec3Dot,'Unknown')) update #Rpt set DivExec = case when RiskHierarchy LIKE 'F%' THEN 'MARC GORDON' when RiskHierarchy LIKE 'AL%' THEN 'AMY BRADY' when RiskHierarchy LIKE 'P%' THEN 'GENE GODBOLD' when RiskHierarchy LIKE 'AK%' THEN 'MARY ELLEN BAKER' when RiskHierarchy LIKE 'HT%'THEN 'VLAD TORGOVNIK' when RiskHierarchy LIKE 'QZ%' THEN 'VLAD TORGOVNIK' when RiskHierarchy LIKE 'TG%' THEN 'JAMES KELLY' when RiskHierarchy LIKE 'TH%' THEN 'JAMES KELLY' when RiskHierarchy LIKE 'NT%' then 'JAMES KELLY' when RiskHierarchy LIKE 'NM%' then 'JAMES KELLY' else 'Unassigned' end from #Rpt a inner join tblRisk b (nolock) on a.Executive = isnull(b.DivisionExec3Dot,'Unknown') and RID = (SELECT MAX(RID) from tblRisk (nolock) where DivisionExec3Dot = isnull(b.DivisionExec3Dot,'Unknown')) update #Rpt set Division = case when RiskHierarchy in ('100','150') THEN 'GBMWM' when RiskHierarchy = '200' THEN 'CSBBT&O & HLIT' when RiskHierarchy = '400' THEN 'ERCS' when RiskHierarchy = '500' THEN 'Corporate Workplace' when RiskHierarchy = '600' THEN 'GTQ&CD' when RiskHierarchy = '700' THEN 'ET&D' when RiskHierarchy LIKE 'F%' THEN 'ET&D' when RiskHierarchy LIKE 'AL%' THEN 'GTQ&CD' when RiskHierarchy LIKE 'P%' THEN 'Corporate Workplace' when RiskHierarchy LIKE 'AK%' THEN 'ERCS' when RiskHierarchy LIKE 'HT%' THEN 'CSBBT&O & HLIT' when RiskHierarchy LIKE 'QZ%' THEN 'CSBBT&O & HLIT' when RiskHierarchy LIKE 'TG%' THEN 'GBMWM' when RiskHierarchy LIKE 'TH%' THEN 'GBMWM' when RiskHierarchy LIKE 'NT%' then 'GBMWM' when RiskHierarchy LIKE 'NM%' then 'GBMWM' else 'Unknown' end from #Rpt a update #Rpt set DivExec = case when RiskHierarchy LIKE 'F%' THEN 'MARC GORDON' when RiskHierarchy LIKE 'AL%' THEN 'AMY BRADY' when RiskHierarchy LIKE 'P%' THEN 'GENE GODBOLD' when RiskHierarchy LIKE 'AK%' THEN 'MARY ELLEN BAKER' when RiskHierarchy LIKE 'HT%'THEN 'VLAD TORGOVNIK' when RiskHierarchy LIKE 'QZ%' THEN 'VLAD TORGOVNIK' when RiskHierarchy LIKE 'TG%' THEN 'JAMES KELLY' when RiskHierarchy LIKE 'TH%' THEN 'JAMES KELLY' when RiskHierarchy LIKE 'NT%' then 'JAMES KELLY' when RiskHierarchy LIKE 'NM%' then 'JAMES KELLY' when RiskHierarchy = '100' THEN 'JAMES KELLY' when RiskHierarchy = '150' THEN 'JAMES KELLY' when RiskHierarchy = '200' THEN 'VLAD TORGOVNIK' when RiskHierarchy = '400' THEN 'MARY ELLEN BAKER' when RiskHierarchy = '500' THEN 'GENE GODBOLD' when RiskHierarchy = '600' THEN 'AMY BRADY' when RiskHierarchy = '700' THEN 'MARC GORDON' else 'Unassigned' end from #Rpt a --Risk ;with CTE AS ( select isnull(DivisionExec3Dot,'Unknown') DivisionExec3Dot, isnull(Hierarchy,'Unknown') Hierarchy -- ,sum(case When RBType = 'Proposed Self Identified Audit Issue' -- and aud_sub_date is null -- THEN 1 else 0 End) as SIAINotValidated ,case When RBType = 'Proposed Self Identified Audit Issue' and aud_sub_date is null THEN 1 else 0 End as SIAINotValidated from metview_owner.tblRisk a (nolock) Where Draft = '0' group by isnull(DivisionExec3Dot,'Unknown'), isnull(Hierarchy,'Unknown') ,rbtype, aud_sub_date ) update #Rpt set SIAIProp = b.SIAINotValidated ,RiskHierarchy = b.Hierarchy from #Rpt a inner join CTE b on a.Executive = b.DivisionExec3Dot and a.RiskHierarchy = b.hierarchy --Final select isnull(Division,'Unknown') Division ,isnull(DivExec,'Unknown') DivExec ,isnull(AuditLOB,'Unknown') AuditLOB ,isnull(RiskHierarchy, 'Unknown') RiskHierarchy ,isnull(RTRIM(b.FirstName) + ' ' + RTRIM(b.LastName),'Unassigned') as Exec3Dot ,sum(isnull(SIAIProp,0)) SIAIProp from #Rpt a left join metview_owner.directory b (nolock) on a.Executive = b.PersonNbr where (RiskHierarchy = '100' or RiskHierarchy = '150' or RiskHierarchy = '200' or RiskHierarchy = '400' or RiskHierarchy = '500' or RiskHierarchy = '600' or RiskHierarchy = '700' or RiskHierarchy LIKE 'F%' or RiskHierarchy LIKE 'AL%' or RiskHierarchy LIKE 'P%' or RiskHierarchy LIKE 'AK%' or RiskHierarchy LIKE 'HT%' or RiskHierarchy LIKE 'QZ%' or RiskHierarchy LIKE 'TG%' or RiskHierarchy LIKE 'TH%' or RiskHierarchy LIKE 'NT%' or RiskHierarchy LIKE 'NM%') group by isnull(Division,'Unknown') ,isnull(RiskHierarchy, 'Unknown') ,isnull(RTRIM(b.FirstName) + ' ' + RTRIM(b.LastName),'Unassigned') ,isnull(DivExec,'Unknown') ,isnull(AuditLOB,'Unknown') order by isnull(Division,'Unknown') ,isnull(RiskHierarchy, 'Unknown') ,isnull(RTRIM(b.FirstName) + ' ' + RTRIM(b.LastName),'Unassigned') ,isnull(DivExec,'Unknown') ,isnull(AuditLOB,'Unknown')
case-statement
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
it may help if you post your code to help explain the problem
5 Likes 5 ·
Scot Hauder avatar image Scot Hauder commented ·
Remove the group by clause
3 Likes 3 ·
Tim avatar image Tim commented ·
Yes, please post your code so others can help you. Sounds like you have some extra stuff in your case statement. Which if you need the detail you may not need the case statement.
1 Like 1 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
without seeing the syntax that you mention we will not be able to help you.
1 Like 1 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
now you've posted the code - to be clear what is the issue - the definition of the 2nd CTE or the query that runs against it? Can you run the CTE definition as just a query without error?
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
Here is one way to resolve the issue with the cte under the comment line reading --Risk. Since this is just an update of the temp table from the join, you can restate it like this (no need to use a cte): --Risk update a set SIAIProp = case when b.RBType = 'Proposed Self Identified Audit Issue' and b.aud_sub_date is null then 1 else 0 end, RiskHierarchy = isnull(b.Hierarchy,'Unknown') from #Rpt a inner join metview_owner.tblRisk b on a.Executive = isnull(b.DivisionExec3Dot,'Unknown') and a.RiskHierarchy = isnull(b.Hierarchy,'Unknown') where b.Draft = '0'; Oleg
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.