Trouble implementing T-SQL GROUP BY clause

Question

Someone help me out before i fall into depression.

I'm creating a Simple licencing voting system. When a licence is received, it has to be Approved or Rejected by a committee. This committee consists of 5 members and the voting process is based on a majority vote(Meaning that every application received needs an approval/Rejection of 3 or more of the committee members. E.g If 3 or more members vote to approve the application is said to be "Approved", if 3 or more members vote to reject then the application is said to have been "Rejected" ).

The idea is that 3 majority votes are always needed to determine the status of the application. If all the members of the committee have not voted or the number of votes for each status(Approved or Rejected) is less than 3 then the Application is said to be "Pending" E.g if 2 members approve and 2 reject OR 2 approve and 1 reject OR 1 approve and 2 reject OR 1 approve and 1 reject OR Only 2 approve OR Only 2 reject

Here's the sructure of one of the tables am working on

CREATE TABLE [CommitteeApproval](
[CommitteeApprovalID] [int] IDENTITY(1,1) NOT NULL,
[LicenceApplicationID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[ActionDate] [date] NULL,
[CommitteeApprovalStatusID] [int] NOT NULL)

[CommitteeApproval] is the Primary key while [LicenceApplicationID] ,[CommitteeApprovalStatusID] and [UserID] are foreign keys to their respective tables.

[CommitteeApprovalStatus] Table(CommitteeApprovalStatus is the Primary Key)

   CommitteeApprovalStatusID      CommitteeApprovalStatusName
    1                             Approve
    2                             Reject

[CommitteeApproval] Table contains

LicenceApplicationID UserID ActionDate  CommitteeApprovalStatusID
4173                  37    2013-12-17         2
4173                  36    2013-12-17         1
4173                  6     2013-12-17         1
4173                  7     2013-12-17         1
4174                  37    2013-12-17         1
4174                  36    2013-12-17         2
4174                  7     2013-12-17         2
4174                  6     2013-12-17         2
4174                  38    2013-12-17         2
4176                  38    2013-12-17         2
4177                  7     2013-12-17         2
4179                  36    2013-12-17         1
4179                  38    2013-12-17         2

I want to return the number of CommitteeApprovalStatus for each licence application. For Example: For LicenceApplication 4174, 4 members rejected and 2 members approved, so the application is said to be "Rejected"

I used the below query to display the list of rejected applications to the user

SELECT LicenceApplicationID ,CommitteeApprovalStatusID ,COUNT(UserID) AS votes
  FROM CommitteeApproval
  WHERE CommitteeApprovalStatusID=2
  GROUP BY LicenceApplicationID, CommitteeApprovalStatusID
  HAVING COUNT(UserID) >= 3

I also successfully retrieved the list of Approved queries with a similar query but by replacing with

WHERE CommitteeApprovalStatusID=1

Now the PROBLEM arises when i try to retrieve the list of "Pending" applications.

1. I can't capture applications where 2 members approved and 2 members rejected, 1 member approved and 1 member rejected, 2 members approved and none or 1 member rejected, 3 members rejected and 1 or 2 members approved, 2 members rejected and 3 members approved.

2. I can only capture applications of only one type at a time e.g WHERE CommitteeApprovalStatusID=2 or 1 whereas i wanted to capture all applications that didn't qualify.

The query i wrote is:

 SELECT LicenceApplicationID, COUNT(UserID) AS votes 
 FROM CommitteeApproval   
 WHERE CommitteeApprovalStatusID=1 
 GROUP BY LicenceApplicationID   
 HAVING COUNT(UserID) < 3

This doesn't help much coz i still have to write another one with

WHERE CommitteeApprovalStatusID=2

And it still woun't be able to capture the results in problem 1. Is there a way to display all the "Pending" results with one query??

Answer 1

My attempt at readability:

WITH votes AS (
  SELECT
    LicenceApplicationID,
    CommitteeApprovalStatusName Vote
  FROM CommitteeApproval A
  INNER JOIN CommitteeApprovalStatus S ON 
    S.CommitteeApprovalStatusID = A.CommitteeApprovalStatusID
)
SELECT
  LicenceApplicationID,
  Approve,
  Reject,
  CASE
    WHEN  Approve >= 3 THEN 'Approved'
    WHEN  Reject  >= 3 THEN 'Rejected'
    ELSE 'Pending'
  END AS VoteStatus
FROM votes
PIVOT(COUNT(Vote) FOR Vote IN (Approve,Reject)) P

Answer 2

Try this.

(SELECT LicenceApplicationID, COUNT(UserID) As "Votes"
FROM CommitteeApproval
WHERE CommitteeApprovalStatusID = 2) B

SELECT LicenceApplicationID,
CASE 
WHEN A.Votes > 2 THEN "Approved"
WHEN B.Votes >2 THEN  "Rejected"
ELSE "Pending"

END AS "Status"

FROM 

(SELECT LicenceApplicationID, COUNT(UserID) AS "Votes"
FROM CommitteeApproval
WHERE CommitteeApprovalStatusID = 1
GROUP BY LicenceApplicationID ) A 

FULL OUTER JOIN

(SELECT LicenceApplicationID, COUNT(UserID) AS "Votes"
FROM CommitteeApproval
WHERE CommitteeApprovalStatusID = 2
GROUP BY LicenceApplicationID ) B

ON A.LicenceApplicationID = B.LicenceApplicationID

Answer 3

try this query

select 
  * 
from 
  (select 
    LicenceApplicationID,
    sum(case when 
          CommitteeApprovalStatusID=1 then 1 
        else 0 end) as pass,
    sum(case when 
          CommitteeApprovalStatusID=2 then 1 
        else 0 end) as fail
    from 
        tbl
    group by 
        LicenceApplicationID
  )tbl 
where 
  pass < 3 and 
  fail < 3

Fiddle

| LICENCEAPPLICATIONID | PASS | FAIL |
|----------------------|------|------|
|                 4176 |    0 |    1 |
|                 4177 |    0 |    1 |
|                 4179 |    1 |    1 |

Answer 4

Try with this:

 SELECT LicenceApplicationID, COUNT(UserID) AS votes, 
 CASE  WHEN CommitteeApprovalStatusID = 1 THEN 'Aprove' ELSE 'Rejected' END AS Status
 FROM CommitteeApproval   
 WHERE CommitteeApprovalStatusID in (1, 2) 
 GROUP BY LicenceApplicationID, CommitteeApprovalStatusID    
 HAVING COUNT(UserID) < 3