Sub SQL_Updater_Testing()
Call Unhide_Extraction
Sheets("Extraction").Select
Range("A4").Select
ActiveCell.FormulaR1C1 = "=TODAY()-5"
'
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = True
Calculate
If IsEmpty(Range("Extraction!A4")) Then
MsgBox "You must enter a start date", , "Error"
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else
If (Range("Extraction!c3") - Range("Extraction!c4")) < -397 Then
MsgBox "You cannot search for more than 13 months of data", , "Error"
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else
If IsEmpty(Range("Extraction!A2")) And Range("Extraction!c3") < -366 And Range("Extraction!c2") = 0 Then
MsgBox "You can only search for 6 months across all customers without further criteria", , "Error"
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else
If IsEmpty(Range("Extraction!A2")) And (Range("Extraction!c3") - Range("Extraction!c4")) < -366 And Range("Extraction!c2") > 0 Then
MsgBox "You can only search for 6 months across all customers", , "Error"
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False
Else
Dim sqlcmdstring As String
Dim sqlcmdstring1 As String
Dim sqlcmdstring2 As String
Dim sqlcmdstring3 As String
Dim sqlcmdstring4 As String
Dim sqlcmdstring5 As String
Dim sqlcmdstring6 As String
Dim sqlcmdstring7 As String
Dim sqlcmdstring8 As String
Dim sqlcmdstring9 As String
Dim sqlcmdstring10 As String
Dim sqlcmdstring11 As String
Dim sqlcmdstring12 As String
Dim sqlcmdstring13 As String
Dim sqlcmdstring14 As String
Dim sqlcmdstring15 As String
Dim sqlcmdstring16 As String
Dim sqlcmdstring17 As String
Dim sqlcmdstring18 As String
Dim sqlcmdstring19 As String
Dim sqlcmdstring20 As String
Dim sqlcmdstring21 As String
Dim sqlcmdstring22 As String
Dim sqlcmdstring23 As String
Dim sqlcmdstring24 As String
Dim sqlcmdstring25 As String
Dim sqlcmdstring26 As String
Dim sqlcmdstring27 As String
Dim sqlcmdstring28 As String
Dim sqlcmdstring29 As String
Dim sqlcmdstring30 As String
sqlcmdstring1 = "declare @Days as int"
sqlcmdstring2 = "declare @EndDay as int"
sqlcmdstring3 = "set @Days = '%2'"
sqlcmdstring4 = "set @EndDay = '%9'"
sqlcmdstring5 = "declare @colselect as varchar"
sqlcmdstring6 = "set @colselect = '%A'"
sqlcmdstring7 = "declare @colselect2 as varchar"
sqlcmdstring8 = "set @colselect2 = '%C'"
sqlcmdstring9 = "SELECT inc_number,inc_affected_user_company,inc_brief_description,inc_description,inc_resolve,inc_affected_user_department,"
sqlcmdstring10 = "inc_resolve_product,inc_affected_user_site,inc_affected_user_forename,inc_affected_user_organisation,"
sqlcmdstring11 = "inc_affected_user_surname,inc_report_date,submit_date,inc_resolve_date,Submit_By,"
sqlcmdstring12 = "inc_resolve_product_category_2,inc_resolve_product_category_3,inc_resolve_category_1,inc_resolve_category_2,inc_resolve_time_onhold,"
sqlcmdstring13 = "inc_resolve_category_3,inc_current_assigned_group,inc_current_assignee,inc_resolve_time_remainder,"
sqlcmdstring14 = "inc_service_type,inc_impact,inc_urgency,inc_status,inc_resolve_cause,inc_report_method_inbound"
sqlcmdstring15 = "FROM SLAM.dbo.incident_information"
sqlcmdstring16 = "WHERE inc_report_date >=(dateadd(d,@Days,convert(VARCHAR(10),getdate(),10))) and inc_report_date <=(dateadd(d,@EndDay,convert(VARCHAR(10),getdate(),10)))"
If IsEmpty(Range("Extraction!A2")) Then
Else
sqlcmdstring17 = "And (inc_affected_user_company like '%' + '%1' + '%')"
End If
If IsEmpty(Range("Extraction!A7")) Then
Else
sqlcmdstring18 = "And (inc_brief_description like '%' + '%3' + '%')"
End If
If IsEmpty(Range("Extraction!B7")) Then
Else
sqlcmdstring19 = "And (inc_description like '%' + '%4' + '%')"
End If
If IsEmpty(Range("Extraction!A10")) Then
Else
sqlcmdstring20 = "And (inc_resolve like '%' + '%5' + '%')"
End If
If IsEmpty(Range("Extraction!A13")) Then
Else
sqlcmdstring21 = "And (inc_asset_tags like '%' + '%6' + '%')"
End If
If IsEmpty(Range("Extraction!B13")) Then
Else
sqlcmdstring22 = "And (inc_affected_user_site like '%' + '%7' + '%')"
End If
If IsEmpty(Range("Extraction!B10")) Then
Else
sqlcmdstring23 = "And (inc_resolve_product like '%' + '%8' + '%')"
End If
If IsEmpty(Range("Extraction!A16")) Then
Else
sqlcmdstring24 = "And (%A like '%' + '%B' + '%')"
End If
If IsEmpty(Range("Extraction!B16")) Then
Else
sqlcmdstring25 = "And (%C NOT like '%' + '%D' + '%')"
End If
'Where Inc Status Like New, Assigned, Pending or in progress
sqlcmdstring29 = "And (inc_status IN ('new', 'assigned', 'In Progress', 'Pending', 'Closed', 'Resolved', 'Cancelled'))"
'(inc_status like '%' + '%F' + '%' OR inc_status like '%' + '%G' + '%' OR inc_status like '%' + '%H' + '%' OR inc_status like '%' + '%I' + '%')"
'Where Inc Status Like New, Assigned, Pending, in progress, closed, cancelled or resolved
'sqlcmdstring28 = "And (inc_status like '%' + '%F' + '%' OR inc_status like '%' + '%G' + '%' OR inc_status like '%' + '%H' + '%' OR inc_status like '%' + '%I' + '%' OR inc_status like '%' + '%J' + '%' OR inc_status like '%' + '%K' + '%' OR inc_status like '%' + '%L' + '%')"
'Where Inc Status Like closed, cancelled or resolved
'sqlcmdstring28 = "And (inc_status like '%' + '%J' + '%' OR inc_status like '%' + '%K' + '%' OR inc_status like '%' + '%L' + '%')"
'sqlcmdstring29 = "Or (inc_status like '%' + '%G' + '%')"
'sqlcmdstring30 = "Or (inc_status like '%' + '%H' + '%')"
sqlcmdstring = sqlcmdstring1 & Chr(13) & "" & Chr(10) & _
sqlcmdstring2 & Chr(13) & "" & Chr(10) & _
sqlcmdstring3 & Chr(13) & "" & Chr(10) & _
sqlcmdstring4 & Chr(13) & "" & Chr(10) & _
sqlcmdstring5 & Chr(13) & "" & Chr(10) & _
sqlcmdstring6 & Chr(13) & "" & Chr(10) & _
sqlcmdstring7 & Chr(13) & "" & Chr(10) & _
sqlcmdstring8 & Chr(13) & "" & Chr(10) & _
sqlcmdstring9 & Chr(13) & "" & Chr(10) & _
sqlcmdstring10 & Chr(13) & "" & Chr(10) & _
sqlcmdstring11 & Chr(13) & "" & Chr(10) & _
sqlcmdstring12 & Chr(13) & "" & Chr(10) & _
sqlcmdstring13 & Chr(13) & "" & Chr(10) & _
sqlcmdstring14 & Chr(13) & "" & Chr(10) & _
sqlcmdstring15 & Chr(13) & "" & Chr(10) & _
sqlcmdstring16 & Chr(13) & "" & Chr(10) & _
sqlcmdstring17 & Chr(13) & "" & Chr(10) & _
sqlcmdstring18 & Chr(13) & "" & Chr(10) & _
sqlcmdstring19 & Chr(13) & "" & Chr(10) & _
sqlcmdstring20 & Chr(13) & "" & Chr(10) & _
sqlcmdstring21 & Chr(13) & "" & Chr(10) & _
sqlcmdstring22 & Chr(13) & "" & Chr(10) & _
sqlcmdstring23 & Chr(13) & "" & Chr(10) & _
sqlcmdstring24 & Chr(13) & "" & Chr(10) & _
sqlcmdstring25 & Chr(13) & "" & Chr(10) & sqlcmdstring26 & Chr(13) & "" & Chr(10) & sqlcmdstring27 & Chr(13) & "" & Chr(10) & sqlcmdstring28 & Chr(13) & "" & Chr(10) & sqlcmdstring29 '& Chr(13) & "" & Chr(10) & sqlcmdstring30
sqlcmdstring = Replace(sqlcmdstring, "%1", Range("Extraction!A2").Value)
sqlcmdstring = Replace(sqlcmdstring, "%2", Range("Extraction!c3").Value)
sqlcmdstring = Replace(sqlcmdstring, "%3", Range("Extraction!A35").Value)
sqlcmdstring = Replace(sqlcmdstring, "%4", Range("Extraction!A34").Value)
sqlcmdstring = Replace(sqlcmdstring, "%5", Range("Extraction!A32").Value)
sqlcmdstring = Replace(sqlcmdstring, "%6", Range("Extraction!A31").Value)
sqlcmdstring = Replace(sqlcmdstring, "%7", Range("Extraction!A30").Value)
sqlcmdstring = Replace(sqlcmdstring, "%8", Range("Extraction!A37").Value)
sqlcmdstring = Replace(sqlcmdstring, "%9", Range("Extraction!C4").Value)
sqlcmdstring = Replace(sqlcmdstring, "%A", Range("Extraction!A16").Value)
sqlcmdstring = Replace(sqlcmdstring, "%B", Range("Extraction!A17").Value)
sqlcmdstring = Replace(sqlcmdstring, "%C", Range("Extraction!B16").Value)
sqlcmdstring = Replace(sqlcmdstring, "%D", Range("Extraction!A36").Value)
sqlcmdstring = Replace(sqlcmdstring, "%E", Range("Extraction!A19").Value)
sqlcmdstring = Replace(sqlcmdstring, "%F", Range("Data_Validation!G7").Value)
sqlcmdstring = Replace(sqlcmdstring, "%G", Range("Data_Validation!G8").Value)
sqlcmdstring = Replace(sqlcmdstring, "%H", Range("Data_Validation!G9").Value)
sqlcmdstring = Replace(sqlcmdstring, "%I", Range("Data_Validation!G10").Value)
sqlcmdstring = Replace(sqlcmdstring, "%J", Range("Data_Validation!G11").Value)
sqlcmdstring = Replace(sqlcmdstring, "%K", Range("Data_Validation!G11").Value)
sqlcmdstring = Replace(sqlcmdstring, "%L", Range("Data_Validation!G13").Value)
sqlcmdstring = Replace(sqlcmdstring, "%M", Range("Extraction!A18").Value)
sqlcmdstring = Replace(sqlcmdstring, "%N", Range("Extraction!A22").Value)
sqlcmdstring = Replace(sqlcmdstring, "%O", Range("Extraction!A23").Value)
sqlcmdstring = Replace(sqlcmdstring, "%P", Range("Extraction!A24").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Q", Range("Extraction!A25").Value)
sqlcmdstring = Replace(sqlcmdstring, "%R", Range("Extraction!A26").Value)
sqlcmdstring = Replace(sqlcmdstring, "%S", Range("Extraction!A27").Value)
sqlcmdstring = Replace(sqlcmdstring, "%T", Range("Extraction!A28").Value)
sqlcmdstring = Replace(sqlcmdstring, "%U", Range("Extraction!A29").Value)
sqlcmdstring = Replace(sqlcmdstring, "%V", Range("Extraction!A20").Value)
sqlcmdstring = Replace(sqlcmdstring, "%W", Range("Extraction!A40").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AD", Range("Extraction!A38").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Y", Range("Extraction!A33").Value)
sqlcmdstring = Replace(sqlcmdstring, "%Z", Range("Extraction!A21").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AB", Range("Extraction!A41").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AC", Range("Extraction!A39").Value)
sqlcmdstring = Replace(sqlcmdstring, "%AE", Range("Extraction!A42").Value)
With ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection
.BackgroundQuery = False
.CommandText = sqlcmdstring
.Refresh
End With
End If
End If
End If
End If
End Sub
ActiveWorkbook.Connections("CCENTDBASP006 SLAM").OLEDBConnection.EnableRefresh = False