I want to group certain DDSCodes. So if the doc is s67, s41 or s66 I want a column to be created called DDSGroup and put V01 next to them and if not just leave it as null.This is what I want:Doc Dist Reg Typecode DDSState DDSGroup001 001 A 13 ME NULL002 002 A 13 ME NULLS67 Null C 7 ME V01S41 Null C 7 PA V01S66 Null C 7 PA V01
I want to add it to this query below. When I did I'm getting invlaid column name 'V01' SELECT distinct(c.OfficeCode) AS DOC, CASE WHEN c.officecode in ('s67', 's41', 's66') then VO1 else Null end as DDSGroup, case WHEN TypeCode IN (1, 18) THEN ReportsTo WHEN TypeCode IN (13) THEN c.OfficeCode ELSE NULL END AS DIST, c.Region AS Reg, r.RegionAcronym, CASE WHEN Area < 10 THEN '0' + CAST(Area AS Char(1)) ELSE CAST(Area AS Char(2)) END AS Area, REPLACE(c.Name, ',', '') AS DOC_NAME, r.RegionNumber AS REGION_NUM, r.RegionName, c.TypeCode, a.addressstate as DDSStateFROM Offices.dbo.OfficeCodes AS c left JOIN Offices.dbo.Regions AS r ON c.Region = r.RegionLetter join offices.dbo.addr as a on a.officecode = c.officecodeWHERE (c.TypeCode IN (1, 18, 13, 42, 7))What am I missing?
Hep with case statement please...
Posted - 2012-03-20 : 09:12:54
