SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Variable for a status field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 12/11/2012 :  10:28:55  Show Profile  Reply with Quote
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


Edited by - sz1 on 12/11/2012 10:29:59

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/12/2012 :  00:03:28  Show Profile  Reply with Quote
Include

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

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


--
Chandu
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
441 Posts

Posted - 12/12/2012 :  04:36:29  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 12/12/2012 :  04:58:09  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000