Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 no clue

Author  Topic 

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-06 : 09:13:52
Did you execute your code? If so what was the error? Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -