edit sql query using vba excel

[Afarag]

hello there,

please I need your support in editing SQL query via VBA,

i'm not familiar with SQL or VBA coding but after some serches into internet reached to have a code with SQL Connection and i set a SQL query into VBA.

what i want is I have 2 static criterias used in SQL "DATETIMEFROMPARTS(2016,5,11,16,30,0,0)" and "DATETIMEFROMPARTS(2016,5,11,19,15,0,0)"

is that available if can i set a specific 2 blank cells or popup form then fill them with inputs to be variable instead of this static criterias? and how can i perform that

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=COMP1\SERVER1;" & _
                  "Initial Catalog=EGCAHCSGALT352\SQLEXPRESS;" & _
                  "Integrated Security=SSPI;"
   
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("select * into test1 from [All VDN Calls]" & _
            "where" & _
                      "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>=DATETIMEFROMPARTS(2016,5,11,16,30,0,0)" & _
        "AND" & _
          "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<=DATETIMEFROMPARTS(2016,5,11,19,15,0,0)" & _
        "AND" & _
                      "VDN = 'SA_New_ar'" & _
        ";")
   
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("A1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
   
End Sub

gratefully,

 

[p45cal]

You should be able to do something like this (impossible for me to test):

Sub ConnectSqlServer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
' Create the connection string.
Set rngStartDate = Range("G27") '<<adjust (and it would bea good idea to include the sheet)
Set rngEndDate = Range("G28") '<<adjust (and it would bea good idea to include the sheet)
FromStr = Join(Array(Year(rngStartDate), Month(rngStartDate), Day(rngStartDate), Hour(rngStartDate), Minute(rngStartDate), 0, 0), ",")
ToStr = Join(Array(Year(rngEndDate), Month(rngEndDate), Day(rngEndDate), Hour(rngEndDate), Minute(rngEndDate), 0, 0), ",")

sConnString = "Provider=SQLOLEDB;Data Source=COMP1\SERVER1;" & _
              "Initial Catalog=EGCAHCSGALT352\SQLEXPRESS;" & _
              "Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("select * into test1 from [All VDN Calls]" & _
                      "where" & _
                      "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>=DATETIMEFROMPARTS(" & FromStr & ")" & _
                      "AND" & _
                      "DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<=DATETIMEFROMPARTS(" & ToStr & ")" & _
                      "AND" & _
                      "VDN = 'SA_New_ar'" & _
                      ";")
' Check we have data.
If Not rs.EOF Then
  ' Transfer result.
  Sheets(1).Range("A1").CopyFromRecordset rs
  ' Close the recordset
  rs.Close
Else
  MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

In this example the start and end date/times are in cells G27 and G28, adjust this to suit. These cells should contain real Excel dates, for example such as:
11/05/2016 16:30
(This happens to be UK format Day/month/Year but the format doesn't matter at all.)

 

[p45cal]

Instead of all this:

Set rngStartDate = Range("G27") '<<adjust (and it would be a good idea to include the sheet)
Set rngEndDate = Range("G28") '<<adjust (and it would bea good idea to include the sheet)
FromStr = Join(Array(Year(rngStartDate), Month(rngStartDate), Day(rngStartDate), Hour(rngStartDate), Minute(rngStartDate), 0, 0), ",")
ToStr = Join(Array(Year(rngEndDate), Month(rngEndDate), Day(rngEndDate), Hour(rngEndDate), Minute(rngEndDate), 0, 0), ",")

you could shorten it to:

FromStr = Format(Range("G27"), "yyyy,m,d,h,m,0,0")
ToStr = Format(Range("G28"), "yyyy,m,d,h,m,0,0")

although it would still be a good idea to include the sheet name in those range references.

 

[Afarag]

@p45cal

it works fine, thanks a lot for your great support.

 

[Afarag]

@p45cal

please I need your support for the same topic what about if I want to apply multiple SQL query, I ask how to add the below SQL Queries to the code to be run

select count(outage_with_detection) from customer_age where call_date >= Start date time
                                                            AND
                                                            call_date <= End date time;

drop table test1;

select * into test1 from [All VDN Calls]
            where
                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>= Start date time
                  AND
                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<= End date time
                  AND
                  VDN = 'SA_New_ar'
                    ;

select count(*)  from calls_witin_tool_outage
           
            cross join test1 where
                    DATETIMEFROMPARTS(calls_witin_tool_outage.year, calls_witin_tool_outage.[New Month], calls_witin_tool_outage.Day, calls_witin_tool_outage.Hour, calls_witin_tool_outage.Min, 0, 0)
                    >=
                    DATEADD(HOUR,-24,DATETIMEFROMPARTS(test1.year, test1.[New Month], test1.Day, test1.Hour, test1.Min, 0, 0))
                    AND
                    DATETIMEFROMPARTS(calls_witin_tool_outage.year, calls_witin_tool_outage.[New Month], calls_witin_tool_outage.Day, calls_witin_tool_outage.Hour, calls_witin_tool_outage.Min, 0, 0)
                    <=
                    DATEADD(MINUTE,-1,DATETIMEFROMPARTS(test1.year, test1.[New Month], test1.Day, test1.Hour, test1.Min, 0, 0))
                    AND
                    calls_witin_tool_outage.VDN = 'SA_New_ar'
                    AND
                    calls_witin_tool_outage.[Collected digits]= test1.[Collected digits]
                    ;

select count([Collected digits])*1.0/COUNT(DISTINCT [Collected digits])from [All VDN Calls] where
                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)>= Start date time
                  AND
                  DATETIMEFROMPARTS([All VDN Calls].year, [All VDN Calls].[New Month], [All VDN Calls].Day, [All VDN Calls].Hour, [All VDN Calls].Min, 0, 0)<= End date time
                  AND
                  VDN = 'SA_New_ar';

thanks a lot,

 

[p45cal]

I can't work out what you want; with your earlier question you made it clear what was wanted. Do the same again, but one query at a time, step by step.
I can't tell if you've quoted actual code or just a sql query from somewhere. Actual vba code (recorded) would be best.
Start with just one sql, the select count(outage… one.