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
>

help converting filetime bigint to SQL datetime

Hello,
I am using a DTS package to putt information from a Windows 2003 Active
Directory into SQL 2000.
The DTS package connects to AD, which is set up as a linked server, and
reads several attributes of user accounts, including the accountExpires and
lastLogonTimestamp columns.
The accountExpires and lastLogonTimestamp fields store dates and times. I
found an excellent technet article by the Scripting Guys that explains that
this value is stored in AD as a 64-bit integer, and that it represents the
number of 100-nanosecond units since January 1, 1601.
Unfortunately, SQL Server doesn't store dates that way. The datetime type in
SQL server is the number of three-hundredths of a second since 01/01/1753.
I thought about writing a conversion function, but I realized two things: 1)
someone must have encountered this before, and 2) it's not as easy as it
might seem at first glance because of leap years.
Moreover, I can't do anything about it in the DTS package with a custom
ActiveX Transformation because (I think) the ActiveX implementation in DTS
only handles 32-bit integers.
Can anyone help me with this? I really need to store those two columns in
SQL Server.
Thank you!
- Michael GibbonsHi Michael
You could try subtracting the difference (in 100 ns units) between the two
base dates, convert the remainder to nano-seconds and then adding to the SQL
Server base date.
John
"Michael" wrote:

> Hello,
> I am using a DTS package to putt information from a Windows 2003 Active
> Directory into SQL 2000.
> The DTS package connects to AD, which is set up as a linked server, and
> reads several attributes of user accounts, including the accountExpires an
d
> lastLogonTimestamp columns.
> The accountExpires and lastLogonTimestamp fields store dates and times. I
> found an excellent technet article by the Scripting Guys that explains tha
t
> this value is stored in AD as a 64-bit integer, and that it represents the
> number of 100-nanosecond units since January 1, 1601.
> Unfortunately, SQL Server doesn't store dates that way. The datetime type
in
> SQL server is the number of three-hundredths of a second since 01/01/1753.
> I thought about writing a conversion function, but I realized two things:
1)
> someone must have encountered this before, and 2) it's not as easy as it
> might seem at first glance because of leap years.
> Moreover, I can't do anything about it in the DTS package with a custom
> ActiveX Transformation because (I think) the ActiveX implementation in DTS
> only handles 32-bit integers.
> Can anyone help me with this? I really need to store those two columns in
> SQL Server.
> Thank you!
> - Michael Gibbons

Help Converting Character string to smalldatetime data type

I am attempting to pass a string variable from a form to an SQL statement an
d
I get this error "Syntax error converting character string to smalldatetime
data type"
Your assistance is appreciated in advance....
Here is my code
<%
Dim TESTTHIS__MMColParam1
TESTTHIS__MMColParam1 = "0"
If (Request.Form("StartDate") <> "") Then
TESTTHIS__MMColParam1 = Request.Form("StartDate")
End If
%>
<%
Dim TESTTHIS__MMColParam2
TESTTHIS__MMColParam2 = "0"
If (Request.Form("EndDate") <> "") Then
TESTTHIS__MMColParam2 = Request.Form("EndDate")
End If
%>
<%
Dim TESTTHIS
Dim TESTTHIS_numRows
Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
(SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
(SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTBL"
TESTTHIS.CursorType = 0
TESTTHIS.CursorLocation = 2
TESTTHIS.LockType = 1
TESTTHIS.Open()
TESTTHIS_numRows = 0
%>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1Run a Profiler trace to see the actual select statement being sent to SQL
Sever. Also, consider using parameters instead of concatenating values.
Parameters are more secure and you don't have to double-up embedded quotes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chamark via webservertalk.com" <u21870@.uwe> wrote in message
news:6168f7c85cf47@.uwe...
>I am attempting to pass a string variable from a form to an SQL statement
>and
> I get this error "Syntax error converting character string to
> smalldatetime
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") +
> "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site)
> DERIVEDTBL"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||Also keep in mind that if something is a valid date it still might not
be able to be converted into smalldatetime (for example dates before
19000101 and after 20790606)
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Chamark via webservertalk.com wrote:
> I am attempting to pass a string variable from a form to an SQL statement
and
> I get this error "Syntax error converting character string to smalldatetim
e
> data type"
> Your assistance is appreciated in advance....
> Here is my code
> <%
> Dim TESTTHIS__MMColParam1
> TESTTHIS__MMColParam1 = "0"
> If (Request.Form("StartDate") <> "") Then
> TESTTHIS__MMColParam1 = Request.Form("StartDate")
> End If
> %>
> <%
> Dim TESTTHIS__MMColParam2
> TESTTHIS__MMColParam2 = "0"
> If (Request.Form("EndDate") <> "") Then
> TESTTHIS__MMColParam2 = Request.Form("EndDate")
> End If
> %>
> <%
> Dim TESTTHIS
> Dim TESTTHIS_numRows
> Set TESTTHIS = Server.CreateObject("ADODB.Recordset")
> TESTTHIS.ActiveConnection = MM_CSSMetricsCONN_STRING
> TESTTHIS.Source = "SELECT Team, SumofInAdherenceSecsQty /
> (SumofInAdherenceSecsQty + SumofOutOfAdherenceSecsQty) AS Adherence FROM
> (SELECT TOP 1000 Team, SUM(OutOfAdherenceSecsQty) AS
> SumofOutOfAdherenceSecsQty, SUM(InAdherenceSecsQty) AS
> SumofInAdherenceSecsQty FROM bo.[National Call Stats] WHERE (Site =
> 'Dallas') AND ([Date] >='" + Replace(TESTTHIS__MMColParam1, "'", "''") + "')
> AND ([Date] <= '" + Replace(TESTTHIS__MMColParam2, "'", "''") + "')
> GROUP BY Team, Site ORDER BY Team, Site) DERIVEDTB
L"
> TESTTHIS.CursorType = 0
> TESTTHIS.CursorLocation = 2
> TESTTHIS.LockType = 1
> TESTTHIS.Open()
> TESTTHIS_numRows = 0
> %>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||I am able to see that it is getting to the server because I get a return of
the column headers but I am not getting any data. When I hard code the dates
in the query works fine. I am sending for example 8/1/2005 from the form. If
I take out the two / and send 812005 I get an overflow error?
Dan Guzman wrote:
>Run a Profiler trace to see the actual select statement being sent to SQL
>Sever. Also, consider using parameters instead of concatenating values.
>Parameters are more secure and you don't have to double-up embedded quotes.
>
>[quoted text clipped - 43 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200606/1|||On Wed, 07 Jun 2006 20:12:46 GMT, "Chamark via webservertalk.com"
<u21870@.uwe> wrote:

>I am able to see that it is getting to the server because I get a return of
>the column headers but I am not getting any data. When I hard code the date
s
>in the query works fine. I am sending for example 8/1/2005 from the form. I
f
>I take out the two / and send 812005 I get an overflow error?
You will get an error, as '812005' does not convert to datetime:
select convert(datetime,'812005')
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
The most reliable format for a date string is YYYYMMDD:
select convert(datetime,'20050801')
--
2005-08-01 00:00:00.000
Roy Harvey
Beacon Falls, CTsql