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!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format Date for SQL query 3

Status
Not open for further replies.

Shelby500

IS-IT--Management
Oct 16, 2013
44
US
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
 
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.
 
Also, shouldn't it be sum(SQLArray,2) rather than sum(SQLArray,0) since we are looking at the 3rd column in the array?
 
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
 
For some reason I get a type mismatch on
Code:
sum = sum + SQLArray(2,i)
 
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



 
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
 
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
 
Changing it to 1 on the for loop didn't help. It still says type mismatch. So far none of the functions are working.
 
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
 
Ok, here is what I have right now. It is returning the number
Code:
20
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
 
@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

 
Here is PHV's. It returns
Code:
10
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
 
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
 
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?
 
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
 
Glad to see my vbsFormat function making its way in the world ... :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top