Thread: String Manipulation in MS Query works, returns no results in Worksheet

I am trying to pull in some data from a query I wrote for data on SQL Server 2K8. The connections work but this particular query does not return any results from MS Query to the worksheet. The results show up fine in MS Query, but the cell only says the name of the connection after saying "Getting Data".. No errors are thrown.

A co-worker said that Excel doesn't like any string manipulation and therefore won't display the results, but that sounds strange.

Anything I can do? Its pretty critical.

DECLARE @day as int
declare @report as datetime
SET @report = Datepart(m, GETDATE())
SET @day = DATEPART(dd, getdate())


--Practices with no SuperUser Visits ever with Future and months live
SELECT c.name,

CAST(SUM(CASE
WHEN ev.TYPE LIKE 'EMR Super User Visit'
AND datepart(m, ev.activitydate)= @report and datepart(yyyy, ev.activitydate) = datepart(yyyy, getdate())
THEN 1
else 0
END) AS INT) AS 'EMR Super User Visits this month',


CAST(SUM(CASE
WHEN ev.TYPE LIKE 'EMR Super User Visit'
AND datepart(m, ev.activitydate)= DATEPART(m,getdate()) and
datepart(d,ev.activitydate) between
(@day-(@day-1)) and @day and datepart(yyyy, ev.activitydate) = datepart(yyyy, getdate()) THEN 1
else 0
END) AS INT) AS 'EMR Super User Visits this month to date'

FROM hqin_stg.dbo.hq_sf_holdevent ev
INNER JOIN (SELECT id,
ecw_account_id__c as apu_a ,
name as name_a
FROM dbo.hq_sf_account
WHERE flaglatest = 1
AND ecw_account_id__c IS NOT NULL
AND go_live_date__c <= Getdate()
AND organization_type__c = 'Small Practice'
AND stage_summary__c = 'Live' )acc
ON ev.accountid = acc.id
inner join (select name, id from dbo.HQ_SF_Holduser) c
on c.Id = ev.CreatedById

WHERE cancelled__c LIKE 'false'
group by c.name
having CAST(SUM(CASE
WHEN ev.TYPE LIKE 'EMR Super User Visit'
AND datepart(m, ev.activitydate)= @report and datepart(yyyy, ev.activitydate) = datepart(yyyy, getdate())
THEN 1
else 0
END) AS INT) <> 0

This query gets pasted into ms query. It returns the proper results, but wont put the data into the worksheet. I don't have dba rights on the sql server, im just a simple report jockey.
:-)

If anyone is interested, I was able to fix this by removing all variables and just coding them into the query. I tried it directly on the server as a VIEW and it worked, and then it worked in ms Query and properly pulled the data into the excel worksheet. Thanks again.

SELECT c.Name, CAST(SUM(CASE WHEN ev.TYPE LIKE 'EMR Super User Visit' AND datepart(m, ev.activitydate) = Datepart(m, GETDATE()) AND datepart(yyyy,
ev.activitydate) = datepart(yyyy, getdate()) THEN 1 ELSE 0 END) AS INT) AS 'EMR Super User Visits this month',
CAST(SUM(CASE WHEN ev.TYPE LIKE 'EMR Super User Visit' AND datepart(m, ev.activitydate) = DATEPART(m, getdate()) AND datepart(d,
ev.activitydate) BETWEEN (DATEPART(dd, getdate()) - (DATEPART(dd, getdate()) - 1)) AND DATEPART(dd, getdate()) AND datepart(yyyy, ev.activitydate)
= datepart(yyyy, getdate()) THEN 1 ELSE 0 END) AS INT) AS 'EMR Super User Visits this month to date'
FROM dbo.HQ_SF_HoldEvent AS ev INNER JOIN
(SELECT Id, eCW_Account_ID__c AS apu_a, Name AS name_a
FROM dbo.HQ_SF_Account
WHERE (flagLatest = 1) AND (eCW_Account_ID__c IS NOT NULL) AND (Go_Live_Date__c <= GETDATE()) AND
(Organization_Type__c = 'Small Practice') AND (Stage_Summary__c = 'Live')) AS acc ON ev.AccountId = acc.Id INNER JOIN
(SELECT Name, Id
FROM dbo.HQ_SF_HoldUser) AS c ON c.Id = ev.CreatedById
WHERE (ev.Cancelled__c LIKE 'false')
GROUP BY c.Name
HAVING (CAST(SUM(CASE WHEN ev.TYPE LIKE 'EMR Super User Visit' AND datepart(m, ev.activitydate) = Datepart(m, GETDATE()) AND datepart(yyyy,
ev.activitydate) = datepart(yyyy, getdate()) THEN 1 ELSE 0 END) AS INT) <> 0)