MS SQL query to list count by status

I want to achieved the result below. I want to list all records having a status of “For Approval” together with this condition:

For example for Username ‘Leo’

if ProcedureLevel="Propose" then count all userId of 'Leo' from ProposedCheckBy field 
if ProcedureLevel="Endorse" then count all userId of 'Leo' from EndorsedCheckBy field 
if ProcedureLevel="Approve" then count all userId 'Leo' from ApprovedCheckBy field 

Then sum all the status having “For Approval” which is the checker is ‘Leo’ with the condition above

I want to achieved the result like this
-----------------------------------------
Username   | No of For Approval  |
Leo        |   3                 |
Taurus     |   2                 | 
Capricorn  |   1                 | 
-----------------------------------------
Tables

Users
Id, userName

Limits

Id, [Description] ,[status],[procedurelevel] checkbyby1,checkbyby2, checkby3

Users

Id   [UserName]
1    Leo
2    Taurus
3    Capricorn

Limits

Id, [Description] ,[status]       ,[procedurelevel] ProposedCheckedBy1 ,EndorsedCheckedBy2 , ApprovedCheckBy3

1     Limits1       For Approval    Propose         1                      null                null
2     Limits2       For Approval    Propose         1                      null                null
3     Limits3       For Approval    Endorse         1                      2                   null  
4     Limits4       For Approval    Approve         1                      2                   1  
5     Limits5       For Approval    Approve         2                      3                   2
5     Limits5       For Approval    Approve         1                      2                   3

Answer

I think this is what you are after; if its not i have created tables and data inserts which will help whoever gives you the final solution. I think coalesce is the thing you are looking for you; using the status 1,2,3 columns you can work backwards to find the current user in one pass.

It currently returns

NoMatches   procedurelevel  userName
1           Approve     Leo
1           Endorse     Taurus
1           Propose         Capricorn
2           Propose     Leo
1           Propose         Taurus



Create table tblUsers
(
Id int, userName varchar(100)
)

Create table tblLimits

(
Id int, [Description] varchar(20),[status] varchar(20),[procedurelevel] varchar(20), checkby1 int,checkby2 int, checkby3 int
)

insert into tblUsers select 1, 'Leo'
insert into tblUsers select 2, 'Taurus'
insert into tblUsers select 3, 'Capricorn'



insert into tblLimits select 1, 'Limits1', 'For Approval', 'Propose',1, null,null
insert into tblLimits select 2, 'Limits2', 'For Approval', 'Propose',1, null, null
insert into tblLimits select 3, 'Limits3', 'For Approval', 'Endorse',1, 2,null  
insert into tblLimits select 4, 'Limits4', 'For Approval', 'Approve',1, 2,1  
insert into tblLimits select 5, 'Limits5', 'For Approval', 'Propose',2, 3,2
insert into tblLimits select 5, 'Limits5', 'For Approval', 'Propose',1, 2,3


select count(ProcedureLevel) as NoMatches, procedurelevel, bb.userName
From tbllimits aa
inner join tblUsers bb
on coalesce(checkby3, checkby2, checkby1) = bb.id
group by bb.userName, aa.procedurelevel

drop table tblusers
drop table tbllimits