How to calculate ranking based on two different attributes??

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar8y ago
Accepted answer
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

+1
Rajneesh Chaubey8y ago
@Suraj Kumar Thank you very much for your help. That solved my problem.
Thanks again
0