Subquery help Please - 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

General SQL Server Forums

New to SQL Server Programming

Subquery help Please

Author

Topic

bulubuk1976
Starting Member

24 Posts

Posted - 2008-01-18 : 06:08:01

I am trying to get the % of resolved for every calltype based on an input dates. Here is my table:

Table: Win4
SessionDate Calltype Status
---------------------------------------
1/2/2008 Email Resolved
1/3/2008 Email Unresolved
1/4/2008 Connectivity Resolved
1/5/2008 Connectivity Unresolved
1/6/2008 Connectivity Resolved
1/7/2008 General Resolved
1/8/2008 General Resolved
1/9/2008 General Unresolved

Here's my desired result given the parameter dates 1/2/08 to 1/9/2008:

Calltype #Count #Resolved
---------------------------------------
Email 2 1
Connectivity 3 2
General 3 1

Here's my code:

SELECT Calltype, count(Calltype) as #Count
FROM dbo.WIN4
Where Sessiondate >= '1/2/2008' and Sessiondate <= '1/9/2008'
Group by Calltype

Then problem is that I do not know how to count all the RESOLVED with alias #Resolved.

I appreciate any help or who could write the code for me.

Thanks!
Dennis S.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2008-01-18 : 06:29:23

Try something like:

SELECT CallType
    ,COUNT(CallType) AS TypeCount
    ,COUNT(CASE Status WHEN 'Resolved' THEN 1 END) AS ResolvedCount
    -- or variations like
    -- ,SUM(CASE Status WHEN 'Resolved' THEN 1 ELSE 0 END) AS ResolvedCount
FROM Win4
WHERE SessionDate >= '20080102'
    AND SessionDate < '20080110'
GROUP BY CallType

bulubuk1976
Starting Member

24 Posts

Posted - 2008-01-18 : 06:34:57

Thank you! This forum is amazing. Again, Thank you so much! I will try this when I get back to work.

noorultsi
Starting Member

2 Posts

Posted - 2008-01-18 : 07:23:56

Try this,

for this output

Resutlt:

Calltype #Count #Resolved
---------------------------------------
Email 2 1
Connectivity 3 2
General 3 2

Query:

select a.calltype,count(a.calltype) as count,x as Resolvedcount from
win4 a,
(
select calltype,count(status) x from win4
where status='Resolved'
group by
calltype
) t
where
a.calltype=t.calltype
group by a.calltype,x

and let me know your view on this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources