Database Reference
In-Depth Information
DECLARE @return varchar(max) = ''
DECLARE @tbl Table(CountryName varchar(255), JSON varchar(max),
FullJSON varchar(max))
INSERT INTO @tbl (CountryName, JSON)
select
countryname,
CASE WHEN ROW_NUMBER() over (order by countryname) = 1 THEN '' ELSE ',
' END +
'{
"children": [],
"data": {
"GDP Per Capita": "' + CAST( CAST( round(fonr.value /
131.487000,0) as int) as varchar(10)) + '",
"$color": "' + CASE CAST( ROUND( (FOP.Value -
17311.929220) / 7248.925672000,0) + 1 as int)
WHEN 1 THEN '#A50026'
WHEN 2 THEN '#D73027'
WHEN 3 THEN '#F46D43'
WHEN 4 THEN '#FDAE61'
WHEN 5 THEN '#FEE08B'
WHEN 6 THEN '#FFFFBF'
WHEN 7 THEN '#A6D96A'
WHEN 8 THEN '#66BD63'
WHEN 9 THEN '#66BD63'
WHEN 10 THEN '#1A9850'
WHEN 11 THEN '#006837'
END +'",
"image": "",
"$area": ' + CAST( CAST( round(fonr.value / 131.487000,0)
as int) as varchar(10)) + '
},
"id": "' + dc.CountryName + '",
"name": "' + dc.CountryName + '"
}'
FROM DBO.FactOECDNationalReserve FONR
INNER JOIN
dbo.DimCountry DC
on FONR.DimCountryID = dc.CountryID
INNER JOIN dbo.FactOECDPopulation FOP
on DC.CountryID = FOP.DimCountryID
and fop.DimDateID = 20110101
and FOP.DimOECDStatisticID = 197
and FOP.value is not null
where fonr.DimDateID = 20110901
