Help converting procedural VB code to SQL

I am at the last hurdle on converting a very large chunk of VB code that
massages a recordset to produce a report.
This question relates to my previous question from 3/2 and the ddl that I
posted for that question.
I need to replace the following vb code with SQL and I think I can do it
with a Case statement but would really appreciate some input on this. The V
B
code follows the url for the original message.
http://msdn.microsoft.com/newsgroup...r />
4F24A2-F7
1F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
'***********Code Start************
Dim TempValue As Single
If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
If iGreenRpt Then ' A green report has been requested
If iRunInData Then 'Data or spec is not green so make adjustment
TempValue = iValue * iFactor
Else ' Take data as is "Green"
TempValue = iValue
End If
statAdjustData = TempValue
Else 'Non-green or Runin/"Market Rating" report has been requested
If Not iRunInData Then 'Data or spec is green so make adjustment
TempValue = iValue / iFactor
Else 'Take data as is "Runin"
TempValue = iValue
End If
statAdjustData = TempValue
End If ' Green or Runin/Market data report requested
Else ' iFactor = 1 or 0 therefore no need to adjust
statAdjustData = iValue
End If ' iFactor = To Or <> 1 or 0
'********Code End*****************--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps:
DECLARE @.True TINYINT, @.False TINYINT
SET @.True = 1
Set @.False = 0
SELECT ... ,
CASE WHEN iFactor Not In (0,1)
THEN CASE WHEN iGreenReport = @.True
THEN CASE WHEN iRunInData = @.True
THEN iValue * iFactor
ELSE iValue
END
WHEN iGreenReport = @.False
THEN CASE WHEN iRunInData = @.False
THEN iValue / iFactor
ELSE iValue
END
END
ELSE iValue
END As statAdjustData
FROM ...
WHERE ...
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
Yk4XGtIPaWOgPNdJC8g+Zz1r
=ZTUB
--END PGP SIGNATURE--
StvJston wrote:
> I am at the last hurdle on converting a very large chunk of VB code that
> massages a recordset to produce a report.
> This question relates to my previous question from 3/2 and the ddl that I
> posted for that question.
> I need to replace the following vb code with SQL and I think I can do it
> with a Case statement but would really appreciate some input on this. The
VB
> code follows the url for the original message.
> http://msdn.microsoft.com/newsgroup...76C%2C774F24A2-
F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
> '***********Code Start************
> Dim TempValue As Single
> If iFactor <> 0 And iFactor <> 1 Then ' Pursue adjustment
> If iGreenRpt Then ' A green report has been requested
> If iRunInData Then 'Data or spec is not green so make adjustme
nt
> TempValue = iValue * iFactor
> Else ' Take data as is "Green"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> Else 'Non-green or Runin/"Market Rating" report has been requested
> If Not iRunInData Then 'Data or spec is green so make adjustme
nt
> TempValue = iValue / iFactor
> Else 'Take data as is "Runin"
> TempValue = iValue
> End If
> statAdjustData = TempValue
> End If ' Green or Runin/Market data report requested
> Else ' iFactor = 1 or 0 therefore no need to adjust
> statAdjustData = iValue
> End If ' iFactor = To Or <> 1 or 0
> '********Code End*****************|||Thanks for the reply.
I ended up doing this as a function and it seems to work very well and is
fast.
Stvjston
CREATE FUNCTION dbo.StatAdjustData ( @.IVal as FLOAT, @.iFactor as FLOAT,
@.iGreen as BIT, @.iRunnin as bit)
RETURNS FLOAT
BEGIN
DECLARE @.RetVal as FLOAT
IF @.iFactor <> 0 and @.iFactor <> 1
BEGIN
IF @.iGreen = -1
if @.iRunnin = -1
BEGIN
SET @.RetVAL = @.iVal * @.iFactor
END
ELSE
BEGIN
SET @.RetVAl = @.iVal
END
ELSE
IF @.iRunnin <> -1
BEGIN
SET @.RetVal = @.iVal / @.iFactor
END
ELSE
BEGIN
SET @.RetVal = @.iVal
END
END
ELSE
BEGIN
SET @.RETVAL = @.IvAL
END
RETURN (@.RetVal)
END
"MGFoster" wrote:

> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Perhaps:
> DECLARE @.True TINYINT, @.False TINYINT
> SET @.True = 1
> Set @.False = 0
> SELECT ... ,
> CASE WHEN iFactor Not In (0,1)
> THEN CASE WHEN iGreenReport = @.True
> THEN CASE WHEN iRunInData = @.True
> THEN iValue * iFactor
> ELSE iValue
> END
> WHEN iGreenReport = @.False
> THEN CASE WHEN iRunInData = @.False
> THEN iValue / iFactor
> ELSE iValue
> END
> END
> ELSE iValue
> END As statAdjustData
> FROM ...
> WHERE ...
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBRAzCQoechKqOuFEgEQJEMwCePBFQCl7kq6CW
NHM1LTWmKqgLGf8An29S
> Yk4XGtIPaWOgPNdJC8g+Zz1r
> =ZTUB
> --END PGP SIGNATURE--
>
> StvJston wrote:
2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
>