Im getting way too many rows retured..what its trying to do is insert
a 0 for revenue for months 7 - 12 (aka July through December) for each
of these cost centers for each payor type..Im getting a lot of repeats
and the concatenation field date always comes back as January 2003
instead of the month and date its supposed to
--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_Revenue_tmp table
(
Revenue money default 0,
[Date] varchar(15),
monthn smallint,
yearn smallint,
[CostCenter] varchar(50),
[PayorType] varchar(50)
)
--Temp table to store the values of the cost 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.fullname + ' '
+ costcenter.code from costcenter, agency_cost_center
where costcenter.oid = agency_cost_center.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,
payor
where payor_type.oid = payor.payor_type_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_Revenue_tmp temp table
while (@pcount <= @ptcounter)
begin
Insert into @Recorded_Revenue_tmp(Revenue, [Date], monthn,
yearn,[CostCenter],[PayorType])
select 0, datename(month, @month)+ ' ' + cast(@year -1 as varchar(4)),
@month, @year -1, ccname, ptname
from @costcenter_tmp ct,@payor_type_tmp pt where ct.ccid = @ccount and
pt.ptid = @pcount
set @pcount = @pcount + 1
end
set @pcount = 1
set @ccount = @ccount + 1
end
set @ccount = 1
set @month = @month + 1
end
select * from @Recorded_Revenue_tmp
sample return data:(returns 16008 rows!!!)
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 SAGA
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Self Pay
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 ABH
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Managed Medicaid
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Managed Medicaid
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Managed Medicaid
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Managed Medicaid
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Commercial
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Commercial
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Commercial
..0000 January 2003 7 2003 Genesis Assertive Community Treatment Team
250 Commercial
thanks -Jim
