rick020200
New Member
- Joined
- Mar 25, 2014
- Messages
- 1
I've got a somewhat elaborate query that I first composed in SQL Server Management Studio. I want to hand it to someone in an Excel spreadsheet so that they don't need to worry about using SSMS. So I've created the connection in Excel, pasted the SQL (can't be viewed graphically, which is no surprise) and run the query in the Query editor. It returns the appropriate data after running about 1 minute (same as in SSMS).
However, if I try to return the data to Excel, the query refreshes in the background, but only the Query name is displayed in Excel. The data never populates.
SQL posted below. Any thoughts? Excel 2010 on Win7 32bit
However, if I try to return the data to Excel, the query refreshes in the background, but only the Query name is displayed in Excel. The data never populates.
SQL posted below. Any thoughts? Excel 2010 on Win7 32bit
use config_db
select ' ' as 'VQ_Name'
,s.name as 'Skill Name'
,COUNT(*) as 'Agents Skill 15'
,(select COUNT(*) from routing_db..PD_Customer where skill_id=s.name and Acct_Status_Code=1)
+ ( select COUNT(*) from routing_db..MPS_Customer where Skill_id=s.name) as 'Customer Count'
, ' ' as 'Elevator Def'
, case when s.name like 'M%' then 'Medical'
else 'Pharmaceutical'
end as 'Class of Trade'
, case SUBSTRING(s.name, 3,2)
when 'AM' then 'Ambulatory'
when 'LA' then 'Lab'
when 'NP' then 'Non-Prime'
when 'SP' then 'Specialty'
when 'AC' then 'Acute'
when 'IN' then 'Independent'
when 'NA' then 'National Accounts'
else 'ValueLink or Prime or Other'
end as 'COT based on skill name'
, null as 'Region'
,case
when s.name like '%[_]V%' then 'Virtual'
when s.name like '%[_]L%' then 'Little Rock'
when s.name like '%[_]R%' then 'Radcliff'
else 'Unknown' end as 'Site'
, null as 'Agent Level'
, '' as 'Supervisor'
, ' ' as 'Hours of Ops M-Th'
, ' ' as 'Hours of Ops F'
, null as 'Team Ext'
, ' ' as 'ECR Backup Skill'
, null as 'Last 30 Day Inb Call Answ'
, null as 'Last 30 Day Out Call'
into #GoSkills
from cfg_skill s
inner join cfg_skill_level sl
on s.dbid=sl.skill_dbid
where level_=15
and (name like 'M%' or name like 'P%')
and name not like 'MPS_KAP%'
group by s.name, case when s.name like 'M%' then 'Medical'
else 'Pharmaceutical' end
,case SUBSTRING(s.name, 3,2)
when 'AM' then 'Ambulatory'
when 'LA' then 'Lab'
when 'NP' then 'Non-Prime'
when 'SP' then 'Specialty'
when 'AC' then 'Acute'
when 'IN' then 'Independent'
when 'NA' then 'National Accounts'
else 'ValueLink or Prime or Other'
end
,case
when s.name like '%[_]V%' then 'Virtual'
when s.name like '%[_]L%' then 'Little Rock'
when s.name like '%[_]R%' then 'Radcliff'
else 'Unknown' end
alter table #GoSkills alter column [Supervisor] NVARCHAR(255)
alter table #GoSkills alter column [Elevator Def] NVARCHAR(255)
update #GoSkills set VQ_Name=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name=#GoSkills.[Skill Name]
and ct.name='HSCS_Skills'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name='VQ_name'
update #GoSkills set [Elevator Def]=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name=#GoSkills.[Skill Name]
and ct.name='HSCS_Skills'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name='Elevator Definition'
update #GoSkills set [Supervisor]=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name=#GoSkills.[Skill Name]
and ct.name='HSCS_Skills'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name='Supervisor'
update #GoSkills set [Hours of Ops M-Th]=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name=#GoSkills.[Skill Name]
and ct.name='HSCS_Skill_Time_Zone_EST'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name='1'
update #GoSkills set [Hours of Ops F]=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name=#GoSkills.[Skill Name]
and ct.name='HSCS_Skill_Time_Zone_EST'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name='5'
update #GoSkills set [Team Ext]=cfp2.prop_name
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name='Skill_Extension'
and ct.name='HSCS_RP_to_Skill_Mapping'
and ct.dbid=cfp.object_dbid
and cfp2.prop_value=#GoSkills.[Skill Name]
update #GoSkills set [ECR Backup Skill]=cfp2.prop_value
from config_db..cfg_flex_prop cfp
,config_db..cfg_transaction ct
,config_db..cfg_flex_prop cfp2
where
cfp.dbid=cfp2.parent_dbid
and cfp.prop_name='Skill'
and ct.name='ECR_SkillTranslations'
and ct.dbid=cfp.object_dbid
and cfp2.prop_name=#GoSkills.[Skill Name]
update #GoSkills set [Last 30 Day Inb Call Answ]=(select SUM(num_answered)
from GIM.dbo.ps_ixn_day
where VQ_Name=vq_resource_name
and cal_date >= GETDATE()-30
group by vq_resource_name)
update #GoSkills set [Last 30 Day Out Call]=(select SUM(n_extout_calls_attempted)
from GIM.dbo.ps_agent_60_m
where 'VQ_'+[Skill Name]=PRIMARY_SKILL
and cal_date >=GETDATE()-30
group by PRIMARY_SKILL)
select * from #GoSkills