I have this query but can’t create map by result set of query.
CREATE TABLE #Map
(MediaItem_Count INT,
Country VARCHAR(20),
Lat Decimal (12,10),
Long Decimal (9,6))
INSERT INTO #MAP
SELECT Count(mi.Mediaitemid) AS TotalCount, Cn.Country,
Lat =
CASE
WHEN Cn.Country = ‘Australia’ THEN -25.274398 --133.775136–MAX(p.DateCreated) AS MinMaxDateTime
WHEN Cn.Country = ‘China’ THEN 35.861660 – 104.195397
WHEN Cn.Country = ‘Hong Kong’ THEN 22.396428 – 22.396428
WHEN Cn.Country = ‘Indonesia’ THEN -0.789275 – 113.921327
WHEN Cn.Country = ‘Malaysia’ THEN 4.210484 – 101.975766
WHEN Cn.Country = ‘New Zealand’ THEN -40.900557 – 174.885971
WHEN Cn.Country = ‘Philippines’ THEN 12.879721 – 121.774017
WHEN Cn.Country = ‘Singapore’ THEN 1.352083 – 103.819836
WHEN Cn.Country = ‘Thailand’ THEN 15.870032 – 100.992541
WHEN Cn.Country = ‘Vietnam’ THEN 14.058324 – 108.277199
END,
Long =
CASE
WHEN Cn.Country = ‘Australia’ THEN 133.775136
WHEN Cn.Country = ‘China’ THEN 104.195397
WHEN Cn.Country = ‘Hong Kong’ THEN 22.396428
WHEN Cn.Country = ‘Indonesia’ THEN 113.921327
WHEN Cn.Country = ‘Malaysia’ THEN 101.975766
WHEN Cn.Country = ‘New Zealand’ THEN 174.885971
WHEN Cn.Country = ‘Philippines’ THEN 121.774017
WHEN Cn.Country = ‘Singapore’ THEN 103.819836
WHEN Cn.Country = ‘Thailand’ THEN 100.992541
WHEN Cn.Country = ‘Vietnam’ THEN 108.277199
END
FROM Prospects p (nolock)
INNER JOIN mediaitems mi (nolock) ON mi.MediaItemID = p.MediaItemID
INNER JOIN Programs pg (nolock) ON mi.programid = pg.programid
INNER JOIN Sources s (nolock) ON s.SourceID = pg.SourceID
INNER JOIN MediaTypes mt (nolock) ON mt.MediaTypeID = s.MediaTypeID
INNER JOIN states st ON s.StateID = st.StateID
INNER JOIN [Countries] (NOLock) AS cn ON ST.CountryID = cn.CountryID
INNER JOIN Briefs (NOLock) AS b ON p.BriefID = b.BriefID
INNER JOIN BriefDeliverySets (NOLock) AS bds ON b.BriefID = bds.BriefID
WHERE mt.MediaTypeID IN (7,10) AND BDS.DeliverysetID= 74942 AND YEAR(CAST(mi.AirDate AS Date))>=2016
GROUP BY Cn.Country
SELECT * FROM #Map
DROP TABLE #Map
