SQL connections?

[deletedalien]

Can someone help with a question regarding an SQL connection to excel?

im just trying to set 2 parameters (dates) in Excel cells and have the data in the query update accordingly
Thanx

 

[VBA Geek]

Hello. you need to be a bit more specific. do you want to run an update query? or select data based upon these dates? what does the table look like?

 

[Norie]

How are you connecting to the SQL database?

 

[deletedalien]

hi there, sorry i wasn't being too specific, at first i just wanted to make sure i could get help on SQL...

so.... i have this code that runs on SQL server 2008 and runs Just fine....

use HistoricalData




select 
CONVERT(DATE,[HistoricalData].[dbo].[GetRealDate] ('PSTMX',StartDate)) as StartDatePST
--,a.Direction
,(CASE WHEN a.Direction='S' AND a.OutboundProcessId ='' THEN 'OutBound_Manual' 
	WHEN a.Direction='S' THEN 'OutBound_Engine'
	WHEN a.Direction='E' THEN 'InBound'
ELSE NULL END) as [Type]
,a.Campaign
,a.LastAgent
--,a.AttentionTime
,(CASE WHEN a.AttentionTime<60 THEN '[<60]'
		WHEN a.AttentionTime between 60 and 120 THEN '[60-120]'
		WHEN a.AttentionTime between 121 and 240 THEN '[121-240]'
		WHEN a.AttentionTime between 241 and 300 THEN '[241-300]'
		WHEN a.AttentionTime>300 THEN '[>300]'
ELSE NULL END) as AttTimeRange
,COUNT(*) as Calls




from HistoricalData.dbo.InteractionDetail a
where 


StartDate >= [HistoricalData].[dbo].[GetInvRealDate] ('PSTMX','2018-01-01')  --   <-- Start date****************
AND StartDate < [HistoricalData].[dbo].[GetInvRealDate] ('PSTMX','2018-01-31') -- <-- Today's date*******************
and a.IsTaked=1
group by 
CONVERT(DATE,[HistoricalData].[dbo].[GetRealDate] ('PSTMX',StartDate))
--,a.Direction
,(CASE WHEN a.Direction='S' AND a.OutboundProcessId ='' THEN 'OutBound_Manual' 
	WHEN a.Direction='S' THEN 'OutBound_Engine'
	WHEN a.Direction='E' THEN 'InBound' 
ELSE NULL END)
,a.Campaign
,a.LastAgent
--,a.AttentionTime
,(CASE WHEN a.AttentionTime<60 THEN '[<60]'
		WHEN a.AttentionTime between 60 and 120 THEN '[60-120]'
		WHEN a.AttentionTime between 121 and 240 THEN '[121-240]'
		WHEN a.AttentionTime between 241 and 300 THEN '[241-300]'
		WHEN a.AttentionTime>300 THEN '[>300]'
ELSE NULL END)
order by 1

now when i add a connection in MS excel 2013 using (from other resources > from Microsoft query...)

it can and will run BUT the parameters button is disabled, so i can't set the lines (in orange) as parameters linked to cells in the workbook.

i hope i was able to explain my self because i have little to no experience in SQL...

Thanx in advance.

 

[deletedalien]

How are you connecting to the SQL database?

Data> from other sources > From microsoft query ...

i don't know if that is what you're asking..

Sorry

 

[Norie]

When you try and execute that query are you asked for any parameters/values?

 

[deletedalien]

When you try and execute that query are you asked for any parameters/values?

not while running it on the SQL server 2008,

Edit: Actually not in excel either thats the problem it will only run with those given dates

 

[Norie]

As far as I can see the SQL doesn't take any parameters so I think it would need to be rewritten if you want to supply your own values for the criteria.

 

[deletedalien]

right.....

but how would i re write it?