Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Row count based on condition

Status
Not open for further replies.

theoryofben

IS-IT--Management
Aug 25, 2001
149
US
I'm sure there is a simple solution to my problem...but I'm just starting to use SQL Reporting...so I need a little assistance. Here goes:

My query uses an outer join on a column, let's call it "confirm". I have the report grouped by location and employee and am displaying the total number or records for an employee using the CountRows function.

The problem is that I need to display the total number of rows that have a NULL for the "confirm" column for each employee. I know that reporting replaces NULLS with a blank space, but I still don't know how to calculate this in an expression so I can display it for each employee.

Any assistance or push in the right direction would be greatly appreciated.

Thanks a ton.

________________________________________________
[sub]"I have not failed. I've just found 10,000 ways that won't work."-Thomas Edison[/sub]

 
What about taking the other tack and altering the confirm column so that it becomes a meaningful value in the result set that you can then report on ?

e.g. (assuming Confirm datatype is a string) something like

SELECT
COALESCE(t2.Confirm,'Y') AS Confirm
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.Confirm = t1.Confirm

This way you don't have to do anything tricky at the RS client end and can use the same code to ensure that other reports or client-side consumers all return exactly the same result

:)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top