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 Chriss Miller 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
Joined
Oct 2, 2006
Messages
110
Location
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
This query works:
Code: 
Select count(*) as CallCount, 
dateadd(d,-365,getdate()) as CallDate, 
'LAB' as Site  
From dbo.master_index where FileDateTime >=  convert(char(10),dateadd(d,-365,getdate()),110) AND FileDateTime <convert(char(10),dateadd(d,-364,getdate()),110)
This one gets the datetime to string error and does not work:
Code: 
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)"
Next

The first one is used with or without VBScript and the other is used in VBScript. This just means that the second one contains variables used in VBScript.
 
Upvote 0 Downvote
Perhaps this ?
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)"

You have to know how to write literal date values in your SQL dialect (a common way: 'yyyy-mm-dd')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I did try the single quotes as well but I guess I should have also mentioned from the last post that the StartDate is equal to Date() in vbscript. That is why I was converting it using the 110. I'll try this again. It does work with the single quotes but does not gather the CallCount that I'm looking for. Not sure why but it doesn't give me any errors either. I'll post the output after I add the single quotes and let you see what I mean. I'll also post the output from the one that works so you can see what I mean. Give me just a few minutes and I'll get it posted.

JP
 
Upvote 0 Downvote
Here is the output from the code that works by manually changing the 365 days piece as in the 3rd from the bottom post above.

OUTPUT Should look like this:
Code: 
LAB	2006-12-23 04:15:09.937	1469	NULL
LAB	2006-12-24 04:15:10.580	1488	NULL
LAB	2006-12-25 02:15:04.277	1465	NULL
LAB	2006-12-26 02:15:05.260	875	NULL
LAB	2006-12-27 02:15:11.837	1726	NULL
LAB	2006-12-28 02:15:12.983	563	NULL
LAB	2006-12-29 03:15:03.760	1565	NULL
LAB	2006-12-30 04:15:01.950	1548	NULL
LAB	2006-12-31 04:15:08.890	2314	NULL
LAB	2007-01-01 04:15:10.873	1998	NULL
LAB	2007-01-02 02:15:04.547	1480	NULL
LAB	2007-01-03 02:15:05.793	935	NULL
LAB	2007-01-04 02:15:12.440	1502	NULL
LAB	2007-01-05 02:15:13.547	510	NULL
LAB	2007-01-06 03:15:04.023	1618	NULL
LAB	2007-01-07 04:15:02.300	1268	NULL
LAB	2007-01-08 04:15:09.470	2145	NULL
LAB	2007-01-09 04:15:11.470	1718	NULL

OUTPUT from using the script with the single quotes as you mentioned in your post:

Code: 
LAB	2006-12-23 00:00:00.000	0	NULL
LAB	2006-12-24 00:00:00.000	0	NULL
LAB	2006-12-25 00:00:00.000	0	NULL
LAB	2006-12-26 00:00:00.000	0	NULL
LAB	2006-12-27 00:00:00.000	0	NULL
LAB	2006-12-28 00:00:00.000	0	NULL
LAB	2006-12-29 00:00:00.000	0	NULL
LAB	2006-12-30 00:00:00.000	0	NULL
LAB	2006-12-31 00:00:00.000	0	NULL
LAB	2007-01-01 00:00:00.000	0	NULL
LAB	2007-01-02 00:00:00.000	0	NULL
LAB	2007-01-03 00:00:00.000	0	NULL
LAB	2007-01-04 00:00:00.000	0	NULL
LAB	2007-01-05 00:00:00.000	0	NULL
LAB	2007-01-06 00:00:00.000	0	NULL
LAB	2007-01-07 00:00:00.000	0	NULL
LAB	2007-01-08 00:00:00.000	0	NULL
LAB	2007-01-09 00:00:00.000	0	NULL

JP
 
Upvote 0 Downvote
I asked you about literal date value. Any working example ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
I think the problem is the difference between getdate() and date() but below is the example you wanted.

This is directly from the database where the data is coming from. This comes from the dbo.Master_Index table. This is the literal date from the source database table.
Code: 
FileDateTime
2004-03-22 12:49:59.000
2004-03-22 12:59:48.000
2004-03-22 16:01:03.000
2004-03-22 16:01:51.000
2004-06-17 11:23:17.000
2004-06-17 11:25:15.000
2004-06-17 11:26:18.000
2005-03-17 14:39:24.000
2005-03-17 14:41:28.000
2005-03-17 14:43:14.000

Is this what you were asking for? The Date Literal from the Source Database, correct?

JP
 
Upvote 0 Downvote
I asked you about literal date value in SQL code. Any working example ?
 
Upvote 0 Downvote
I used the literal date rather than the Date() for my script above and it works perfectly except the count just keeps showing up as 0 and I do not know why. Looks great except for that. I guess I don't have a working example but this is as close as I've come to it. I do thank you for the ideas. Now if I can just figure out why the count is not gathering correctly I will be in great shape. Any more ideas on that part of it is greatly appreciated. The code below is what I tried with a literal date which works except the count is not working now. Could it be because the time is also on the date format in the source database? Not sure how to get around that? Can I use the convert to add this piece or is that a dumb idea?

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, 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

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "LUBLAB"
strServer2 = "SFLAB"
StartDate = "2007-01-09"

For i = -1024 To -1
    date1 = DateAdd("d", i, StartDate)

    sqlQuery = "Select count(*) as CallCount," & _
        "'" & DateAdd("d", 1, 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)"

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=Recdb;" '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

Here is the output that I get or a piece of it.

Code: 
SITE    RecDate                 NumOfRecs
LAB	2007-01-04 00:00:00.000	0
LAB	2007-01-05 00:00:00.000	0
LAB	2007-01-06 00:00:00.000	0
LAB	2007-01-07 00:00:00.000	0
LAB	2007-01-08 00:00:00.000	0
LAB	2007-01-09 00:00:00.000	0
...

The NumOfRecs is where the count for each of these days should be but it just puts a 0 no matter what I've tried.

JP
 
Upvote 0 Downvote
Got it. Here is what worked. Thanks.

Code: 
Option Explicit
'On Error Resume Next

Dim objFSO, 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

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = "LUBLAB"
strServer2 = "SFLAB"
StartDate = CDate(#2007-01-09#)

'	sqlQuery = "Select count(*) as CallCount," & _
'		"convert(char(10),FileDateTime  ,110)as CallDate," & _
'		"'LAB' as Site  " & _
'		"From dbo.master_index where FileDateTime Between " & _
'		"convert(char(10),getdate()-405,110) AND convert(char(10),getdate(),110)" & _
'		"Group by convert(char(10),FileDateTime  ,110)"

For i = -403 To -1
    date1 = DateAdd("d", i, StartDate)

    sqlQuery = "Select count(*) as CallCount," & _
        "'" & DateAdd("d", -1, 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),'" & 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=RecDB;" '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

'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

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
Status
Not open for further replies.

Similar threads

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

Part and Inventory Search

Sponsor

Back
Top