join and count

[yan19454]

I have three tables OcchEmp,tblInternalEduModule, tblCurrentWinTrainingLog



I want to get the total employee count in OcchEmp group by unit as Tcount

I need to the count employee in tblCurrentWinTrainingLog as count group by module name tblInternalEduModule which category is WIN and group by occhemp unit and tblInternalEduModule coursename.



I wrote the quiz. I think the join is not right. the result did not come out right.



Thx.







SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO









ALTER PROCEDURE dbo.UnitPercent
@quiz VARCHAR( 25 )=null,
@unit nvarchar(125) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin

set @sql='SELECT E.Unit AS Unit,D.Quiz , E.[Unit Desc]
,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 CourseName as Quiz from tblInternalEduModule T1 where Category=''Win'' ) D
left outer JOIN tblCurrentWinTrainingLog T ON E.EmplNo = T.EmplNo AND D.quiz = T.quizname where 1=1 '
IF @unit is not null
set @sql= @sql + ' AND E.Unit ='''+ @unit +''''


if @quiz is not null
set @sql= @sql + ' And D.Quiz = '''+ @quiz +''''




set @sql= @sql+' GROUP BY E.Unit, D.quiz, E.[Unit Desc] ORDER BY E.Unit, D.quiz '
IF @unit ='-1' or @unit=''
set @sql= @sql


if @quiz ='-1' and @quiz=''
set @sql= @sql

IF @Debug = 1
begin

PRINT @sql PRINT ''


END





exec (@sql)

end



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[SQLSister]

I got lost trying to follow what you wanted it to do. COuld you give me some sameple data and sample results, then it might be clearer.

Questions about posting. See faq183-874