SQLTeam.com | Weblogs | Forums

Mapping numbers to color Hex dynamically


#1

Ok been trying this for a couple of hours and few cups of coffee now getting dizzy. I am trying to dynamically map range of number to color hex. this is what I have so far. Me and case statements are not very close so I would really love to avoid that if at all possible.

[code]
create table #memoryscales (memoryscaleid int identity(1,1) , memorysizegb int not null, fromMem bigint, ToMem bigint , Memstatus varchar(50), StatusColor varchar(50)) ;

--Generate dynamic sample #memoryscales
;WITH Nums(memorysizegb) AS
(SELECT 1000 AS memorysizegb
 UNION ALL
 SELECT memorysizegb + 1000 FROM Nums where memorysizegb < 10000
)
insert into #memoryscales(memorysizegb)
select memorysizegb from Nums option(maxrecursion 100)

select * from #memoryscales

update tgt
	set tgt.fromMem = tgt.memorysizegb - 999,
	    tgt.ToMem = tgt.memorysizegb
from #memoryscales tgt


update tgt
	set tgt.Memstatus = case 
		                   when ToMem >=  10000 then 'Disaster' 
						   else 'Shaka Laka Boom Boom'
		                end,
        tgt.StatusColor = '#c73101'
from #memoryscales tgt


select * from #memoryscales

drop table #memoryscales

#2

ok I did the following

create table #memorycolormapper (memorysizegb  int , fromMem bigint , ToMem bigint , [Status] varchar(50), StatusColor varchar(50)) ;
declare @basememorysizegb int,
        @nearestthousand int = 10000,
		@basevalue int = 1000

--Generate dynamic sample #memorycolormapper
;WITH Nums(memorysizegb) AS
(SELECT @basevalue AS memorysizegb
	UNION ALL
	SELECT memorysizegb + @basevalue FROM Nums where memorysizegb < @nearestthousand
)
insert into #memorycolormapper(memorysizegb)
select memorysizegb from Nums option(maxrecursion 10000)

select * from #memorycolormapper
	
update tgt
	set tgt.fromMem = tgt.memorysizegb - (@basevalue -1),
		tgt.ToMem = tgt.memorysizegb
from #memorycolormapper tgt

update tgt
	set tgt.StatusColor = case 
			                when ToMem >=  @nearestthousand then '#ff0000' 	
							when @nearestthousand - (@basevalue * 1) between fromMem and ToMem then '#ffff00' 	
							else  '#008000'						    
							 
			            end,
		tgt.[Status] = case 
			                when ToMem >=  @nearestthousand then 'Critical' 	
							when @nearestthousand - (@basevalue * 1) between fromMem and ToMem then 'Operational' 	
							else  'Good'	
			            end
from #memorycolormapper tgt

select * From #memorycolormapper

drop table #memorycolormapper