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.