General information
Forum:
Microsoft SQL Server
I don't know that you can do a while loop withing a select like that.
You need perens around the date for the month function...
>case month( @accsubdate )
Don't know if that will solve the problem though.
BOb
>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
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only