halifaxdal (4/11/2014)

---

This is driving people crazy!

There is another sp to be converted and this sp contains loop and lots of calculation. The original sp is working fine and logic is very simple, can I have advice from anyone?

Create proc [dbo].[ddGetDevDRROnly]

AS

Declare @q int --variable for quarter

Declare @Balance int

Declare @SVP varchar(20)

Declare @b-2 int --Open Deviations as of Oct 31

Declare @C int --New dev with original TargetClosureDate within current fiscal year

Declare @D int --New dev with original TargetClosureDate beyong current fiscal year

Declare @a int --YTD number of retired dev

Declare @DRR float --A/(B+C+D)

Create table #temp

(

Division varchar(20),

B int,

C int,

D int,

A int,

DRR float

)

--Loop each Division

declare cDivision cursor for

Select Division From ITSDivision

OPEN cDivision

FETCH NEXT FROM cDivision

INTO @SVP

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @b-2 = count(1) FROM Document d

left outer join ITSDivision i on d.ITSDivisionID = i.ID

Where

i.Division = @SVP and

DocStatus = 'Active' and

DocType = 1

SELECT @C = count(1) FROM Document d

left outer join ITSDivision i on d.ITSDivisionID = i.ID

Where

i.Division = @SVP and

DocStatus = 'Active' and

DocType = 1 and

CAST(YEAR(TargetClosureDate) AS int) = dbo.fnFiscalYear(getdate())

SELECT @D = count(1) FROM Document d

left outer join ITSDivision i on d.ITSDivisionID = i.ID

Where

i.Division = @SVP and

DocStatus = 'Active' and

DocType = 1 and

CAST(YEAR(TargetClosureDate) AS int) > dbo.fnFiscalYear(getdate())

SELECT @a = count(1) FROM Document d

left outer join ITSDivision i on d.ITSDivisionID = i.ID

Where

i.Division = @SVP and

DocStatus = 'Retired' and

DocType = 1 and

CAST(YEAR(RetiredDate) AS int) = (dbo.fnFiscalYear(getdate())-1)

IF ((@B + @C + @D)> 0)

BEGIN

Set @DRR = CONVERT(DECIMAL(8,1), 100.0) * @a /(@B + @C + @D)

END

ELSE

BEGIN

Set @DRR = 0

END

Insert into #temp Values(@SVP, @b-2, @C, @D, @a, @DRR)

FETCH NEXT FROM cDivision

INTO @SVP

END

CLOSE cDivision

DEALLOCATE cDivision

select * from #temp order by DRR desc