For some reason the compiler is telling me that I must declarethe
variable @costcenter_tmp on lines 74 and 98...but if i put a select
statement in ther (for testing) before the loop I get data back from
the temp table..
why is this happening to this temp table and no others?..heres my
code..its a little lengthy
--Fiscal Year
declare @year smallint
set @year = 2004
--Month number the Fiscal year starts and ends
declare @month smallint
set @month = 7
--Place holder for number of costcenters
declare @cccounter smallint
--loop counter for cost centers
declare @ccount smallint
set @ccount = 1
--Place holder for number of payor types
declare @ptcounter smallint
--loop counter for payor types
declare @pcount smallint
set @pcount = 1
--Temp table to store the blank values for all cost centers/payor
types for the fiscal year
declare @Recorded_Reven ue_tmp table
(
Revenue money default 0,
[Date] varchar(15),
monthn smallint,
[Cost Center] varchar(50),
[Payor Type] varchar(50)
)
--Temp table to store the values of the coster centers
declare @costcenter_tmp table
(
ccid int IDENTITY (1,1),
ccname varchar(50)
)
--Inserts cost centers and code into the @costcenter_tmp temp table
insert into @costcenter_tmp (ccname) select costcenter.full name + ' '
+ costcenter.code from costcenter, agency_cost_cen ter
where costcenter.oid = agency_cost_cen ter.cost_center _moniker
--Sets the @cccounter variable to the number of cost centers
select @cccounter = count(*) from @costcenter_tmp
--Temp table to store the values of the payor types
declare @payor_type_tmp table
(
ptid int identity (1,1),
ptname varchar(50)
)
--Inserts payor types into the @payor_type_tmp temp table
Insert into @payor_type_tmp (ptname)select fullname from payor_type
where payor_type.oid = payor.payor_typ e_moniker
--Sets the @ptcounter variable to the number of payor types
select @ptcounter = count(*) from @payor_type_tmp
--Loop that gets the first part of the fiscal year
While (@month <13)
begin
--Loop that gets the value of the cost center to insert
While (@ccount <= @cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @Recorded_Reven ue_tmp temp table
while (@pcount <= @ptcounter)
begin
Insert into @Recorded_Reven ue_tmp(Revenue, [Date], monthn, [Cost
Center],[Payor Type])
select 0, datename(month, @month)+ ' ' + @year -1, @month, [Cost
Center], [Payor Type]
from @costcenter_tmp ,@payor_type_tm p where @costcenter_tmp .ccid =
@ccount and
@payor_type_tmp .ptid = @pcount
set @pcount = @pcount + 1
end
set @pcount = 1
set @ccount = @ccount + 1
end
set @ccount = 1
set @month = @month + 1
end
set @month = 1
--Loop that inserts values for the second part of the fiscal year into
the @Recorded_Reven ue_tmp temp table
While (@month <7)
begin
--Loop that gets the value of the cost center to insert
While (@ccount <= @cccounter)
begin
--Loop that inserts values for the first part of the fiscal year into
the @Recorded_Reven ue_tmp temp table
while (@pcount <= @ptcounter)
begin
Insert into @Recorded_Reven ue_tmp([Date], monthn, [Cost Center],[Payor
Type])
select 0,datename(mont h, @month)+ ' ' + @year, @month, [Cost Center],
[Payor Type]
from @costcenter_tmp , @payor_type_tmp where @costcenter_tmp .ccid =
@ccount and
@payor_type_tmp .ptid = @pcount
set @pcount = @pcount + 1
end
set @pcount = 1
set @ccount = @ccount + 1
end
set @ccount = 1
set @month = @month + 1
end
--Pulls in all the data for the report
(select Revenue,[Date],[Cost Center],[Payor Type] from
@Recorded_Reven ue_tmp)
union
(select (revenue) as Revenue, (b.monthname + ' ' + Cast(b.yearn as
varchar(4))) as 'Date',
c.fullname + ' ' + c.code as 'Cost Center',d.fulln ame as 'Payor
Type'
from chr_recorded_re venue a, chr_recorded_re venue_dates b,
costcenter c, payor_type d
where a.date = b.day and a.[Cost Center]= c.oid and a.[Payor Type]
= d.oid)
order by d.fullname,b.mo nthn, c.oid
thanks..Jim
