How to calculate ranking based on two different attributes??

Question

Hi,

I have two tables tbl_Generate and tbl_Status. tbl_Generate has two columns one is TGenerate(having only boolean value 1 or 0) and and Emp_Id Table is something like this:-

T_ID Tgenerate Emp_ID
   1   0      101
   2   1      101
   3   1      101
   4   0      102
   5   1      102
   6   1      102
   7   1      102
   8   0      102

I have another table tbl_Status It has also having two imp columns Status (Status has four fixed value 'Delivered','Pending','Didn't Call','Refused') and Emp_Id.

status_Id       Status      Emp_ID
1            Delivered       101
2            Didn't Call     102
3            Pending         101
4            Refused         101
5            Delivered       101
6            Refused         102
7            Delivered       102
8            Pending         102
9            Didn't Call     101
10           Pending         102

I want to calculate ranking for every Emp_Id based on who have generated more no of TGenerate(For ex in tbl_Generate 101 has generated total 2 and 102 generated total 3 Tgenerate) and Emp_Id who have highest count of status as delieverd(For ex 101 has total 2 delivered count).

So how do I write sql query to get rank on the basis of those two attributes(TGenerate and Status). And How do I get total count whose status is not Delivered. P.S.:- I am using SQL Server 2012. 

Answer 1

Hello Rajneesh,

Please try this query

 

SELECT tbl_Generate.Emp_ID, Tgenerate, Count(Tgenerate) #Cnt,

ROW_NUMBER() OVER(Partition by tbl_Generate.Emp_ID ORDER BY tbl_Generate.Emp_ID)

AS [Row_Number] from tbl_Generate

Group by tbl_Generate.Emp_ID,Tgenerate

 

or you can use below query also

 

SELECT tbl_Generate.Emp_ID, Tgenerate, Count(Tgenerate) #Cnt,

ROW_NUMBER() OVER(Partition by tbl_Generate.Emp_ID ORDER BY tbl_Generate.Emp_ID)

AS [Row_Number] from tbl_Generate

where Tgenerate = 1

Group by tbl_Generate.Emp_ID,Tgenerate

 

It will give the below output

 

 

Answer 2

@Suraj Kumar Thank you very much for your help. That solved my problem. 

 Thanks again