Format Date for SQL query 3

Shelby500 · Oct 25, 2013

Below is my code. I need to create some variables that I can put in my SQL query. They need to increment by 15 minutes each time the script runs....therefore pulling data every 15 minutes. Below is what I have so far. I'm just not sure how to do the incrementing each time the script runs?

Code:

Dim strCon
Dim SQLArray
Dim strTime
Dim stpTime

theDate = date()
newDate = DateAdd("D", -1, theDate)
strYear = datepart("yyyy", Now())
strMonth = datepart("m", Now())
strDay = datepart("d", Now())
strHour = datepart("h", Now())
strMin = "00"
strMin2 = "15"
strMin3 = "30"
strMin4 = "45"

strTime = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin
strTime2 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin2
strTime3 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin3
strTime4 = strYear & "-" & strMonth & "-" & strDay & " " & strHour & ":" & strMin4

strCon = "Data Source=<servername>:3203/SE001P;password=passwd;User ID=userid;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
'Has data
Set oRs = oCon.Execute("SELECT TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI'), count(*) FROM t_messaging_log lg WHERE lg.logtime > TO_DATE('"&strTime&"', 'YYYY-MM-DD HH24:MI') AND lg.logtime < TO_DATE('"&stpTime&"', 'YYYY-MM-DD HH24:MI') AND lg.FUNCTIONCATEGORY = 'AgentApp-heAppSubmissionDelegate'   GROUP BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI') ORDER BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI')")

If oRs.BOF or oRs.EOF = "True" then
	strEmpty = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;><TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR></TABLE>"
	strResults = strEmpty & vbCr & "<H1><b>Sales Express ACAPS Submit Numbers - Zero in last 15 minutes/H1>"
	sendemail "blah@blah.com","blah@blah.com", "blah@blah.com", "TESTING Submit Numbers - Quiet last 15 minutes - " & date, strResults
	Wscript.quit
Else
SQLArray = oRs.GetRows(,,Array(CStr("TO_CHAR(LG.LOGTIME,'HH24')"),CStr("TO_CHAR(LG.LOGTIME,'MI')"),CStr("COUNT(*)")))
End If

'Begin writing array to variable

strBegin = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;>"
strHead = "<TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT(*)",50) & "</center></TD></TR>"
strRows = ""

for i = 0 to ubound(SQLArray,2)
   strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
next
strResults = strBegin & strHead & strRows & "</TABLE>" 

oCon.Close
Set oRs = Nothing
Set oCon = Nothing

'Send email notification function
function sendEmail (strTo, strCC, strFrom, strSubject, strBody)
   set objEmail = CreateObject("CDO.Message")
   objEmail.From = strFrom
   objEmail.To = strTo
   objEmail.CC = strCC
   objEmail.Subject = strSubject
   objEmail.HTMLBody = strBody
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "smtpservername"
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
   objEmail.Configuration.Fields.Update
   objEmail.Send
end function

Shelby500 · Oct 30, 2013

If I remove the sum=0 or sum=-1 altogether it returns the correct number but it returns it 3 times. So rather than returning 10 it returns 101010.

Shelby500 · Oct 30, 2013

Also, shouldn't it be sum(SQLArray,2) rather than sum(SQLArray,0) since we are looking at the 3rd column in the array?

PHV · Oct 30, 2013

The function is not suitable for calculating the sum you want as it needs 3 parameters: the array, the dimension and the index.
To get the sum you want:

Code:

sum = 0
For i = 0 To UBound(SQLArray,2)
  sum = sum + SQLArray(2,i)
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Shelby500 · Oct 30, 2013

For some reason I get a type mismatch on

Code:

sum = sum + SQLArray(2,i)

Geates · Oct 30, 2013

I don't understand. Only the array and dimension (i called it 'index') need to be passed. The index (i) is determined by the for loop. Additionally, doesn't ubound(SQLArray, 2) refers to the second dimension and SQLArray(2) refers to the third. The example I posted suggests that the function works. Where does it go wrong?

- Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Geates · Oct 30, 2013

For some reason I get a type mismatch on"
likely because SQLArray(2, i) does not contain a number (string or otherwise). Verify that the array your want to sum contains number, not literals.

- Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

PHV · Oct 30, 2013

Geates, your function should be like this:

Code:

Function sum(arr, dimension, col)
sum = 0
For i = 0 To UBound(arr, dimension)
    If dimension = 2 Then
        sum = sum + arr(col, i)
    Else
        sum = sum + arr(i, col)
    End If
Next
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Shelby500 · Oct 30, 2013

Well...it contains the count. I would assume that SQL creates numbers when it does a count, correct? Below is an example output. It also works for SQLArray(0,i) and SQLArray(1,i) but not for SQLArray(2,i).

Code:

TO_CHAR(LG.LOGTIME,'HH24') 	TO_CHAR(LG.LOGTIME,'MI') 	COUNT 
10					15			1
10					19			2
10					25			1
10					27			1
10					28			1
10					29			2
10					30			1
10					40			1

Geates · Oct 30, 2013

@Shelby500
yes, but the first value in that column is "COUNT" which is not a number. change the for loop to start at 1 instead of 0.

@PHV
Hmmm... Let me chew on that. I don't see why that would work.

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Geates · Oct 30, 2013

@Shelby500
Ignore what I said before about chaning [tt]sum = -1[/tt]. That'll make the function return 1 less then actual.

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 30, 2013

Changing it to 1 on the for loop didn't help. It still says type mismatch. So far none of the functions are working.

PHV · Oct 30, 2013

And this ?

Code:

sum = 0
For i = 0 To UBound(SQLArray,2)
  sum = sum + CLng(SQLArray(2,i))
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Shelby500 · Oct 30, 2013

Ok, here is what I have right now. It is returning the number

Code:

in an endless loop.

Code:

for i = 0 to ubound(SQLArray,2)
wscript.echo sum(SQLArray,0)
	If sum(SQLArray,0) = 0 then
		....code here....
		Wscript.quit
	else
		strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>"

and

Code:

function sum(arr, index)
   sum = 0
   for i = 1 to ubound(arr, (index + 1))
      sum = sum + arr(index, i)
   next
end function

Geates · Oct 30, 2013

@PHV
After some further testing, I now understand why. Although, I didn't see a need for the if statement.

Code:

Function sum(arr, dimension, index)
	sum = 0
	For i = 0 To UBound(arr, dimension)
		sum = sum + arr(index, i)
		msgbox "arr(" & index & ", " & i & ") = " & arr(index, i) & vbNewLine & "sum: " & sum
	Next
End Function 

'EXAMPLE
dim var(3, 3)
var(0, 0) = 1
var(0, 1) = 2
var(0, 2) = 3
var(0, 3) = 4
var(1, 0) = 5
var(1, 1) = 6
var(1, 2) = 7
var(1, 3) = 8
var(2, 0) = 9
var(2, 1) = 10
var(2, 2) = 11
var(2, 3) = 12
var(3, 0) = 13
var(3, 1) = 14
var(3, 2) = 15
var(3, 3) = 16

msgbox sum(var, 1, 3) 'output is 58

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

Shelby500 · Oct 30, 2013

Here is PHV's. It returns

Code:

about six times. This is actually the correct SUM. Thanks PHV and Geates for all the help.

Code:

for i = 0 to ubound(SQLArray,2)
wscript.echo sum(SQLArray)
	If sum(SQLArray) = 0 then
		....code here....
		Wscript.quit
	else
		strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>"

and

Code:

function sum(arr)
sum = 0
For j = 0 To UBound(arr,2)
  sum = sum + CLng(arr(2,j))
Next
end function

PHV · Oct 30, 2013

Geates, uses dim var(4, 3) for example and then msgbox sum(var, 1, 3) followed by msgbox sum(var, 2, 3) to see what I mean.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Shelby500 · Oct 30, 2013

One more question. Should I start the For Loop at 1 rather than 0 or does that make any difference since I am getting the correct SUM now?

Shelby500 · Oct 30, 2013

Just so the thread isn't left unfinished. Here is the final script in all its glory. Thanks again PHV and Geates. If you see anything wrong or code that can be improved please let me know. I'm a newbie and appreciate all of the insight.

Code:

Dim strCon
Dim SQLArray

theDate = date()
newDate = DateAdd("D", -1, theDate)

'Allow for 30 minutes of data
sec_since = timer mod 2400
sec_until = 1800 - sec_since

'Add 2 hours to date/time to adjust to central time from pacific time
fromDate = dateAdd("h",2,dateAdd("s", (timer - sec_since), date()))
toDate = dateAdd("h",2,dateAdd("S", (timer + sec_until), date()))

strFromDate = vbsFormat(fromDate, "yyyy-MM-dd hh:mm")
strToDate = vbsFormat(toDate, "yyyy-MM-dd hh:mm")

'Connect String
strCon = "Data Source=servername:3203/SE001P;password=passwd;User ID=userid;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;"

Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection")
Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset")
oCon.Open strCon
'Has data
Set oRs = oCon.Execute("SELECT TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI'), count(*) FROM t_messaging_log lg WHERE lg.logtime > TO_DATE('"&strFromDate&"', 'YYYY-MM-DD HH24:MI') AND lg.logtime < TO_DATE('"&strToDate&"', 'YYYY-MM-DD HH24:MI') AND lg.FUNCTIONCATEGORY = 'AgentApp-heAppSubmissionDelegate'   GROUP BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI') ORDER BY TO_CHAR(lg.logtime, 'HH24'),TO_CHAR(lg.logtime, 'MI')")

If oRs.BOF or oRs.EOF = "True" then
	strEmpty = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;><TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT",50) & "</center></TD></TR></TABLE>"
	strResults = strEmpty & vbCr & "<H2><b> Submit Numbers - Zero in last 15 minutes</H2>"
	sendemail "blah@blah.com","", "blah@blah.com", "TESTING  Submit Numbers - Quiet last 15 minutes - " & date, strResults
	Wscript.quit
Else
SQLArray = oRs.GetRows(,,Array(CStr("TO_CHAR(LG.LOGTIME,'HH24')"),CStr("TO_CHAR(LG.LOGTIME,'MI')"),CStr("COUNT(*)")))
End If

'Begin writing array to variable
strBegin = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;>"
strHead = "<TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT",50) & "</center></TD></TR>"
strRows = ""

for i = 0 to ubound(SQLArray,2)
	If sum(SQLArray) = 0 then
		strEmpty = "<TABLE BORDER=2 CELLPADDING=2 CELLSPACING=2 style=table-layout:fixed;><TR><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'HH24')",50) & "</center></TD><TD><center><b>" & fixStrLen("TO_CHAR(LG.LOGTIME,'MI')",50) & "</center></TD><TD><center><b>" & fixStrLen("COUNT",50) & "</center></TD></TR></TABLE>"
		strResults = strEmpty & vbCr & "<H2><b> Submit Numbers - Zero in last 15 minutes</H2>"
		sendemail "blah@blah.com","", "blah@blah.com", "TESTING  Submit Numbers - Quiet last 15 minutes - " & date, strResults
		Wscript.quit
	else
		strRows = strRows & "<TR style=WORD-BREAK:BREAK-ALL;><TD NOWRAP><center>" & SQLArray(0, i) & "</center></TD><TD NOWRAP><center>" & SQLArray(1, i) & "</center></TD><TD style=word-wrap: break-word;><center>" & SQLArray(2, i) & "</center></TD></TR>"
	End If
next
strResults = strBegin & strHead & strRows & "</TABLE>" 

oCon.Close
Set oRs = Nothing
Set oCon = Nothing

'Quit the script after success
WScript.quit

'Format Output
function fixStrLen(strValue, intLength)
   fixStrLen = left(strValue & space(intLength), intLength)
end function

'Format Output
Function Lpad (inputStr, padChar, lengthStr)  
    Lpad = string(lengthStr - Len(inputStr),padChar) & inputStr  
End Function

'Format Date/Time
Public Function vbsFormat(Expression, Format)
    vbsFormat = CoreFormat("{0:" & Format & "}", Expression)
End Function

'Allows more of the .NET formatting functionality to be used directly if required
Public Function CoreFormat(Format, Expression)
    CoreFormat = Expression
    On Error Resume Next
    With CreateObject("System.Text.StringBuilder")
        .AppendFormat Format, Expression
        If Err=0 Then CoreFormat = .toString
    End With
End Function

function sum(arr)
sum = 0
For j = 0 To UBound(arr,2)
  sum = sum + CLng(arr(2,j))
Next
end function

'Send email notification function
function sendEmail (strTo, strCC, strFrom, strSubject, strBody)
   set objEmail = CreateObject("CDO.Message")
   objEmail.From = strFrom
   objEmail.To = strTo
   objEmail.CC = strCC
   objEmail.Subject = strSubject
   objEmail.HTMLBody = strBody
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "servername"
   objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
   objEmail.Configuration.Fields.Update
   objEmail.Send
end function

Geates · Oct 30, 2013

@PHV
I dig.

-Geates

http://ns7.webmasters.com/caspdoc/html/vbscript_language_reference.htm

strongm · Oct 31, 2013

Glad to see my vbsFormat function making its way in the world ...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Format Date for SQL query 3

IS-IT--Management

IS-IT--Management

IS-IT--Management

MIS

IS-IT--Management

Programmer

Programmer

MIS

IS-IT--Management

Programmer

Programmer

IS-IT--Management

MIS

IS-IT--Management

Programmer

IS-IT--Management

MIS

IS-IT--Management

IS-IT--Management

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor