SQL counting rows for multiple criteria

[T2T2]

The project I am working on involves Reporting Services.

The requirement is to track sales representative contact activity with clients.

For example:

Total Calls Made to clients.......xxx
Total Appointments.................xxx
Total Calls to Active Clients.....xxx
Total Calls resulting in Sale......xxx
Total Meetings.......................xxx
...
... etc
...

The problem I have is that the data is stored in one table, each row representing a contact.

The columns identify the

record type.........: call / appointment / message / other
Sub record type..: call in/ call out / message in/.....etc
result code.........: Very Interested / Moderate Interest / Sale / No interest
Client Status......: Active / Prospective

I believe I need to do separate counts based on the specific criteria for each report item.
I am new to Reporting services, I don't know if it has the ability to count detail lines at different criteria.
So I thought this would have to be done in the SQL query extracting the data.

Does anyone know of a sql approach to extract different criteria counts into on result?

Thanks.

[Chris256]

You need to use the SQL aggregate count function. This must be followed by a "Group by" statement. After the "Group by", you must list the fields that you would like to count. For example, if you want to see how many records there are for each record type for each client you would include the client_id field as well as the record type in the group by section. You will end up with a table with 3 columns: Client, Record Type, Record count.

To learn more about aggregate functions, specifically the count function. Look it up on www.w3schools.com , and excellent resource.

[T2T2]

Thanks Chris256.

Simple group by and aggregate functions (sum, count, max, etc) are not new to me, but I have always grouped by at the same level for all columns.
I found on the code project site some examples of sql with the CASE statement, again I have previously used this, the exception being I have never put the case in the group by statement.

I believe this will help me get my result, I have to nut it out to see before I can be sure?

I have also been playing with functions in the report layout area of Reporting Services, so between the sql query and the report designing I should get the categories for my report.
Fingers crossed.

[T2T2]

After familarising myself with Reporting Services 2000, I decided to resolve this issue of grouping at different levels after the query.

Basically, I set up a query to capture summed or counted information at the lowest required level.
Table regions are then used to format data in reports.
I make the detail line invisible and have group lines summing at required sort levels.

eg

Sales Rep Id (the report I am building is specific to a particular sales rep)
Client Status (active client, prospective client)
Contact Type (phone calls, appointments, emails)
ResultCode (eg sale made, client interested, client not interested,...etc)
Number of client contacts (sql count grouping on all the above columns).


This approach seems to be the most logical, given that Reporting Services has aggregate functions and sub totalling would be an essential process in any reporting tool.

Unfortunately, I get mixed results. I have proven that I can sum accurately at the contact type level.
In this case I filter contact type to be phone calls, and resultcode to be sales.
When I try to sum at the Client Status or Sales Rep Id, my report doesn't show the table altogether or produces inaccurate summing.?

I am assuming that the query grouping is the issue, but have not resolved this to be the reason, yet?

Anyone out there who understood my problem and has a suggestion of what to try next will be appreciated.

[erickwidya]

have u tried using ROLLUP? or CUBE? i've not done this myself but according to BOL
Summarizing Data Using ROLLUP
The ROLLUP operator is useful in generating reports that contain subtotals and totals. The ROLLUP operator generates a result set that is similar to the result sets generated by the CUBE operator. For more information, see Summarizing Data Using CUBE.

The differences between CUBE and ROLLUP are:

CUBE generates a result set showing aggregates for all combinations of values in the selected columns.

ROLLUP generates a result set showing aggregates for a hierarchy of values in the selected columns.
PS: check for ROLLUP Operator and also GROUPING in BOL

i'm not sure what the query looks like so plz post it if u have resolved this issue, maybe if gave the Table value and the result u want so i can play with

goodluck

EDIT: plz ignore my post, misread ...

[T2T2]

thanks ericwidya,

I had a play with the cube and rollup commands.
They are powerful giving you sub totals and totals.
One thing I didn't like, it was difficult to read the table result.
Maybe the examples I pulled off the net could have been more better at presentation.

The other issue I have is when I used the queries within Reporting Services, the subtotals and totals where seen as additional rows within the details rows.
I don't know if this is manageable.

I left these in the too hard basket because I couldn't see how I could pick the totals from the detail lines.

One thing I achieved with the Case statements was to build columns across my group by category.
This separates the summing process in reporting services nicely.
I think this is the approach to take.

[erickwidya]

One thing I achieved with the Case statements was to build columns across my group by category. can u post the query?
when i try using CASE it end up with only use 'LABEL' as the result, can't figure it out how to process other thing is the case if found

how about this one??? forgot what its called (not subquery i think)
SELECT COUNT(1) as 'ClientActive',
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.resultcode = 'sale') AS TotalSale,
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.resultcode = 'interest') AS TotalInterest,
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.resultcode = 'meeting') AS TotalMeeting,
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.recordtype = 'call') AS TotalCall,
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.subrecordtype= 'call in') AS TotalCallIn,
(SELECT COUNT(1) FROM t1 AS t2 WHERE t2.resultcode= 'interest') AS TotalInterest, ...
FROM t1
WHERE clientstatus='active'
go

[T2T2]

Here is the code i used to get my result.
The first case translates a coded field to a meaningful word.
The subsequent cases each generate a column of counts based on the criteria specified.

I hope it makes sense.


SELECT h.USERID, c.KEY1,
CASE srectype
WHEN 'A' THEN 'Appointments'
WHEN 'C' THEN 'Calls'
ELSE 'Nothing'
END AS ContactType,
COUNT(h.USERID) AS Total_Contacts,
COUNT(CASE WHEN resultcode BETWEEN 0 AND 4 THEN 1 ELSE NULL END) AS Contacts_Made,
COUNT(CASE WHEN resultcode = 4 THEN 1 ELSE NULL END) AS Sales
FROM CONTHIST h INNER JOIN
CONTACT1 c ON h.ACCOUNTNO = c.ACCOUNTNO
WHERE (h.USERID = @userident)
AND (h.ONDATE BETWEEN @startdate AND @enddate)
AND (h.SRECTYPE IN ('A', 'C'))
GROUP BY h.USERID, c.KEY1, h.SRECTYPE
ORDER BY h.USERID, c.KEY1, h.SRECTYPE

[erickwidya]

...
COUNT(CASE WHEN resultcode BETWEEN 0 AND 4 THEN 1 ELSE NULL END) AS Contacts_Made,
COUNT(CASE WHEN resultcode = 4 THEN 1 ELSE NULL END) AS Sales
...COUNT(1) only display 1 and it will display an error if the resultcode out of scope but maybe that just me..

PS: i did pm'ing u yesterday, have u followed the link?

[Krzemo]

Replace this to:


SUM(CASE WHEN resultcode BETWEEN 0 AND 4 THEN 1 ELSE 0 END) AS Contacts_Made,
SUM (CASE WHEN resultcode = 4 THEN 1 ELSE 0 END) AS Sales

and it will work OK ;)

Best regards,
Krzemo.