AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2005-06-06 : 08:05:33
|
writes "Is there any way I can take my case statement and set my "end as" name to something dynamically like getdate()?Essentially i would like to dynamically name the columns with the Month and year.Here is my example.Select replace(replace(replace(replace(replace(replace(replace(measure,'numCommitted','#Comm'),'Committed', '%CDD'),'numCompleted', '#Comp'), 'ComClientMisses', '%Client Misses'),'ComITMisses','%IT Misses'),'numITMisses','#IT Misses'), 'numClientMisses','#Client Misses') as CDD, max(case when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'Committed' then cast(convert(decimal,pct)as varchar(5)) + '%' when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'numCommitted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'numCompleted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'numClientMisses' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'ComClientMisses' then cast(convert(decimal,pct)as varchar(5)) + '%' when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'numITMisses' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-02-01'),1,3) and os.measure = 'ComITMisses' then cast(convert(decimal,pct)as varchar(5)) + '%' end) as Feb, max(case when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'Committed' then cast(convert(decimal,pct)as varchar(5)) + '%' when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'numCommitted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'numCompleted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'numClientMisses' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'ComClientMisses' then cast(convert(decimal,pct)as varchar(5))+ '%' when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'numITMisses' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-04-01'),1,3) and os.measure = 'ComITMisses' then cast(convert(decimal,pct)as varchar(5)) + '%' end) as Apr, max(case when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-06-01'),1,3) and os.measure = 'Committed' then cast(convert(decimal,pct)as varchar(5)) + '%' when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-06-01'),1,3) and os.measure = 'numCommitted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-06-01'),1,3) and os.measure = 'numCompleted' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-06-01'),1,3) and os.measure = 'numClientMisses' then cast(convert(decimal,pct)as varchar(5)) when substring(datename(mm, os.monthMeasured),1,3) = substring(datename(mm, '2005-06-01'),1,3) and os.measure = 'ComClientMisses' then cast(conve |
|