June 5, 2007 at 8:05 am
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
June 5, 2007 at 9:00 am
Frances
Without an explanation of how the actual result set differs from the anticipated one, never mind any DDL or sample data, it's difficult to help you. However, I don't think you really need the dynamic SQL, and I would start by rewriting the procedure something like I've done below (assuming you don't have any nulls in the Unit column in OcchEmp or in the CourseName column in tblInternalEduModel).
ALTER
PROCEDURE dbo.UnitPercent
@quiz VARCHAR( 25 )=null,
@unit nvarchar(125) = NULL
as
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
WHERE Category='Win') D
LEFT JOIN tblCurrentWinTrainingLog T
ON E.EmplNo = T.EmplNo
AND D.Quiz = T.quizname
WHERE E.Unit = CASE @unit WHEN NULL THEN E.Unit ELSE @unit END
AND D.Quiz = CASE @quiz WHEN NULL THEN D.Quiz ELSE @quiz END
GROUP BY E.Unit, D.quiz, E.[Unit Desc]
ORDER BY E.Unit, D.quiz
GO
John
June 5, 2007 at 9:14 am
CREATE TABLE [dbo].[OcchEmp] (
[SSN] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HIRE_DATE] [smalldatetime] NULL ,
[DOB] [smalldatetime] NULL ,
[EMPLNO] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FULL NAME] [nvarchar] (26) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unit] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Unit Desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Department] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Manager] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobCCNo] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CCNAME] [nvarchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblInternalEduModule] (
[ModuleID] [int] NULL ,
[Category] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CourseName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quiz] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequriedBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblCurrentWinTrainingLog] (
[EmplNO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CTDate] [datetime] NOT NULL ,
[QuizName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ModuleName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Here is the ddl. The result did not come out right ,too.
when I keyed in unit and quiz as null . no result come up
I think the problem for the following is not right.
WHERE E.Unit = CASE @unit WHEN NULL THEN E.Unit ELSE @unit END
AND D.Quiz = CASE @quiz WHEN NULL THEN D.Quiz ELSE @quiz END
I think the CROSS JOIN is not right here.
June 5, 2007 at 10:03 am
I changed the
WHERE E.Unit =COALESCE(@unit, Unit)
AND D.Quiz =COALESCE(@quiz, Quiz)
it worked. Thx.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply