none
Execute SQL Pivot Statement with VBA in Excel RRS feed

  • Question

  • I am trying to execute this statement and cant seem to get it formatted right. 

    THis exact statement works in SSMS so I know it works, but getting the formatting right in VBA has stumped me.

    Once I get that figured out, I will insert the variables and other information to allow for changing in parameters.

    select GL_Acct, isnull(Monday,0) as Monday, isnull(Tuesday,0) as Tuesday, isnull(Wednesday,0) as Wednesday, isnull(Thursday,0) as Thursday, isnull(Friday,0) as Friday, isnull(Saturday,0) as Saturday, isnull(Sunday,0) as Sunday  from 
    (Select GL_acct , serv_day_of_week,convert(decimal(10,2),amt) as dollars from dbo.billing where serv_br = '3870' and [INV_DATE] between '2018-08-01' and '2018-08-31'and SERV_TYPE = 'V')  as ps
    pivot
    (avg(dollars) for serv_day_of_week in ([Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [Sunday]))  pvt


    Any help would be appreciated.

    Thanks 

    Chuck

    Friday, October 19, 2018 2:27 AM

All replies

  • I find formatting it helps.  What data type is serv_br.  It looks like integer but you have quotes around it.

    qs = ""
    qs = qs & "SELECT Gl_acct, " & vbCrLf
    qs = qs & "       Isnull(Monday, 0)    AS Monday, " & vbCrLf
    qs = qs & "       Isnull(Tuesday, 0)   AS Tuesday, " & vbCrLf
    qs = qs & "       Isnull(Wednesday, 0) AS Wednesday, " & vbCrLf
    qs = qs & "       Isnull(Thursday, 0)  AS Thursday, " & vbCrLf
    qs = qs & "       Isnull(Friday, 0)    AS Friday, " & vbCrLf
    qs = qs & "       Isnull(Saturday, 0)  AS Saturday, " & vbCrLf
    qs = qs & "       Isnull(Sunday, 0)    AS Sunday " & vbCrLf
    qs = qs & "FROM   (SELECT Gl_acct, " & vbCrLf
    qs = qs & "               Serv_day_of_week, " & vbCrLf
    qs = qs & "               CONVERT(DECIMAL(10, 2), Amt) AS dollars " & vbCrLf
    qs = qs & "        FROM   dbo.billing " & vbCrLf
    qs = qs & "        WHERE  Serv_br = '3870' " & vbCrLf
    qs = qs & "               AND [Inv_date] BETWEEN '2018-08-01' AND '2018-08-31' " & vbCrLf
    qs = qs & "               AND Serv_type = 'V') AS ps " & vbCrLf
    qs = qs & "       PIVOT (Avg(Dollars) " & vbCrLf
    qs = qs & "             FOR Serv_day_of_week IN ([Monday], " & vbCrLf
    qs = qs & "                                      [Tuesday], " & vbCrLf
    qs = qs & "                                      [Wednesday], " & vbCrLf
    qs = qs & "                                      [Thursday], " & vbCrLf
    qs = qs & "                                      [Friday], " & vbCrLf
    qs = qs & "                                      [Saturday], " & vbCrLf
    qs = qs & "                                      [Sunday])) pvt"
    

    Friday, October 19, 2018 12:01 PM
  • Serv_BR is character

    Friday, October 19, 2018 2:46 PM
  • I have 2 suggestions:

    1. Run SQL Profiler - See what VBA sql is shown in profiler.  Copy sql and run it in SSMS.  I've found numerous problems that way.

    2. What I do when I have this problem is I gradually build the query.  Start small and build it up.  That way it is easier to determine problem.

    Saturday, October 20, 2018 1:39 PM