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

Does it give an actual error or just stop short of what your expecting? — Automate This
– Automate This, Commented Sep 24, 2013 at 22:18
No error, it literally just sits there for about three minutes (the query normally should take about 15 seconds), and the Status Bar returns to normal (not saying "Running Background query click here to cancel") with no data being returned. — Tsaukpaetra
– Tsaukpaetra, Commented Sep 24, 2013 at 22:48
Actually, I'm thinking it may have to do with how I'm adding the query. I'm basing the query off of a template that fetches a completely different table, and pasting the replacement code instead of it. I think Excel is getting confused, since the query is returning columns that are 100% different than expected... — Tsaukpaetra
– Tsaukpaetra, Commented Sep 24, 2013 at 22:50

Tsaukpaetra · Accepted Answer · 2013-09-25 19:31:17Z

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

answered Sep 25, 2013 at 19:31

Tsaukpaetra

5885 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query works in Microsoft Query, not in Microsoft Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related