If you are trying to dynamically create the field names in a select statement, you'll probably have to construct the string and then execute it with EXEC or sp_executesql, something like:

-- Create the Statement 
SET @sqltext1 = 'Select '
-- Create the column name dynamically --
While @accsubdate < @enddate 
begin
if (month(@accsubdate)=1)
	SET @sqltext1 = @sqltext1 + '@accsubdate as Jan '+str(year(@accsubdate))+',' 
if (month(@accsubdate)=2)
	SET @sqltext1 = @sqltext1 + '@accsubdate as Feb. '+str(year(@accsubdate))+',' 
if (month(@accsubdate)=3)
	SET @sqltext1 = @sqltext1 + '@accsubdate as Mar '+str(year(@accsubdate))+',' 
-- etc.
set @accsubdate = dateadd(mm,1,@accsubdate)

end
-- finish constructing @sqltext1..
EXEC (@sqltext1)

HTH

>Thanks for the reply. It is a cross tab I just left out the group by study for the count()
>
>I am trying to define the columns in the statement this way. I error on the When??? Any ideas? This should just return a date in a column named for that Character month & year. After I get the date to be in teh colum I'll add the meat of counting. But this can run anywhere as is.
>
>
>-- Create vars for process
>declare @AccSubDate datetime
>declare @startdate datetime
>declare @enddate datetime
>declare @currMo varchar(10)
>declare @currYr varchar(10)
>declare @sqltext1 varchar(2000)
>declare @currentdate datetime
>declare @NoMonths varchar(10)
>declare @i numeric
>
>-- Set data to start
>set @startdate = '07/01/2000' --@ustartdate -- user passed in value
>set @enddate = '10/31/2000' --@uenddate -- user passed in value
>set @accsubdate = dateadd(dd,-1,@startdate)
>set @NoMonths = datediff(mm,@startdate,@enddate)
>set @i = 0
>
>-- Create the Statement
>select @accsubdate as
>-- Create the column name dynamically --
>(
>While @accsubdate < @enddate
>begin
>case month @accsubdate
>when 1 then 'Jan '+str(year(@accsubdate))+','
>when 2 then 'Feb. '+str(year(@accsubdate))+','
>when 3 then 'March '+str(year(@accsubdate))+','
>when 4 then 'Arpil '+str(year(@accsubdate))+','
>when 5 then 'May '+str(year(@accsubdate))+','
>when 6 then 'June '+str(year(@accsubdate))+','
>when 7 then 'July '+str(year(@accsubdate))+','
>when 8 then 'Aug. '+str(year(@accsubdate))+','
>when 9 then 'Sept. '+str(year(@accsubdate))+','
>when 10 then 'Oct. '+str(year(@accsubdate))+','
>when 11 then 'Nov. '+str(year(@accsubdate))+','
>when 12 then 'Dec. '+str(year(@accsubdate))+','
>
>set @accsubdate = dateadd(mm,1,@accsubdate)
>
>end
>)
>
>It now dies on the When. Any ideas?
>
>TIA
>
>__Stephen
>

---

>
>>Steven
>>
>>Not really a cross-tab since you only have one variable. It would be a cross-tab if you wanted to know number that started for each Dr.
>>
>>But, anyway, you can do what you want using count()
>>
>>SELECT
>>count(datepart(m,@startdatein)=datepart(m,startdate) As Month1
>>,count(datepart(m,@startdatein)<=datepart(m,startdate) As Month1
>>....
>>WHERE startdate > @startdatein
>>
>>I haven't test this, but it should get you started.
>>
>>BOb