That is how I finally got it.

Thanks for the idea. The statement can excede theVarChar limit when the user selects 30 months to report on. Currently that is not a problem but it could be in a while.

__Stephen

>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