CODEWITHSUNDEEP
asp.netsql-servercountsubqueryinner-join
SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

How to Get Count() for multiple rows of same Worker in sqlserver?

i am listing TaskList in GridView1 and to the right of the Listing i am Displaying the Workers List having different skills under oneserviceCategoryID. The Display of Worker List is Filtered after Clicking on GridView1 Row and all the Workers with multiple skills under one serviceCategoryID are Listed in GridView2.

Before Alloting the Task to any Worker i want to display the number of PendingTasks of that Worker in GridView2 Which is List of Workers with multiple skills under one selected serviceCategoryID from GridView1.

Current SQL Query is:

SELECT WorkerMst.workerID, WorkerMst.FName, WorkerMst.LName,WorkerMst.FName +' '+ WorkerMst.LName as Name,WorkerSkills.WorkerSkillsID,
     WorkerSkills.WorkerID AS WorkerSkills_WorkerID, WorkerSkills.SkillTypeID AS WorkerSkills_SkillTypeID, WorkerSkills.Score, skillTypeMst.skillTypeID, skillTypeMst.skillType, 
     serviceCategoryMaster.serviceCategoryMasterID, serviceCategoryMaster.CategoryName, WorkerMst.IsActive AS WorkerMst_IsActive,WorkerMst.IsIndividual,WorkerMst.readyfornewtask,
     (select count(WorkerID)from WorkerTask where isAproved=0 and workerid in (select distinct(workerid) from workertask where isaproved=0 group by workerid)) as PendingTsks

 FROM WorkerMst 
 INNER JOIN WorkerSkills ON WorkerMst.workerID = WorkerSkills.WorkerID 
 INNER JOIN skillTypeMst ON WorkerSkills.SkillTypeID = skillTypeMst.skillTypeID 
 INNER JOIN serviceCategoryMaster ON skillTypeMst.serviceCategoryMasterID = serviceCategoryMaster.serviceCategoryMasterID
where skillTypeMst.serviceCategoryMasterID=1 and 
WorkerMst.workerID in (select distinct(workerid) from workertask where isaproved=0)
order by workermst.workerid

enter image description here

WorkerTask Table has Following Fields:

WorkerID,TaskMstID,IsAccepted ,IsRejected,OnDate,isAproved

Any Suggestion? Help Appreciated! Thanks in Advance!

Upvotes: 0

Views: 144

Answers (2)

SHEKHAR SHETE
SHEKHAR SHETE

Reputation: 6066

Here is Modifed Query:

SELECT 
     WorkerMst.workerID, WorkerMst.FName, WorkerMst.LName,
     WorkerMst.FName +' '+ WorkerMst.LName as Name,WorkerSkills.WorkerSkillsID,
     WorkerSkills.WorkerID AS WorkerSkills_WorkerID, 
     WorkerSkills.SkillTypeID AS WorkerSkills_SkillTypeID, WorkerSkills.Score,
     skillTypeMst.skillTypeID, skillTypeMst.skillType, 
     serviceCategoryMaster.serviceCategoryMasterID, serviceCategoryMaster.CategoryName, 
      WorkerMst.IsActive AS WorkerMst_IsActive, WorkerMst.IsIndividual, 
      WorkerMst.readyfornewtask,(select count(workerid)from WorkerTask 
         where isAproved=0 and workerid = WorkerMst.workerid) as PendingTsks

 FROM WorkerMst 
 INNER JOIN WorkerSkills ON WorkerMst.workerID = WorkerSkills.WorkerID 
 INNER JOIN skillTypeMst ON WorkerSkills.SkillTypeID = skillTypeMst.skillTypeID 
 INNER JOIN serviceCategoryMaster 
    ON skillTypeMst.serviceCategoryMasterID =  serviceCategoryMaster.serviceCategoryMasterID
where skillTypeMst.serviceCategoryMasterID=1 and WorkerSkills.Score>=70
order by workermst.workerid

Thanks @VinayC for your suggestion!

Upvotes: 0

VinayC
VinayC

Reputation: 49225

You have not provided schema of your tables but I am assuming that the WorkerTask table would have one row for a task assignment to (any) worker. I am assuming that WorkerTaskId is primary key of this table then following query should suffice:

SELECT 
     WorkerMst.workerID, WorkerMst.FName, WorkerMst.LName,
     WorkerMst.FName +' '+ WorkerMst.LName as Name,WorkerSkills.WorkerSkillsID,
     WorkerSkills.WorkerID AS WorkerSkills_WorkerID, 
     WorkerSkills.SkillTypeID AS WorkerSkills_SkillTypeID, WorkerSkills.Score,
     skillTypeMst.skillTypeID, skillTypeMst.skillType, 
     serviceCategoryMaster.serviceCategoryMasterID, serviceCategoryMaster.CategoryName, 
      WorkerMst.IsActive AS WorkerMst_IsActive, WorkerMst.IsIndividual, 
      WorkerMst.readyfornewtask,
     (select count(t.workertaskid)from WorkerTask t
         where t.isAproved=0 and t.workerid = WorkerMst.workerid) as PendingTsks
 FROM WorkerMst 
 INNER JOIN WorkerSkills ON WorkerMst.workerID = WorkerSkills.WorkerID 
 INNER JOIN skillTypeMst ON WorkerSkills.SkillTypeID = skillTypeMst.skillTypeID 
 INNER JOIN serviceCategoryMaster 
    ON skillTypeMst.serviceCategoryMasterID =  serviceCategoryMaster.serviceCategoryMasterID
where skillTypeMst.serviceCategoryMasterID=1 
order by workermst.workerid

Upvotes: 1

Related Questions