Edward Edward - 4 months ago 10
SQL Question

How to show PRINT results in EXCEL

A team member and I re-wrote a query we posted here due to issues; using various sources from provided answers from my previous question as well as checking other solutions and the query works now as intended. HOWEVER we need to embed this query and run in the background (we already have) into an EXCEL sheet but the issue is we need to somehow actually display the results in the cell. The PRINT function of the query actually pops up a window in EXCEL when we run (DUH, as intended) but what we would like to do is some how show the PRINTED results. Since the query is run automagically inside the spreadsheet the option to export from SSMS to a file then retrieve from there is not an option since OUR DBA is pretty funky on what we do with the DB we cannot run as a job..etc.... Any help again would be appreciated.

Declare @StartDate1 as DateTime
Declare @StartDate2 as DateTime
Declare @EndDate1 as DateTime
Declare @EndDate2 as DateTime
Declare @TodaysDate1 as DateTime
Declare @TodaysDate2 as DateTime
Declare @Yesterday as DateTime

Declare @TotalRecords1 int = 7
Declare @TotalRecords2 int = 7

set @TodaysDate1 = GETDATE()
set @TodaysDate2 = @TodaysDate1
set @Yesterday = DATEADD(day,-1,@TodaysDate1)

-------------------------------------------------------------------
-- For AM Shift Data Readings --
-------------------------------------------------------------------
set @StartDate1 = cast(convert(varchar(4), datepart(yyyy, @Yesterday)) + '-' +
convert(varchar(2), datepart(mm, @Yesterday)) + '-' +
convert(varchar(2), datepart(dd, @Yesterday)) + ' ' +
'17:00' as datetime)
set @EndDate1 = cast(convert(varchar(4), datepart(yyyy, @TodaysDate1)) + '-' +
convert(varchar(2), datepart(mm, getdate())) + '-' +
convert(varchar(2), datepart(dd, getdate())) + ' ' +
'04:59' as datetime)

-------------------------------------------------------------------
-- For PM Shift Data Readings --
-------------------------------------------------------------------
set @StartDate2 = cast(convert(varchar(4), datepart(yyyy, @Yesterday)) + '-' +
convert(varchar(2), datepart(mm, @Yesterday)) + '-' +
convert(varchar(2), datepart(dd, @Yesterday)) + ' ' +
'05:00' as datetime)
set @EndDate2 = cast(convert(varchar(4), datepart(yyyy, @Yesterday)) + '-' +
convert(varchar(2), datepart(mm, @Yesterday)) + '-' +
convert(varchar(2), datepart(dd, @Yesterday)) + ' ' +
'16:59' as datetime)

--------Material Scan Table--------

SELECT
@TotalRecords1=(SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate1 and @EndDate1),
@TotalRecords2=(SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate2 and @EndDate2)

WHILE @TotalRecords1 = 0 AND @TotalRecords2 = 0

BEGIN
set @TodaysDate1 = DATEADD(day,-1,@TodaysDate1)
set @TodaysDate2 = DATEADD(day,-1,@TodaysDate2)
set @Yesterday = DATEADD(day,-1,@TodaysDate1)

-------------------------------------------------------------------
-- For AM Shift Data Readings --
-------------------------------------------------------------------
set @StartDate1 = cast(convert(varchar(4), datepart(yyyy, @Yesterday)) + '-' +
convert(varchar(2), datepart(mm, @Yesterday)) + '-' +
convert(varchar(2), datepart(dd, @Yesterday)) + ' ' +
'17:00' as datetime)
set @EndDate1 = cast(convert(varchar(4), datepart(yyyy, @TodaysDate1)) + '-' +
convert(varchar(2), datepart(mm, getdate())) + '-' +
convert(varchar(2), datepart(dd, getdate())) + ' ' +
'04:59' as datetime)

-------------------------------------------------------------------
-- For PM Shift Data Readings --
-------------------------------------------------------------------
set @StartDate2 = cast(convert(varchar(4), datepart(yyyy, @TodaysDate2)) + '-' +
convert(varchar(2), datepart(mm, getdate())) + '-' +
convert(varchar(2), datepart(dd, getdate())) + ' ' +
'05:00' as datetime)
set @EndDate2 = cast(convert(varchar(4), datepart(yyyy, @TodaysDate2)) + '-' +
convert(varchar(2), datepart(mm, getdate())) + '-' +
convert(varchar(2), datepart(dd, getdate())) + ' ' +
'16:59' as datetime)

SELECT
@TotalRecords1=(SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate1 and @EndDate1),
@TotalRecords2=(SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate2 and @EndDate2)

IF (SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate1 and @EndDate1) > 0
BREAK
ELSE
IF (SELECT Count(*) FROM [Piston_TOL_PROD_DB].[dbo].[tblLOT_CTRL_Active_Component] WHERE Close_Time_Stamp between @StartDate2 and @EndDate2) > 0
BREAK
ELSE
CONTINUE
END

PRINT '1st Shift LOT Trace Scans ending'
PRINT @EndDate2
PRINT @TotalRecords2
PRINT ' '
PRINT '2nd Shift LOT Trace Scans ending'
PRINT @EndDate1
PRINT @TotalRecords1


THESE ARE THE ACTUAL RESULTS from run query in SSMS we would like to somehow get EXCEL to show:

1st Shift LOT Trace Scans ending
Jul 6 2016 4:59PM
64

2nd Shift LOT Trace Scans ending
Jul 7 2016 4:59AM
73

Answer

So after a long weekend and help from the team we were able to get the results in EXCEL we want and pretty simple really. All we needed to do instead of PRINT was use CONCAT. I thought since we did eventually arrive at a conclusion I would post here. Though the answer above is also a somewhat solution the one I am posting here works more to our liking to avoid have to export the data when we can import automatically via the query. Thanks to all who chimed in!See bellow:

SELECT CONCAT ( '1st Shift  ', @StartDate1, ' Total Scans ', @TotalRecords1, ' and ' +
'2nd Shift ', @StartDate2, ' Total Scans ', @TotalRecords2 ) as LotTrace

And the result was:

LotTrace
1st Shift  Jul 11 2016  5:00PM Total Scans 105                      2nd Shift Jul 11 2016  5:00AM Total Scans 77
Comments