count distinct record with join
-
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
I have table call tblCurrentWinTrainingLog like this:
empId tdate trainingname
012799 8/11/2006 Slips and Falls
012799 8/11/2006 Driver Safety
101697 7/25/2006 Slips and Falls
101697 7/25/2006 Back Injury Prevention
101697 7/25/2006 Slips and Falls
OcchEmp table like this
EMPLNO JobCCNo
100912 030516201
100494 030527100
101549 010206220
072512 030536210
101024 030516201
101679 010206220
I want to cal how many emp in the cost center take the training for different training. some empl take mult training like 101697. it should count as one empl. the join need to be in the emp table and training log with emplno. since training log does not have JobCCNO. Thx.
the result should be
JObCCNO complete training name
010206220 1 Slips and Falls
010206220 1 Back Injury Prevention
...
I can use this query to get the completed count for the corresponding jobccno,
select Count(distinct c.Emplno) as CID, c.QuizName,o.jobccno from tblCurrentWinTrainingLog c join OcchEmp o on c.Emplno=o.EMPLNO group by c.QuizName,o.JobCCNO
go
I can get the total count of the employee in the job cost center.
using this query
select count(Emplno) as TotalEmpCount, jobccno from occhemp group by jobccno
I need a query that give me the
jobccno , completedtraining count, incompletedtraing, totalemployeecount.
How to connect the query together to get the result ? Thx.
Here is what I have right now ? I do not know whether you have better way to do this ?
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
-
if they have training date. they have the record on the log. then mean they have training.
-
Right, I kind of got that part, but how do you know if they have not completed training? A record in the log without a training date?
-
for me , it seems they only request incompleted count. total employee-completed empl count. it will be incompleted count. if they do not have the training record in the log. they did not take the training.
-
Your column names seem to be confused, but this may be what you want:
-- *********************
-- Test Data
DECLARE @tblCurrentWinTrainingLog TABLE
(
EmpNo CHAR(6) COLLATE DATABASE_DEFAULT NOT NULL
,tdate SMALLDATETIME NOT NULL
,trainingname VARCHAR(30) COLLATE DATABASE_DEFAULT NOT NULL)
INSERT @tblCurrentWinTrainingLog
SELECT '012799', '20060811', 'Slips and Falls' UNION ALL
SELECT '012799', '20060811', 'Driver Safety' UNION ALL
SELECT '101697', '20060725', 'Slips and Falls' UNION ALL
SELECT '101697', '20060725', 'Back Injury Prevention' UNION ALL
SELECT '101697', '20060725', 'Slips and Falls'
DECLARE @OcchEmp TABLE
(
EmpNo CHAR(6) COLLATE DATABASE_DEFAULT NOT NULL
,JobCCNo CHAR(9) COLLATE DATABASE_DEFAULT NOT NULL)
INSERT @OcchEmp
SELECT '100912', '030516201' UNION ALL
SELECT '100494', '030527100' UNION ALL
SELECT '101549', '010206220' UNION ALL
SELECT '072512', '030536210' UNION ALL
SELECT '101024', '030516201' UNION ALL
SELECT '101697', '010206220'
-- End of Test Data
-- *********************
-- Select Statement
-- Use your Table and Column names here
SELECT E.JobCCNo AS CostCenter
,COUNT(DISTINCT E.EmpNo) AS NumberOfEmployees
,COUNT(DISTINCT T.EmpNo) As SomeTraining
,COUNT(DISTINCT E.EmpNo) - COUNT(DISTINCT T.EmpNo) AS NoTraining
FROM @OcchEmp E
LEFT JOIN @tblCurrentWinTrainingLog T ON E.EmpNo = T.EmpNo
GROUP BY E.JobCCNo
-
I noticed that you've updated your initial post with a query using derived tables. That is how I would have done it also. If this is giving you the correct results, I would stick with it.
-
thx. It is much clear version. but if i want to group by quizname. how can I accomplish that ?
-
Using the above test data, and assuming trainingname is the same as QuizName, how about:
SELECT E.JobCCNo AS CostCenter
,D.trainingname AS QuizName
,COUNT(DISTINCT E.EmpNo) AS NumberOfEmployees
,COUNT(DISTINCT T.EmpNo) As NumberTrained
,COUNT(DISTINCT E.EmpNo) - COUNT(DISTINCT T.EmpNo) AS NumberNotTrained
FROM @OcchEmp E
CROSS JOIN (
SELECT DISTINCT trainingname
FROM @tblCurrentWinTrainingLog T1 ) D
LEFT JOIN @tblCurrentWinTrainingLog T ON E.EmpNo = T.EmpNo AND D.trainingname = T.trainingname
GROUP BY E.JobCCNo, D.trainingname
ORDER BY E.JobCCNo, D.trainingname
-
Thx.
Here is my sp according to the posting.
ALTER PROCEDURE dbo.CCPercent
@quiz VARCHAR( 25 )=null,
@cc nvarchar(125) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin
set @sql='SELECT E.JobCCNo AS CostCenter,D.QuizName, E.Department
,COUNT(DISTINCT E.EmplNo) AS TCount
,COUNT(DISTINCT T.EmplNo) As Completed
,COUNT(DISTINCT E.EmplNo) - COUNT(DISTINCT T.EmplNo) AS InCompleted
FROM OcchEmp E
CROSS JOIN (
SELECT DISTINCT quizname
FROM tblCurrentWinTrainingLog T1 ) D
left outer JOIN tblCurrentWinTrainingLog T ON E.EmplNo = T.EmplNo AND D.quizname = T.quizname where 1=1 '
IF @cc IS NOT NULL
set @sql= @sql + ' AND E.JObCCNO in('+ @cc +')'
if @quiz is not null
set @sql= @sql + ' And D.QuizName = '''+ @quiz +''''
set @sql= @sql+' GROUP BY E.JobCCNo, D.quizname, E.Department ORDER BY E.JobCCNo, D.quizname '
IF @Debug = 1
begin
PRINT @sql PRINT ''
END
exec (@sql)
end
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply