January 8, 2007 at 8:08 am
I have this stored procedure to calculate the percentage of the employee in the cost center. for training.
If the cost center has the record for one of the employee , I will get that right. but if no records in the cost center. It did not come out as TCount at all.
I tried to modify the stored procedure from cross join to left join. it gave me error. any one to handle that. Thx.
select * from tblInternalEduModule Where Category ='Win'
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
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 '
set @sql= @sql
if @quiz ='-1' and @quiz=''
set @sql= @sql
IF @Debug = 1
begin
PRINT @sql PRINT ''
END
exec (@sql)
end
it gave me the error for where condition.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
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 * from tblInternalEduModule Where Category ='''Win''' 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 '
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
January 8, 2007 at 9:22 am
Let me get some low lying fruit. The following statement is erroneous because of the t1. The t1 doesn't belong after the where clause.
(
select * from tblInternalEduModule Where Category ='''Win''' T1 ) D
Russel Loski, MCSE Business Intelligence, Data Platform
January 9, 2007 at 5:08 am
Here is the working version. Thx.
CREATE 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.Quiz, 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 * from tblInternalEduModule T1 ) D
left outer JOIN tblCurrentWinTrainingLog T ON E.EmplNo = T.EmplNo AND D.quiz = 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.Quiz = '''+ @quiz +''''
set @sql= @sql+' GROUP BY E.JobCCNo, D.quiz, E.Department ORDER BY E.JobCCNo, D.quiz '
set @sql= @sql
if @quiz ='-1' and @quiz=''
set @sql= @sql
IF @Debug = 1
begin
PRINT @sql PRINT ''
END
exec (@sql)
end
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply