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!

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

SQL with loop for each of the last 365 days 3

Status
Not open for further replies.
johnpayback

johnpayback

IS-IT--Management
Oct 2, 2006
110
US
I need to have this statement pull the count for each day for the past 365 days. Currently it will pull the count every night for the previous day but I need it to pull the count for each of the past 365 days and insert them separately based on each day. I do not need to get the total count for the past 365 days...that is easy by changing the -1 to -365. Is there a way to do this with a loop or how can I accomplish this with VBScript? Below is the SQL statement that I need to use in the script.

Code: 
Select count(*) as CallCount, 
dateadd(d,-1,getdate()) as CallDate, 
'CON' as Site  
From dbo.master_index where FileDateTime >=  convert(char(10),dateadd(d,-1,getdate()),110) AND FileDateTime <convert(char(10),getdate(),110)

thks JP
 
Sort by date Sort by votes
I tried the following and was able to get a SQL Query string similar to the one you posted for each day in the last 365 days. If you want to look at the output I would suggest running it using cscript.exe from the command prompt.

Option Explicit
'On Error Resume Next

Dim StartDate, i, Date1, sqlQuery

StartDate = Date()

For i = -365 To -1
Date1 = DateAdd("d", i, StartDate)
sqlQuery = "Select count(*) as CallCount," & _
Date1 & " as CallDate," & _
"'CON' as Site " & _
"From dbo.master_index where FileDateTime >= convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"
WScript.Echo sqlQuery
Next

dm4ever
--------------------------------------------------------------------------------
My approach to things: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
Thank you....one question though. How can I execute this all at one time? The data is pulled from a different server's database and then inserted into a database on the server that this script will run on. Two servers...two databases. I'm not sure how to do this. Any ideas?

JP
 
Upvote 0 Downvote
What does return
Code: 
Select count(*) as CallCount, 
convert(char(10),FileDateTime  ,110)as CallDate, 
'CON' as Site  
From dbo.master_index where FileDateTime Between  convert(char(10),getdate()-365,110) AND convert(char(10),getdate(),110)
Group by convert(char(10),FileDateTime  ,110)

i did not test this but it should return count of call by each day for the last 365 days
 
Upvote 0 Downvote
Yes, it does return the information that I need but I need to gather that information and then push it to the other database on a different server. I just need a way to update the database with the information that the script above gives me.

JP
 
Upvote 0 Downvote
Here is what I have so far. I just don't know how to get this to execute on the "SFLAB" and put the data it returns on the "LUBLAB". Somehow I need to get the data from the "SFLAB" on the "LUBLAB" but I'm not sure how to do it. If you have an idea I would be in your debt.

I've added some code for the "LUBLAB" server and db but I don't know where to go from here. Two connections and two recordsets but how do I get the data from the first one on the second one? I appreciate it.

Before you ask....the userID and password for the databases are not set to "sa" and "passwd". I've changed them in order to post here.


Code: 
Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

Dim objFSO

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
'strServer2 = "LUBLAB"

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
'objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecorderDB;"

StartDate = Date()

For i = -397 To -1
    Date1 = DateAdd("d", i, StartDate)
    sqlQuery = "Select count(*) as CallCount," & _
        Date1 & " as CallDate," & _
        "'LUB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"
'    WScript.Echo sqlQuery
objrecordset.open sqlQuery , objconnection, adopenstatic, adlockoptimistic
Next 

objRecordSet.Close
objConnection.Close
WScript.Quit

JP
 
Upvote 0 Downvote
With the example you posted you may try something like this.

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecorderDB;"
objrecordset2.Open "Select * From YOURTABLE", objconnection, adopenstatic, adlockoptimistic

For i = -397 To -1
    Date1 = DateAdd("d", i, StartDate)
    sqlQuery = "Select count(*) as CallCount," & _
        Date1 & " as CallDate," & _
        "'LUB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"

	objrecordset.open sqlQuery , objconnection, adopenstatic, adlockoptimistic
	objrecordset2.AddNew
	objrecordset2.Fields.Item("FIELD1") = objrecordset.Fields.Item("CallCount")
	objrecordset2.Fields.Item("FIELD2") = objrecordset.Fields.Item("CallDate")
	objrecordset2.Fields.Item("FIELD3") = objrecordset.Fields.Item("Site")
	objrecordset2.Update
	objrecordset.Close
Next

objrecordset2.Close
objconnection2.Close
objConnection.Close

dm4ever
--------------------------------------------------------------------------------
My approach to things: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
Oh, you would need to change YOURTABLE and FIELD1, FIELD2, FIELD3 to those that correspond to your table and fields.

dm4ever
--------------------------------------------------------------------------------
My approach to things: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
Great! I'll try this and let you know. If this works you have saved me. I was cleaning up this database and deleted the past year's data. I just needed a way to get it back without having to do it one at a time and this looks like it will do the trick. I'll let you know. Thanks again!

JP
 
Upvote 0 Downvote
Okay....I tried this but was unable to get it to work. So, I changed it to the below and I get the error message, "Unable to convert datetime to character string" on the line that opens the "sqlQuery". Any other ideas? This is starting to confuse me so the code below may be way off. Let me know what you think.

Code: 
Option Explicit
'On Error Resume Next

Dim strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery
Dim date1, date2

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecDB;"

For i = -365 To -1
    Date1 = DateAdd("d", i, StartDate)
	Date2 = DateAdd("d", 1, Date1)
    sqlQuery = "Select count(*) as CallCount," & _
        "convert(char(10)," & date1 & ",105) as CallDate," & _
        "'LAB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & Date2 & ",110)"

    objrecordset2.open sqlQuery, objconnection2, adopenstatic, adlockoptimistic
	objrecordset2.Close
	objrecordset.Open "INSERT INTO LoggerRecStats (NumOfRecs, RecDate, Site) VALUES (CallCount, CallDate, Site)", objconnection, adopenstatic, adlockoptimistic
    objrecordset.Close
Next

objrecordset2.Close
objconnection2.Close
objConnection.Close

JP
 
Upvote 0 Downvote
Well in the example you just posted you open the recordset2 and close it, never doing anything with it. What error did you get when you tried the other way and what did that look like?

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
I noticed that. I guess my question is how do I update the table that the data will go into? The script that you posted does not state a table name. It only has the database name and the column names. So, the code below is not functional but just to show you what I am thinking. Hopefully you will see what I am trying to get at. I did not mean for the other posted code to be a solution but just to give you an idea of what I was trying to do. I hope this one explains it a little more. Again this below is not a solution but my way of trying to show what I am wanting to do. Thank you for bearing with me.

Code: 
Option Explicit
'On Error Resume Next

Dim strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery
Dim date1

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;"
objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecDB;"

For i = -365 To -1
    Date1 = DateAdd("d", i, StartDate)
    sqlQuery = "Select count(*) as CallCount," & _
        "'" & Date1 & "' as CallDate," & _
        "'LAB' as Site " & _
        "From dbo.master_index where FileDateTime >= convert(char(10)," & "'" & DateAdd("d", 1, Date1) & "'" & ",110) AND FileDateTime <convert(char(10)," & "'" & DateAdd("d", 1, Date1) & "'" & ",110)"

	sqlQuery2 = "INSERT INTO LoggerRecStats (NumOfRecs, RecDate, Site) VALUES (CallCount, CallDate, Site)"
    
    objrecordset2.open sqlQuery, objconnection2, adopenstatic, adlockoptimistic
	objrecordset.Open sqlQuery2, objconnection, adopenstatic, adlockoptimistic
'	objrecordset.Close
'    objrecordset.AddNew
'    objrecordset.Fields.Item("NumOfRecs") = objrecordset2.Fields.Item("CallCount")
'    objrecordset.Fields.Item("RecDate") = objrecordset2.Fields.Item("CallDate")
'    objrecordset.Fields.Item("Site") = objrecordset2.Fields.Item("Site")
'    objrecordset.Update
'    objrecordset.Close
Next

objrecordset2.Close
objrecordset.Close
objconnection2.Close
objConnection.Close

JP
 
Upvote 0 Downvote
You can insert data using a recordset without having to use a "Insert Into" query. That's pretty much what the lines objRecordSet2.AddNew and objRecordSet2.Update and everything in between do. I did forget to put objRecordSet.Fields.Item("NumOfRecs").Value to assign a value though.

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery

Const OpenFileForReading = 1:Const OpenFileForWriting = 2:Const OpenFileForAppending = 8
Const adopenstatic = 3:Const adlockoptimistic = 3:Const aduseclient = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;" 'open connection to source DB
objconnection2.open "driver=SQL Server;server="&strserver2&";uid=sa;pwd=passwd;database=RecorderDB;" ' open connection to destination DB
objrecordset2.Open "Select * From LoggerRecStats", objconnection, adopenstatic, adlockoptimistic 'Create RecordSet for destination DB

For i = -397 To -1
    Date1 = DateAdd("d", i, StartDate)
    sqlQuery = "Select count(*) as CallCount," & _
        Date1 & " as CallDate," & _
        "'LUB' as Site " & _
        "From dbo.master_index where FileDateTime >=  convert(char(10)," & Date1 & ",110) AND FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"

    objrecordset.open sqlQuery , objconnection, adopenstatic, adlockoptimistic 'open RecordSet containing data from your SQLQuery
    objrecordset2.AddNew 'add new record to destination DB; this would be like your insert query
    'assign your values to the corresponding fields; just like the values section of your query
    objrecordset2.Fields.Item("NumOfRecs").Value = objrecordset.Fields.Item("CallCount") 
    objrecordset2.Fields.Item("RecDate").Value = objrecordset.Fields.Item("CallDate")
    objrecordset2.Fields.Item("Site").Value = objrecordset.Fields.Item("Site")
    objrecordset2.Update 'update the recordset of the destination DB
    objrecordset.Close 'close the recordset of the source DB
Next

objrecordset2.Close 'close the recordset of the destination
objconnection2.Close 'close connection to destination
objConnection.Close 'close connection to source

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
I looked at PWise's SQL query and it is far more efficient using it instead of the method I suggested. You may be able to do something like this with it.

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery

Const OpenFileForReading = 1:Const OpenFileForWriting = 2:Const OpenFileForAppending = 8
Const adopenstatic = 3:Const adlockoptimistic = 3:Const aduseclient = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()
sqlQuery = "Select count(*) as CallCount," & _
"convert(char(10),FileDateTime  ,110)as CallDate," & _
"'CON' as Site  " & _
"From dbo.master_index where FileDateTime Between " & _
"convert(char(10),getdate()-365,110) AND convert(char(10),getdate(),110)" & _
"Group by convert(char(10),FileDateTime  ,110)"

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=TestDB;" 'open connection to source DB
objconnection2.open "driver=SQL Server;server="&strServer2&";uid=sa;pwd=passwd;database=RecorderDB;" ' open connection to destination DB
objrecordset.Open sqlQuery, objconnection, adopenstatic, adlockoptimistic 'create a recordset with all the data you need
objrecordset2.Open "Select * From LoggerRecStats", objconnection, adopenstatic, adlockoptimistic 'Create RecordSet for destination DB

Do Until objrecordset.EOF 'loop through you data collected from the source
    objrecordset2.AddNew 'add new record to destination DB; this would be like your insert query
    'assign your values to the corresponding fields; just like the values section of your query
    objrecordset2.Fields.Item("NumOfRecs").Value = objrecordset.Fields.Item("CallCount") 
    objrecordset2.Fields.Item("RecDate").Value = objrecordset.Fields.Item("CallDate")
    objrecordset2.Fields.Item("Site").Value = objrecordset.Fields.Item("Site")
    objrecordset2.Update 'update the recordset of the destination DB
    objrecordset.MoveNext
Loop

objrecordset.Close 'close the source recordset
objrecordset2.Close 'close the recordset of the destination
objConnection.Close 'close connection to source
objconnection2.Close 'close connection to destination
[/code'

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
Okay...that works but it doesn't accomplish what I need. Below is what I've come up with but it still isn't working correctly. I have to get the count for each of the last 365 days and put that info in the database on a separate row. I've given an example output below. Let me know if this makes more sense. The last post worked but it only added one entry into the database. Hopefully this helps explain a little better.

Code: 
Site  RecDate                  NumOfRecs
LAB   2005-12-01 00:00:00.000  555
LAB   2005-12-02 00:00:00.000  333
LAB   2005-12-03 00:00:00.000  444
LAB   2005-12-04 00:00:00.000  777
LAB   2005-12-05 00:00:00.000  222
...
...
...

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, strServer, strServer2, objconnection, objconnection2
Dim objrecordset, objrecordset2, StartDate, i, sqlQuery

Const OpenFileForReading = 1:Const OpenFileForWriting = 2:Const OpenFileForAppending = 8
Const adopenstatic = 3:Const adlockoptimistic = 3:Const aduseclient = 3

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "SFLAB"
strServer2 = "LUBLAB"
StartDate = Date()

For i = -365 To -1
    Date1 = DateAdd("d", i, StartDate)

    sqlQuery = "Select count(*) as CallCount," & _
        "convert(char(10)," & Date1 & ",110) as CallDate," & _
        "'LAB' as Site " & _
        "From dbo.master_index where FileDateTime >=" & _
		"convert(char(10)," & DateAdd("d", 1, Date1) & ",110) AND " & _
        "FileDateTime <convert(char(10)," & DateAdd("d", 1, Date1) & ",110)"

Set objconnection = CreateObject("ADODB.Connection")
Set objconnection2 = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
Set objrecordset2 = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=passwd;database=RecorderDB;" 'open connection to source DB
objconnection2.open "driver=SQL Server;server="&strServer2&";uid=sa;pwd=passwd;database=TestDB;" ' open connection to destination DB
objrecordset.Open sqlQuery, objconnection, adopenstatic, adlockoptimistic 'create a recordset with all the data you need
objrecordset2.Open "Select * From LoggerRecStats", objconnection2, adopenstatic, adlockoptimistic 'Create RecordSet for destination DB

    objrecordset2.AddNew 'add new record to destination DB; this would be like your insert query
    'assign your values to the corresponding fields; just like the values section of your query
    objrecordset2.Fields.Item("NumOfRecs").Value = objrecordset.Fields.Item("CallCount") 
    objrecordset2.Fields.Item("RecDate").Value = objrecordset.Fields.Item("CallDate")
    objrecordset2.Fields.Item("Site").Value = objrecordset.Fields.Item("Site")
    objrecordset2.Update 'update the recordset of the destination DB
Next

objrecordset.Close 'close the source recordset
objrecordset2.Close 'close the recordset of the destination
objConnection.Close 'close connection to source
objconnection2.Close 'close connection to destination

JP
 
Upvote 0 Downvote
When I actually get code that I think works I get the error below. I can't figure out how to keep this from happening but I think if I could figure it out I could get it to work. Anyway...this may or may not be the problem.

Code: 
Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

JP
 
Upvote 0 Downvote
Which method did you try? The one that does this

Do Until objrecordset.EOF
Loop

or this

For i = -397 To -1
Next

If you're getting an error that the format isn't a Date, then you can probably change it prior to inserting it with the CDate function. At this point I would try seeing what data you are getting with the Query PWise suggested by changing the do until to the following(run using cscript). If the data you want is pulled out, then the next step is putting it into your destination table. If you feel more comfortable using an Insert Into SQL query then use that.

Do Until objrecordset.EOF 'loop through you data collected from the source
WScript.Echo objrecordset.Fields.Item("CallCount")
WScript.Echo objrecordset.Fields.Item("CallDate")
WScript.Echo objrecordset.Fields.Item("Site")
objrecordset.MoveNext
Loop

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
I tried both ways. The For loop is what I need and works perfectly except for the datetime from string error. The reason I know it works is because I can put quotes around the date1 and it inserts into the database perfectly but doesn't gather the callcount correctly. The Do loop works as is but only inserts one entry into the database with today's date. Not sure what is wrong with that but I couldn't figure out how to make it do each date separately so I went back to the For loop.

Also, I tried the CDate in various ways and I still get the same error. The only way to make it run is by putting the quotes around it which does not get the count so I'm not sure what I'm doing wrong but it seems so simple and maybe that is why I can't find it.

If you have anymore ideas let me know. If the Do loop is the better way to go then let me know what I need to change to make it insert the count for each day separately. Is the loop not in the right place maybe?

JP
 
Upvote 0 Downvote
This is still not working. I changed the date() to getdate() in the script...actually in the sqlquery and it actually pulls data but not correctly. This leads me to believe that the convert is having a problem converting the datetime from a string and I am at a loss of how to make it work based on this. Any other ideas guys? I would be very greatful. By the way, the scripts were great except they do not accomplish what I need so you get stars for those.

Let me know if you have any ideas on what I can do. I tried using CDate() and it still wouldn't work.

JP
 
Upvote 0 Downvote
So is the problem pulling the data out or putting it into the destination DB? Do you have a query or method you've used that works in extracting the data correctly and one that inserts it into the destination DB correctly?

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

B
  • Locked
  • Question
MsgBox on alternate days
Replies
5
Views
793
strongm
strongm
Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
583
Alexvb6
Alexvb6
elly00
  • Locked
  • Question
sql result
Replies
1
Views
247
strongm
strongm
LandoMarossi
  • Locked
  • Question
VBScript To copy the specific columns only to specific cell in Excel 365 1
Replies
7
Views
694
SkipVought
S
Katto
  • Locked
  • Question
Get the last filled line in excel
Replies
7
Views
685
Katto
Katto

Part and Inventory Search

Sponsor

Back
Top