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