My goal is to send 2 dates (11/1/02 & 11/15/02) to some type of function / procedure / etc. and have calculations done before sending the data to the browser.

Here are the fields in my current queries
EmpID - unique ID
Name - obviously the name
Date - Date of Call or Problem
Type - Call or Problem

A sample might look like this....
10, John Doe, 11/1/02, Call
10, John Doe, 11/2/02, Call
10, John Doe, 11/2/02, Problem
32, Tom Jones, 11/5/02, Call
32, Tom Jones, 11/13/02, Call
44, Elvis Presley, 11/3/02, Call
44, Elvis Presley, 11/8/02, Problem
44, Elvis Presley, 11/9/02, Problem

I would like to do a count of all Calls, Count of all Problems, and a percentage of problems per calls.

ie -
10, John Doe, 2, Call
10, John Doe, 1, Problem
32, Tom Jones, 2, Call
44, Elvis Presley, 1, Call
44, Elvis Presley, 2, Problem

From there I would like to produce 1 recordset with the EmpID, Name, Calls, Problems, Percentage.

ie -
10, John Doe, 2, 1, 50%
32, Tom Jones, 2, 0, 0%
44, Elvis Presley, 1, 2, 200%

Does that make sense? Is there something available for that?

Thank you for your help.

SQL Server 2000
ASP - Server Side Scripting

If SQL Server supports the CASE construct you can:

SELECT empid, name, calls, problems,
CASE WHEN calls = 0 THEN NULL ELSE problems/calls*100 END percentage
FROM
(
SELECT empid, name,
SUM( CASE WHEN type = 'call' THEN 1 ELSE 0 END ) calls,
SUM( CASE WHEN type = 'problem' THEN 1 ELSE 0 END ) problems
FROM table
WHERE ...
GROUP BY empid, name
);

The above works in Oracle, but as far as I know doesn't use anything proprietory to oracle.

I am getting this error:

Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ')'.

Here's the exact code I'm trying to use.
Thanks for any help.

You need to have a name to the outermost subquery....

select ..... from ( select ... ) A



"artsapimp" wrote in message
news:2429529.1043352738@dbforums.com...
  > I am getting this error:
  > Server: Msg 170, Level 15, State 1, Line 12
  > Line 12: Incorrect syntax near ')'.
  > Here's the exact code I'm trying to use.
  > USE Boomerang
  > GO
  > SELECT RepID, Name, Calls, Tickets,
  > CASE WHEN Calls = 0 THEN NULL ELSE Tickets/Calls*100 END
  > FROM
  > (
  > SELECT RepID, Name,
  > SUM(CASE Type WHEN 'CMAHRS' THEN intRecords ELSE 0 END) AS Calls,
  > SUM(CASE Type WHEN 'RRS' THEN intRecords ELSE 0 END) AS Tickets
  > FROM vw_CallsRRSRatio
  > WHERE [Date] BETWEEN '11/1/02' AND '11/5/02'
  > GROUP BY RepID, [Name]
  > )
  > Thanks for any help.
  > --
  > Posted via http://dbforums.com

That was the fix, thank you.

How would I now format that column to allow 2 decimal places? It keeps giving me either a 0 or NULL for what I wrote, and I'm sure that's because it is rounding it to the nearest whole number (right?).

Thanks for your help.