Rahul
3/29/2007 10:54:00 AM
On Mar 29, 1:52 pm, "Tony WONG" <x...@hknet.com> wrote:
> In order to show dynamic header in the table result, i wish to replace
> 'February' by datename(month, @Date1) in line 3 & 4.
>
> How can i do it? Thanks a lot.
>
> ******************* SQL script *********************
> declare @Date1 varchar(8)
> set @Date1 = cast(year(dateadd(m,-1,getdate())) as varchar(4)) + right('0' +
> cast(month(dateadd(m, -1, getdate())) as varchar(2)), 2) + '01'
> select K.UserCode, A.February from (select distinct UserCode from
> ZZZ.dbo.table1) as K
> full join (select UserCode, count(UserCode) as February
> from sms.dbo.oshc_out where Submit_Time_server >'20070301'
> group by UserCode) as A
> on K.UserCode = A.UserCode
> order by K.UserCode
Hi
declare @Date1 varchar(8),
@lDate VarChar(100)
set @Date1 = cast(year(dateadd(m,-1,getdate())) as varchar(4)) +
right('0' +
cast(month(dateadd(m, -1, getdate())) as varchar(2)), 2) + '01'
Select @lDate = Datename(month, @Date1)
Exec ('select K.UserCode, A.February as ' + @lDate + ' from (select
distinct UserCode from
ZZZ.dbo.table1) as K
full join (select UserCode, count(UserCode) as February
from sms.dbo.oshc_out where Submit_Time_server >''' + 20070301 + '''
group by UserCode) as A
on K.UserCode = A.UserCode
order by K.UserCode ')
Rahul