Since I have no idea what you are looking for, see if this helpsSELECT --DISTINCT
ShiptoID = ro.ConsdRAD -- We don't need the ShiptoID, but we can't get rid of the column name either..
,dba_Name = LEFT(MAX(rs.Dba_Name), 35)
,LicName = LEFT(MAX(rs.LicName), 35)
,Addr1 = MAX(ro.Addr1)
,City = MAX(rs.City)
,STATE = MAX(rs.STATE)
,Zipcode = MAX(rs.Zipcode)
,Plus4 = MAX(rs.Plus4)
,AreaCode = MAX(ro.AreaCode)
,Phone = MAX(ro.Phone)
,ClassOfTrade = MAX(ro.ClassOfTrade)
,OnOffPremise = MAX(ro.OnOffPremise)
,LAT = MAX(rs.roLAT)
,LON = MAX(rs.roLON)
,fb.FMLYUID
,pl.FamShort
,pl.FamLong
,fb.BRNDUID
,pl.BrdShort
,pl.BrdLong
,BBS = SUM(rs.TotalBBS)
,SUM(CASE WHEN pl.container = 'Cans' THEN 1 ELSE 0 END) AS CANS
,SUM(CASE WHEN pl.container = 'Bottles' THEN 1 ELSE 0 END) AS BOTTLES
,SUM(CASE WHEN pl.container = 'Kegs' THEN 1 ELSE 0 END) AS Kegs
-- This where I am trying to split the container into 3 values:
--Cans = CASE
--WHEN pl.container = 'Cans' --THEN 'Cans'
--Bottles = CASE
--WHEN pl.Container = 'Bottles' --THEN 'Bottles'
--Kegs = CASE
--WHEN pl.container = 'Kegs' --THEN 'Kegs'
,LastUpdate = GETDATE() --@Today
-- select top 100 rs.*
FROM dbo.vw_RtlSalesNew rs
INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl ON pl.ProdUID = rs.ProdUID
INNER JOIN Pabst_DW1.dbo.prProducts p ON pl.ProdUID = p.ProdUID
INNER JOIN Pabst_DW1.dbo.prFamBrands fb ON p.FMBRUID = fb.FMBRUID
INNER JOIN dbo.vw_RtlOutletsNew ro ON rs.ConsdRAD = ro.ConsdRAD
WHERE rs.InvoiceDate >= '2015-12-01' --BETWEEN @StartDate AND @EndDate
AND ro.CoTCode NOT IN ('06','07','50','99','37','19') -- Omit Non-Retail accounts, Sub-Distributors, Employee Sales..
GROUP BY ro.ConsdRAD
,fb.FMLYUID
,pl.FamShort
,pl.FamLong
,fb.BRNDUID
,pl.BrdShort
,pl.BrdLong;