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