CODEWITHSUNDEEP
sqlsql-serveraggregate
user335160
user335160

Reputation: 1362

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

Upvotes: 1

Views: 183

Answers (2)

user335160
user335160

Reputation: 1362

Here is my answer, the problem is Leo has 2 No of Approval instead of 3. Please correct if what I've been wrong

WITH UserAsignedLimits AS (SELECT ProposedCheckedBy AS Id
    , COUNT(ProposedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Propose'
GROUP BY ProposedCheckedBy
UNION ALL
SELECT EndorsedCheckedBy AS Id
    , COUNT(EndorsedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Endorse'
GROUP BY EndorsedCheckedBy
UNION ALL
SELECT ApprovedCheckedBy AS Id
    , COUNT(ApprovedCheckedBy) AS NoCheckBy 
FROM Limits
WHERE [Status]='ForApproval'
AND Procedurelevel='Approve'
GROUP BY ApprovedCheckedBy)

SELECT ID, COUNT(NoCheckBy) as NoofForApproval 
FROM UserAsignedLimits
GROUP BY Id

Upvotes: 0

u07ch
u07ch

Reputation: 13692

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

Upvotes: 2

Related Questions