February 6, 2007 at 7:16 am
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*. according the due date end user select and ALTER temp table
find the nomatch table in the occh and return the table
if the end user select the data, then it will show just the empl take the
test during the period.
otherise including all the empl not take the test.
1. test a.with previous year training and current year training
b. no previous year training record and current year traning year
c. with previous traning record and no current year training.
*/
ALTER PROCEDURE dbo.DueListWithPreviousTraining
@quiz VARCHAR( 25 ),
@fromdate datetime = NULL,
@todate datetime = NULL,
@unit nchar(5) = NULL,
@cc nvarchar(4000) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin
CREATE TABLE #tempPreviousWinTrainingLog (
[EmplNO] [nvarchar] (50) ,
[CTDate] [datetime] NULL ,
[QuizName] [char] (25)
)
set @sql='SELECT distinct EmplNO , QuizName, CTDate
FROM tblPreviousWinTrainingLog b
WHERE CTDate IN (
SELECT MAX(CTDate)
FROM tblPreviousWinTrainingLog a
WHERE b.EmplNO = a.EmplNO group by b.QuizName ) '
IF @quiz IS NOT NULL and len(@quiz)<>0
set @sql = @sql + ' AND b.QuizName =''' + @quiz +''''
IF @fromdate IS NOT NULL
begin
set @fromdate= DATEADD(year,-1, @fromdate)
set @sql = @sql + ' AND b.CTDate > ''' + convert(char(13), @fromdate,112) + ''''
end
IF @todate IS NOT NULL
begin
set @todate= DATEADD(year,-1, @todate)
set @sql = @sql + ' and b.CTDate <''' + convert(char(13), @todate,112) + ''''
end
set @sql='Insert into #tempPreviousWinTrainingLog (EmplNO,QuizName, CTDate)'+ @sql
exec (@sql)
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
set @sql='select * from #tempPreviousWinTrainingLog order by EmplNO '
set @sql ='select distinct o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o, tblPreviousWinTrainingLog L
where exists (select *
from dbo.#tempPreviousWinTrainingLog L
where L.Emplno= o.EMPLNO )'
set @sql =@sql+ ' and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO '
IF @quiz IS NOT NULL
begin
set @sql = @sql + ' AND c.QuizName =''' + @quiz +''''
end
IF @cc IS NOT NULL
begin
set @sql = @sql+' and o.JObCCNO in('+ @cc +')'
end
IF @unit IS NOT NULL
if len(ltrim(@unit))<>0
begin
set @sql = @sql + ' AND o.Unit = '''+ @unit +''''
end
end
IF @Debug = 1
BEGIN
PRINT @sql PRINT ''
END
exec (@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks.
It kept time me because the run time.
February 6, 2007 at 9:37 am
Here is the print out if I set debug=1. I looked at the execute plan. It is two table scan costly.
One is here since it need to scan the table to max date
set @sql='SELECT distinct EmplNO , QuizName, CTDate
FROM tblPreviousWinTrainingLog b
WHERE CTDate IN (
SELECT MAX(CTDate)
FROM tblPreviousWinTrainingLog a
WHERE b.EmplNO = a.EmplNO group by b.QuizName )
second is here.
set @sql='select * from #tempPreviousWinTrainingLog order by EmplNO '
set @sql ='select distinct o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o, tblPreviousWinTrainingLog L
where exists (select *
from dbo.#tempPreviousWinTrainingLog L
where L.Emplno= o.EMPLNO )'...
since it need to emplNO scan not exist...big scan here, too.
Insert into #tempPreviousWinTrainingLog (EmplNO,QuizName, CTDate)SELECT distinct EmplNO , QuizName, CTDate
FROM tblPreviousWinTrainingLog b
WHERE CTDate IN (
SELECT MAX(CTDate)
FROM tblPreviousWinTrainingLog a
WHERE b.EmplNO = a.EmplNO group by b.QuizName ) AND b.QuizName ='Back Injury Competancy'
select distinct o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc],Manager, Department, JobCCNo
from dbo.OcchEmp o, tblPreviousWinTrainingLog L
where exists (select *
from dbo.#tempPreviousWinTrainingLog L
where L.Emplno= o.EMPLNO ) and not exists (select 1 from tblCurrentWinTrainingLog c
where o.EMPLNO=c.EMPLNO AND c.QuizName ='Back Injury Competancy')
February 6, 2007 at 12:17 pm
the first query looks like
find the last date that the quizzer (empno) took
insert into #tempPreviousWinTrainingLog (EmplNO,QuizName, CTDate)
select a.EmplNO,a.quizname,MAX(CTDate) as last_date
from tblPreviousWinTrainingLog a
where a.QuizName ='Back Injury Competancy'
group by a.quizname,a.EmplNO
/*all that took at least one quiz but not the latest?*/
select o.EMPLNO ,[FULL NAME] , [Unit],[Unit Desc]
,Manager, Department, JobCCNo
from dbo.OcchEmp o /*employee info*/
inner join dbo.#tempPreviousWinTrainingLog L /*previous quizzes*/
on o.EMPLNO=L.EmplNO
where not exists ( /*not in a quiz*/
select * from tblCurrentWinTrainingLog c where
c.EMPLNO=L.Emplno
)
February 6, 2007 at 12:19 pm
yes.
1. find the most recent quiz empl take
2. not in the current training table.
-->get the due date.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply