Executing or Linking Complex SQL Server code from Excel and show results in Excel linked table

newbieX

New Member
Joined
Jul 24, 2013
Messages
35
I have the following query that I need to link to an Excel table in an Excel template that parses this data and then creates charts, etc. using VBA coding. Does anyone have any guidance on how to link this query to the Excel table since it is so complex? I am new to SQL and have never really used Excel to link to outside databases so I am floundering a bit. All the examples I see for creating SQL Server views or embedding the code in Excel do not address queries as complex as this is. I also could not find any examples of accessing the PCE_Final.sql file on my PC and executing it from Excel.

Any code and/or guidance is greatly appreciated.

SQL:
    --Selecting highest result_value for PCE in a specific quarter

    DECLARE @SelectQuarter AS VARCHAR(6);
    SET @SelectQuarter = (CAST(year(getdate()) AS char(4)) + 'Q' + CAST(CEILING(CAST(month(getdate())-4 AS decimal(4,2)) / 3) AS char(1)));
    print @SelectQuarter;


    WITH
      q AS
    (
    SELECT TOP 100 PERCENT GMP.GMP_Sample_Events.sys_loc_code, GMP.GMP_Sample_Events.sample_date, GMP.GMP_Sample_Events.sample_event, GMP.GMP_Sample_Results.chemical_name, GMP.GMP_Sample_Results.result_value, GMP.GMP_Sample_Results.detect, GMP.GMP_Sample_Results.reporting_limit, GMP.GMP_Sample_Results.dilution, GMP.GMP_Sample_Results.edd_no

    FROM    GMP.GMP_Sample_Events INNER JOIN
     GMP.GMP_Sample_Results ON GMP.GMP_Sample_Events.sys_sample_code = GMP.GMP_Sample_Results.sys_sample_code
    WHERE   ((GMP.GMP_Sample_Results.chemical_name = N'Tetrachloroethene') AND (GMP.GMP_Sample_Events.sample_event = @SelectQuarter))
    )
    ,
    SEQUENCED AS  
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY sys_loc_code ORDER BY chemical_name, sys_loc_code, result_value desc) AS sequence_id,*
        FROM q
    )

    SELECT    sys_loc_code, sample_date, sample_event, chemical_name, result_value, detect, reporting_limit, dilution, edd_no
    INTO #PCE1
    FROM  SEQUENCED
    WHERE sequence_id = 1
    ;

    --Selecting most recent sample_date for PCE in a specific quarter

    SELECT  TOP 100 PERCENT  GMP.GMP_Sample_Events.sys_loc_code, GMP.GMP_Sample_Events.sample_date, GMP.GMP_Sample_Events.sample_event, 
                GMP.GMP_Sample_Results.chemical_name, GMP.GMP_Sample_Results.result_value, GMP.GMP_Sample_Results.detect, 
                GMP.GMP_Sample_Results.reporting_limit, GMP.GMP_Sample_Results.dilution, GMP.GMP_Sample_Results.edd_no
    INTO #PCE2
    FROM        GMP.GMP_Sample_Events 
                INNER JOIN
                GMP.GMP_Sample_Results ON GMP.GMP_Sample_Events.sys_sample_code = GMP.GMP_Sample_Results.sys_sample_code
    WHERE       (GMP.GMP_Sample_Events.sample_date>= DATEADD(MONTH, -13, CAST(GETDATE() AS DATE)))  
                AND (GMP.GMP_Sample_Results.chemical_name = N'Tetrachloroethene')
                AND (sample_event <> @SelectQuarter OR sample_event IS NULL) 
    print @SelectQuarter
    ;

    --Concatenate the two files together

    SELECT x.* INTO #PCE3 
    FROM
        (
        SELECT * FROM #PCE1
        UNION 
        SELECT * FROM #PCE2
        ) x
    ;

    -- Find the most recent date for each sys_loc_code and return its row

    WITH
      q AS
    (
    SELECT    *
    FROM    #PCE3  
    )
    ,
    SEQUENCED AS  
    (
        SELECT ROW_NUMBER() OVER (PARTITION BY sys_loc_code ORDER BY  sys_loc_code, sample_date desc) AS sequence_id,*
        FROM  q
    )
    SELECT    sys_loc_code, sample_date, sample_event, chemical_name, result_value, detect, reporting_limit, dilution, edd_no
    INTO    #PCE_Final
    FROM    SEQUENCED
    WHERE    sequence_id = 1

    -- Save into final temp file
    SELECT        * 
    FROM        #PCE_Final
    --FROM #PCE1A
    ORDER BY    sys_loc_code
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Hi there, you can use Power Query to connect directly to your SQL server database and you can drop this code in as a custom SQL query

That will run the query and return the results to an excel table which you can build you chart, pivots tables from.

The VBA would be separate to the Power Query connection so you can refresh the query first then run the VBA code on top of that to create your charts, pivots tables

What version of Excel do you have?
 

newbieX

New Member
Joined
Jul 24, 2013
Messages
35
This was so easy compared to the direction I was going in. I had no idea that I could just "drop the code" into the SQL Statement box under Advanced Options in the Get Data option of the Power Query. I knew I was making this harder than it needed to be. Thank you a zillon times.
 

denzo36

Board Regular
Joined
Sep 22, 2019
Messages
149
Office Version
  1. 365
Platform
  1. Windows
Sorry I meant to get back to you sooner, glad you figured it out!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,425
Messages
5,642,051
Members
417,251
Latest member
Dordrecht

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top