replacing characters in sql code

[baha17]

Dear Masters,

I wrote a code which allows me extract data from server for the sales in my company. If I run it for one month period it takes very long time. Actually I realised it is nothing to the with the server, it slows down because it involves other vba command to format the cells. Since it gets over 100K rows it slows down. Is there anyway to replace those command in the sql statement cuch as below? Thanks for the helpw
Baha

    Sheets("CustomerData").Range("A2:A" & LastRow).Replace What:="V2", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

and

 For Each cel In Sheets("CustomerData").Range("A2:A" & LastRow)
If cel.Text <> "" Then
cel.Offset(0, 1) = cel.Offset(0, 3).Text & " " & cel.Offset(0, 2).Text
cel.Offset(0, 16) = _
Application.WorksheetFunction.SumIf(Sheets("CustomerData").Range("A2:A" & LastRow), cel.Value, Sheets("CustomerData").Range("N2:N" & LastRow))

End If
Next cel

Here is my whole code:

Dim DBFullName, TableName As String
Dim TargetRange As Range
Dim Conn As ADODB.Connection, intColIndex As Integer
Dim cel As Range
Dim TD As Long
Dim qdate As Double
Dim qdate2 As Double
Dim LastRow As Long
Application.ScreenUpdating = False
Application.DisplayAlerts = False
LastRow = Sheets("CustomerData").Range("A" & Sheets("CustomerData").Rows.Count).End(xlUp).Row
On Error Resume Next
qdate = Range("trddate").Value
qdate2 = Range("trddate2").Value
    
    Sheets("CustomerData").Range("AA:AZ").ClearContents
    Sheets("CustomerData").Range("A2:T" & LastRow + 2).ClearContents
    Sheets("CustomerData").Select
    Columns("A:R").AutoFilter                                     'sales.stat_date
    Range("A2").Select
    Selection.Activate
    Set TargetRange = Range("A2")
    Set Conn = New ADODB.Connection
     Conn.Open "driver={SQL Server};" & _
    "server=AACTM12;database=SalesDataBase;"
        Set RecSet = New Recordset
        
    RecSet.Open "SELECT  sales.card_id, '', custmast.fname, custmast.lname, sales.table_id, sales.game_date, " & _
    "sales.empl_id,  " & _
    "sales.dealer_id, '', sales.time_in, sales.time_out, " & _
    "sales.hours, sales.totalin, sales.estwl, sales.avgbet, sales.maxbet, '', " & _
    "setup_casino.pit_name " & _
    "FROM SalesDataBase.dbo.custmast custmast, SalesDataBase.dbo.sales sales, SalesDataBase.dbo.setup_casino setup_casino " & _
    "WHERE sales.card_id = custmast.card_id AND  sales.table_id = setup_casino.table_id AND " & _
    "sales.game_date>='" & qdate & "'And sales.game_date<='" & qdate2 & "'", Conn, , , adCmdText
    TargetRange.CopyFromRecordset RecSet
    
    RecSet.Close
    Set RecSet = Nothing
    Conn.Close
    Set Conn = Nothing
LastRow = Sheets("CustomerData").Range("A" & Sheets("CustomerData").Rows.Count).End(xlUp).Row
    Sheets("CustomerData").Range("A2:A" & LastRow).Replace What:="V2", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False
        
For Each cel In Sheets("CustomerData").Range("A2:A" & LastRow)
If cel.Text <> "" Then
cel.Offset(0, 1) = cel.Offset(0, 3).Text & " " & cel.Offset(0, 2).Text
cel.Offset(0, 16) = _
Application.WorksheetFunction.SumIf(Sheets("CustomerData").Range("A2:A" & LastRow), cel.Value, Sheets("CustomerData").Range("N2:N" & LastRow))

End If
Next cel
    Columns("L:L").NumberFormat = "0.00"
    Columns("J:K").NumberFormat = "h:mm;@"
    Columns("I:I").NumberFormat = "m/d/yyyy"
Columns("A:R").AutoFilter
    Sheets("CustomerData").Select
    Columns("A:B").Select
    Selection.Copy
    Range("AA1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("Q:Q").Select
    Selection.Copy
    Range("AC1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    LastRow = Sheets("CustomerData").Range("AA" & Sheets("CustomerData").Rows.Count).End(xlUp).Row
    ActiveSheet.Range("AA2:AC" & LastRow).RemoveDuplicates Columns:=Array(1, 2, 3), Header:= _
        xlYes
    Range("A1").Select
    
LastRow = Sheets("CustomerData").Range("A" & Sheets("TableData").Rows.Count).End(xlUp).Row
For Each cel In Sheets("CustomerData").Range("E2:E" & LastRow)
If cel.Text <> "" Then
cel.Offset(0, 4) = SaleType(cel.Text)
End If
Next cel
   
Sheets("Main").Select

End Sub

Function SaleType(text_string As String)
If IsNumeric(Mid(text_string, 3, 1)) Then
SaleType = Left(text_string, 2)
Else
SaleType = Left(text_string, 3)
End If
End Function

 

[baha17]

Hi
If I run above code for a day.No problem.But if I run for a month period I got an error once I remove "On error resume next":
[TABLE="width: 100%"]
<TBODY sizset="13" sizcache="0">[TR]
[TD="class: smalltxt"]"Transaction (Process ID 260) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction even using with(nolock) option in select query"
If I keep "On error resume next" in my code, I do not have any data sometimes. I try to add

RecSet.Open "SET DEADLOCK_PRIORITY NORMAL; SELECT....

to my code still did not work out.
Any thoughts?
Thanks
Baha
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[mole999]

This is difficult for me to do remotely

then nolock I use is built onto the SQL statement

dealing with the no lock first

its part of the FROM statement

FROM SalesDataBase.dbo.custmast custmast WITH (nolock), SalesDataBase.dbo.sales sales WITH (nolock), SalesDataBase.dbo.setup_casino setup_casino WITH (nolock)

you will have to ensure you are accurate when you modify the SQL

do that first and test it works as intended

then

maybe your dates might be at issue if you do more than a day, I use

Dim BeginDate As String

compared to your

Dim qdate As Double

basically thats because the cells are formatted as visible dates (this may not be necessary)

with that I use

BeginDate = Sheets("Reference Lookup").Range("A2")

qdate = Range("trddate").Value

again that maybe because its a numeric value ??

 

[baha17]

There you go mate, you are the king.It worked as a charm
Thank you very much for the help.
Have a good day
Baha

 

[mole999]

whats the difference in speed, i'm curious

 

[baha17]

actually I did not change the number format. I just added WITH (nolock) at the end of FROM. That fixed the problem.
Because the date was recorded as number in the server.i also found kind of odd.Anyway it works fine especially with the genious "replace" function
thanks
Baha

 

[baha17]

Kyle I forgot to mention,

substring(sales.card_id,0,patindex('%[0-9]%',sales.card_id))

also works fine.Thank you very much for your input.
Have a great day ahead.
Baha

 

[baha17]

Hi mole,
how can I add sum of sales.estwl per day instead of '', " in my sql? i could not make the syntax.
as far as i understand i need to add group by but where?
thanks for help again
Baha

 

[mole999]

you have "sales.dealer_id, '', sales.time_in, sales.time_out, " & _
"sales.hours, sales.totalin, sales.estwl, sales.avgbet, sales.maxbet, already in your code

so just putting it in ', ' would repeat the value

SQL GROUP BY Statement says you do it this way

WHERE column_name operator value
GROUP BY column_name;

so I would try

"FROM SalesDataBase.dbo.custmast custmast, SalesDataBase.dbo.sales sales, SalesDataBase.dbo.setup_casino setup_casino " & _
"WHERE sales.card_id = custmast.card_id AND sales.table_id = setup_casino.table_id AND " & _
"sales.game_date>='" & qdate & "'And sales.game_date<='" & qdate2 & "' GROUP BY sales.estwl", Conn, , , adCmdText

note I moved the double quote to the end

I have microsoft SQL Server manager installed to test on data, but I cannot test this, if necessary you may need to enlist the person who created the SQL you are using to test it against live data