Variable for a status field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Variable for a status field

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-12-11 : 10:28:55

Hi

I have a status field I want to work out an average for, whereby the status = waiting then work out the percentage of waiting against all calls. Can I use a delare statement to work this out?

DECLARE @AvgWaiting INT

otherwise how do I embed the: select Count(STATUS_NAME)/Count(ID) as Pecent where STATUS_NAME = 'Waiting' it into this:

Current statement

select distinct [ID],c.DESC_SHORT,c.STATUS_NAME,
c.OCCURED_DT AS "Open Date",
DATEDIFF(d, c.OCCURED_DT, GETDATE()) AS "Open Days",
AVG(DATEDIFF(d, c.OCCURED_DT, GETDATE())) AS "Day Average",
c.ASSIGNED_Team_NAME,
c.ASSIGNED_REP_NAME, c.PRIORITY_NAME,
TYPE, [SYMPTOM], [CONTACT_FIRST_NAME] + ' ' + [CONTACT_LAST_NAME] AS "Contact Name",
INTI_CATEGORY
from DIM_CALLs c
where c.OPEN_FLAG = 1 and c.ABC_CURRENT =1
AND TYPE ='Incident'

Group By
[ID],
c.DESC_SHORT,
c.STATUS_NAME,
c.OCCURED_DT,
c.ASSIGNED_Team_NAME,
c.ASSIGNED_REP_NAME,
c.PRIORITY_NAME,
TYPE,
SYMPTOM,
CONTACT_FIRST_NAME,
CONTACT_LAST_NAME,
INTI_CATEGORY

Order by "Open Days" DESC, OCCURED

Thanks you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 00:03:28

Include

SELECT ............
,CASE WHEN STATUS_NAME = 'Waiting' THEN Count(STATUS_NAME)/Count(ID) END as Pecent

FROM DIM_Calls
GROUP BY .............

--
Chandu

sz1
Aged Yak Warrior

555 Posts

Posted - 2012-12-12 : 04:36:29

Thanks Bandi,

So I can embed CASE statements like this as part fo existing select statements? thats really handy that...and the CASE can go anywhere in the select clause as long as before the From table...

Thnaks
S

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 04:58:09

Welcome

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources