combine query together

[yan19454]

I have two queries I want to combine together to get the
JobCCNO, TotolEmpCount, TotalCompleteCount, IncompletedCount

For IncompletedCount should be TotolEmpCount-TotalCompleteCount

The data should be extract from tables OcchEmp,The data should be extract from table OcchEmp,tblCurrentWinTrainingLog

tblCurrentWinTrainingLog which contain the employees training info date , training, emplID
OcchEmp which contain the JobCCNO and EmplNO

I have those two query to get the total count , completed count. I do not know how to make that as one query. Thx.


select count(Emplno) as TotalEmpCount, jobccno from occhemp group by jobccno

select Count(distinct c.Emplno) as CompleteCount, c.QuizName,o.jobccno from tblCurrentWinTrainingLog c join OcchEmp o on c.Emplno=o.EMPLNO group by c.QuizName,o.JobCCNO
go

 

[yan19454]

here is my what I get right now.


select T.TotalEmpCount ,T.jobccno , d.CompleteCount, T.TotalEmpCount-d.CompleteCount as Incompleted from (
select count(Emplno) as TotalEmpCount, jobccno from occhemp o group by jobccno ) as T
join
(select Count(distinct c.Emplno) as CompleteCount, c.QuizName,o.jobccno from tblCurrentWinTrainingLog c join OcchEmp o on c.Emplno=o.EMPLNO group by c.QuizName,o.JobCCNO) as d
on T.jobccno=d.jobccno
Do you know any better way to do that ? Thx.

 

[rac2]

Do you have a table which lists all of the possible training classes? For example, tblClasses with column for QuizName.

Does the OcchEmp table have one row for each employee, so that Emplno is unique?

Can the tblCurrentWinTrainingLog table have many entries for one employee, and possibly no entries for employee who have not completed any training?

Can there be many employees with the same jobccno?

Is there a table which lists all of the possible jobs?

 

[yan19454]

I posted on the sqlservercenter forum. thx.

Some one helped to write
SELECT E.JobCCNo AS CostCenter,D.QuizName
,COUNT(DISTINCT E.EmplNo) AS NumberOfEmployees
,COUNT(DISTINCT T.EmplNo) As NumberTrained
,COUNT(DISTINCT E.EmplNo) - COUNT(DISTINCT T.EmplNo) AS NumberNotTrained
FROM OcchEmp E
CROSS JOIN (
SELECT DISTINCT quizname
FROM tblCurrentWinTrainingLog T1 ) D
LEFT JOIN tblCurrentWinTrainingLog T ON E.EmplNo = T.EmplNo AND D.quizname = T.quizname
GROUP BY E.JobCCNo, D.quizname
ORDER BY E.JobCCNo, D.quizname

. it is a clear version.