Here is my insert into a temp table.
INSERT INTO #Mike_Loc_Tracker
(ShipToID, dba_Name, LicName, Addr1, City, State,
Zipcode, Plus4, AreaCode, Phone, ClassOfTrade, OnOffPremise,
LAT, LON, FMLYUID, FamShort, FamLong, BRNDUID, BrdShort, BrdLong, BBS, Cans, Bottles, Kegs, LastUpdate)
SELECT --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),
-- 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 --with (nolock)
INNER JOIN MM_Data.dbo.vw_VBS_Prodlist_UXT pl --with (nolock)
ON pl.ProdUID = rs.ProdUID
INNER JOIN Pabst_DW1.dbo.prProducts p --with (nolock)
ON pl.ProdUID = p.ProdUID
INNER JOIN Pabst_DW1.dbo.prFamBrands fb --with (nolock)
ON p.FMBRUID = fb.FMBRUID
INNER JOIN dbo.vw_RtlOutletsNew ro --with (nolock)
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