Cursor output - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor output

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-05-25 : 19:17:30

I have a cursor looks lie below. It generates the output looks like

Unit month year oob
---------------------
A 1 2006 8793.89

Unit month year oob
----------------------
A 1 2006 111.89

Unit month year oob
--------------------
c 1 2006 834.89

--so on

But,I'd like to make the output looks like a table

Unit month year oob
---------------------
A 1 2006 8793.89

B 1 2006 111.89

c 1 2006 834.89

How can I do this? Do I have to store the resute in a temp table and slect from it?

create procedure usp_AROOb_report ( @year1 int)
AS

SET NOCOUNT ON
Declare @month int
Declare @year int
Declare @unit_abbr varchar(12)

Declare cr CURSOR FAST_FORWARD FOR
Select month, year , unit_abbr
from revenue_aggregate
where year = @year1
group by month, year,unit_abbr
order by unit_abbr, month

OPEN cr

FETCH cr INTO @month, @year, @unit_abbr

WHILE @@fetch_status = 0

BEGIN

declare @prev_month int
declare @prev_year int
declare @prev_yearB int
SET @prev_month = MONTH(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))
Set @prev_yearB = @year -1
SET @prev_year = YEAR(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))

declare @bdp int
set @bdp = ( select sum (a.bdp) from (select round(sum(bad_dept_provision ),-2) as bdp , a.month
From revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] <= @month) AND (a.[year] = @year) AND (a.[unit_abbr] = @unit_abbr))
group by a.month) a )

With MTDrevenue
AS
( select ( isnull((MTDopenBal),0) + isnull((payments),0) + isnull((refunds),0)+ isnull((bdw),0) + isnull((MTDSNRbeforeBDW),0 ) ) as AR, unit_abbr
from
(

select (select isnull(sum(payments),0) + isnull(sum(refunds),0) + isnull(sum(bad_debt_writeoffs),0) + ( isnull( sum(system_net_revenue),0) + isnull(sum(bad_debt_writeoffs),0) )
from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
where a.unit_abbr = @unit_abbr and (a.year * 100) + a.month <= (@prev_year * 100) + @prev_month) as MTDopenBal,

a.unit_abbr
,isnull(sum(payments),0)as payments
,isnull(sum(refunds),0) as refunds
,isnull(sum(bad_debt_writeoffs),0) as bdw
, isnull( sum(system_net_revenue),0) + isnull(sum(bad_debt_writeoffs),0) as MTDSNRbeforeBDW

from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] = @month) AND (a.[year] = @year) AND (a.[unit_abbr] = @unit_abbr))
group by a.unit_abbr
) a

)

--OUTPUT
select
a.unit_abbr,a.month, a.year, ( MTDrevenue.AR - sum(convert(numeric(10,0),isnull([grand_total],0))) ) as OOB

from aging_aggregate a join MTDrevenue on a.unit_abbr = MTDrevenue.unit_abbr
where a.[month] = @month and a.[year] =@year and a.unit_abbr = @unit_abbr
group by MTDrevenue.AR ,a.unit_abbr, a.month,a.year

FETCH cr INTO @month, @year, @unit_abbr

END

CLOSE cr
DEALLOCATE cr

RETURN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-25 : 19:19:26

Instead of this:

select
a.unit_abbr,a.month, a.year, ( MTDrevenue.AR - sum(convert(numeric(10,0),isnull([grand_total],0))) ) as OOB

from aging_aggregate a join MTDrevenue on a.unit_abbr = MTDrevenue.unit_abbr
where a.[month] = @month and a.[year] =@year and a.unit_abbr = @unit_abbr
group by MTDrevenue.AR ,a.unit_abbr, a.month,a.year

Put it into a temporary table.

After you close out the cursor, do a select from the temporary table.

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-26 : 05:01:03

Why not convert your cursor query to set based.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-05-26 : 10:57:38

quote:
Originally posted by tkizer

Instead of this:

select
a.unit_abbr,a.month, a.year, ( MTDrevenue.AR - sum(convert(numeric(10,0),isnull([grand_total],0))) ) as OOB

from aging_aggregate a join MTDrevenue on a.unit_abbr = MTDrevenue.unit_abbr
where a.[month] = @month and a.[year] =@year and a.unit_abbr = @unit_abbr
group by MTDrevenue.AR ,a.unit_abbr, a.month,a.year

Put it into a temporary table.

After you close out the cursor, do a select from the temporary table.

Tara Kizer
aka tduggan

If I do that, I keep getting an error: There is already an object named '#temp1' in the database.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-05-26 : 10:58:14

quote:
Originally posted by khtan

Why not convert your cursor query to set based.

KH

I am going to look into it...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-26 : 11:55:51

quote:
Originally posted by jung1975

quote:
Originally posted by tkizer

Instead of this:

select
a.unit_abbr,a.month, a.year, ( MTDrevenue.AR - sum(convert(numeric(10,0),isnull([grand_total],0))) ) as OOB

from aging_aggregate a join MTDrevenue on a.unit_abbr = MTDrevenue.unit_abbr
where a.[month] = @month and a.[year] =@year and a.unit_abbr = @unit_abbr
group by MTDrevenue.AR ,a.unit_abbr, a.month,a.year

Put it into a temporary table.

After you close out the cursor, do a select from the temporary table.

Tara Kizer
aka tduggan

If I do that, I keep getting an error: There is already an object named '#temp1' in the database.

Posting the code that generates this error would help us help you correct it.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources