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.
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
