SQL Query works in Microsoft Query, not in Microsoft Excel

Question

I have a complex SQL query that seems to be having trouble translating correctly to Microsoft Excel's Data Connection.

This code works when pasted in Microsoft Query, but as soon as I return it to Excel, Excel gives up and won't fill up the sheet with data. I think it stopped working properly when we added the Left Outer Join pieces to ensure we had the oldest SkitTypes in two groups of SkitTpes, but this thing is now so complicated I'm not entirely sure.

left outer join Table2 b2 WITH (NOLOCK) on (a.ActingNumber = b2.ActingNumber and b.ActingNumber = b2.ActingNumber and b2.SkitType in ('180','184','185') ) left outer join Table2 b3 WITH (NOLOCK) on (a.ActingNumber = b3.ActingNumber and b.ActingNumber = b3.ActingNumber and b3.SkitType in ( '980','984','985') )

and the full query: Declare @ClipID as Varchar(15) set @ClipID = 'Bus'

Declare @Mnth as int
Declare @BgDate as varchar(8)
Declare @EndDate as varchar(8)
Declare @PrvMnth as int

set @Mnth = (select max(mnth) from Table1)
set @Mnth = (left(replace(convert(varchar(10),dateadd(mm, -5, convert(varchar(10),@Mnth,120) + '01'),120),'-',''),6))
set @BgDate =  convert(varchar(8), @Mnth, 120) + '01'
set @EndDate = replace(convert(varchar(10),dateadd(dd, -1, dateadd(mm, 6, convert(smalldatetime, @BgDate))),120),'-','')
set @PrvMnth = (left(replace(convert(varchar(10),dateadd(mm, -1, convert(varchar(10),@Mnth,120) + '01'),120),'-',''),6))

DECLARE @Mnth2 INT
DECLARE @Delq_Mnth CHAR(8)
DECLARE @l_MonthEndDate datetime

SET @Mnth2 = left(replace(convert(varchar(10),dateadd(mm, 4, convert(varchar(10), @Mnth,120) + '01'),120),'-',''),6)
SET @Delq_Mnth = (CONVERT(VARCHAR(8),DATEADD(d,-1,DATEADD(m,1,CONVERT(SMALLDATETIME,CONVERT(VARCHAR(6),@Mnth2) + '01'))),112))
SET @l_MonthEndDate = DATEADD(d,-1,DATEADD(m,1,CONVERT(SMALLDATETIME,CONVERT(VARCHAR(6),@Mnth2) + '01')))


select a.ActingNumber, 
max(a.ClipID) as ClipID, 
max(a.OutfitID) as OutfitID, 
max(b.SkitTrainDate) as SkitTrainDate, 
max(b.sctrn$) as SkitTrainHours,
max(a.CrowdControl) as Last_Mnth_CrowdControl
from Table1 a WITH (NOLOCK)
join Table2 b WITH (NOLOCK) on a.ActingNumber = b.ActingNumber
left outer join Table2 b2 WITH (NOLOCK) 
on (a.ActingNumber = b2.ActingNumber and b.ActingNumber = b2.ActingNumber and b2.SkitType in ('180','184','185') )
left outer join Table2 b3 WITH (NOLOCK) 
on (a.ActingNumber = b3.ActingNumber and b.ActingNumber = b3.ActingNumber and b3.SkitType in ( '980','984','985') )

join (
select *
FROM 
(
SELECT distinct A.ActingNumber,
CASE WHEN a.SkitPerfDate <> 0 THEN B.Prnbal ELSE a.prnbal END as prnbal , '' as ClipID ,'' as OutfitID ,
CASE WHEN c.UsePrebump = 1 THEN  
        CASE WHEN a.CrowdAge < 1 THEN 0
        WHEN a.CrowdAge BETWEEN 1 AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-1,@Delq_Mnth),@Delq_Mnth)) THEN 15
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-1,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-2,@Delq_Mnth),@Delq_Mnth)) THEN 30
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-2,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-3,@Delq_Mnth),@Delq_Mnth)) THEN 60
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-3,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-4,@Delq_Mnth),@Delq_Mnth)) THEN 90
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-4,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-5,@Delq_Mnth),@Delq_Mnth)) THEN 120
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-5,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-6,@Delq_Mnth),@Delq_Mnth)) THEN 150
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-6,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-7,@Delq_Mnth),@Delq_Mnth)) THEN 180
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-7,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-8,@Delq_Mnth),@Delq_Mnth)) THEN 210
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-8,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-9,@Delq_Mnth),@Delq_Mnth)) THEN 240
        WHEN a.CrowdAge BETWEEN CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-9,@Delq_Mnth),@Delq_Mnth)+1) AND CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-10,@Delq_Mnth),@Delq_Mnth)) THEN 270
        WHEN a.CrowdAge > CONVERT(VARCHAR(4),DATEDIFF(dd,DATEADD(MONTH,-10,@Delq_Mnth),@Delq_Mnth)) THEN 300
        ELSE 0
        END 
ELSE 
        ISNULL(a.PostBucket,0) 
END as CrowdControl,
A.SkitPerfDate,
a.Mnth,
A.Closcd
From Table3 a WITH (NOLOCK)
LEFT OUTER JOIN Table4 b WITH (NOLOCK) ON b.ActingNumber = a.ActingNumber
LEFT JOIN Table5 c WITH (NOLOCK) on c.ClipID = ClipID AND c.OutfitID = OutfitID
--left outer join Table3 a2
--on (a.ActingNumber = a2.ActingNumber and a.SAFLDT > a2.SAFLDT )
--left outer join Table3 a3
--on (a.ActingNumber = a3.ActingNumber and a.CrowdAge > a3.CrowdAge )
Where 
a.SAFLDT = 0 and a.CrowdAge = 0

--a3.ActingNumber is null 
--and a2.ActingNumber is null
--Mnth >= @Mnth 
and a.calcSTATUS = 'Completed'

) a
) h on a.ActingNumber = h.ActingNumber
where a.ClipID = @ClipID
-- Transaction codes:
and b.SkitType in ('180','184','185')--, '980','984','985')
and b.SkitTrainDate >= @BgDate 
--and b2.SkitType not in ( '980','984','985')
and b2.SkitType is not null 
and (b2.SkitTrainDate  > b3.SkitTrainDate or b3.SkitTrainDate is null )

group by a.ActingNumber
--order by Last_Mnth_CrowdControl desc

Answer 1

Turns out that using an existing query and modifying it does not always work. I had to Add the query first, make the modifications, and then insert that query into the spreadsheet (Use Existing data connection).