Two queries on one table combination help

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 2000 Forums

Transact-SQL (2000)

Two queries on one table combination help

Author

Topic

spencet
Starting Member

2 Posts

Posted - 2003-05-01 : 11:53:34

Hi Guys,

Here is what I'm trying to do. I have a table that keeps track of the date, time, employee# and service_call# for calls processed in my company. What I have to display on a report is calls <= 30min and total calls for a particular day. My report is web based using .NET. I'm using two different queries and display the results in two different HTML tables. I'm trying to find a way to combine the two into one query so I can have one table. I was thinking a JOIN might have something to do with it, but I haven't had any luck. Is it possible to combine?

Thanks for the help

Here are my queries:

---Under 30min----
SELECT empnum, COUNT(*)
AS total
FROM response_time
WHERE (system_date = 'XX/XX/XXXX')
AND (response <= 30)
GROUP BY empnum
ORDER BY empnum
DESC

---Total for the day---
SELECT empnum, COUNT(*)
AS total2
FROM response_time
WHERE (system_date = 'XX/XX/XXXX')
GROUP BY empnum
ORDER BY empnum
DESC

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-01 : 12:01:52


select
    empnum,
    sum(case when response <= 30 then 1 else 0 end) as 'under30',
    count(*) as 'total'
from
    response_time
where
    system_date = 'XX/XX/XXXX'
group by
    empnum

Jay White
{0}

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-01 : 12:08:56

You should also use the ISO format for dates

'YYYYMMDD'

Benefits

Future compatability.
Easy conversion.
Globalization.
XML.
Replication.

http://www.xencraft.com/resources/ISO8601_IUC21_200204.ppt

Edited by - ValterBorges on 05/01/2003 12:13:49

Edited by - ValterBorges on 05/01/2003 12:16:47

spencet
Starting Member

2 Posts

Posted - 2003-05-01 : 12:29:06

That worked great! Thank you very much! I've been scratching my head about that for the past couple of months. And I learned something new.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources