how to improve this query

[yan19454]

I tried to get the recentest training data for employee

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 )

Here is my table defination:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPreviousWinTrainingLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPreviousWinTrainingLog]
GO


CREATE TABLE [dbo].[tblPreviousWinTrainingLog] (
[EmplNO] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CTDate] [datetime] NULL ,
[QuizName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModuleName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Score] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

 

[yan19454]

the whole query actually is like that

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 Competency' AND b.CTDate > '20050701 ' and b.CTDate <'20060630 '

 

[gmmastros]

This should produce the same results and will probably be faster.

Select  EmplNo,
        'Back Injury Competency' As QuizName,
        Max(CTDate) As CTDate
FROM    tblPreviousWinTrainingLog b 
Where   CTDate > '20050701'
        And CTDate < '20060630'
        AND QuizName ='Back Injury Competency' 
Group By EmplNo

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

This is what I get, makes your WHERE clause more sargable:

SELECT distinct EmplNO , QuizName, CTDate
FROM  tblPreviousWinTrainingLog b 
WHERE CTDate [!]=[/!] 
(SELECT MAX(CTDate)
FROM tblPreviousWinTrainingLog 
WHERE EmplNO = b.EmplNO and ltrim(rtrim(CTDate)) between '20050702' and '20060629') AND b.QuizName ='Back Injury Competency'

<.

 

[monksnake]

DAAAAMMNNN you George, you always beat me to the punch

<.

 

[yan19454]

My whole procedure like this

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 dbcchEmp 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
set @sql = @sql +')'


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


It has too many table scan in my query. I thought about that before and I do not know why to do in my whole stored procedure since it is dyn..

Thx.

 

[gmmastros]

Yan,

You have a lot of conditional logic in this stored procedure, which is probably why you decided to use dynamic sql. In this case, you can probably avoid it. The 'normal' trick to dealing with optional parameters is to use this syntax.

[tt][blue]
Select *
From Table
Where (@Parameter is NULL or Table.Column = @Parameter)
[/blue][/tt]

Of course, your query is a bit more complicated than that, but it is one 'trick' to learn and begin using when you have optional parameters. Even if you end up needing to use dynamic SQL, this trick can simplify the code.

Now, on to your query. It are passing @cc in to the procedure. It appears as though this represents a comma delimited list of JobCCNo's. In the code I show below, I am ignoring that parameter (for now). I encourage you to try this procedure, which doesn't use any dynamic sql. This will return a recordset of employees regardless of JobCCNo. However, I want you to try this and make sure that it returns the correct results. If it does, then I will show you how to deal with a comma delimited list data, but still without using dynamic sql. Notice that I renamed your procedure slightly (so there will be no conflicts with your existing procedure).

Create PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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                                                                   

Set @Quiz = NullIf(@Quiz, '')
Set @Unit = NullIf(@Unit, '')


Select 	o.EMPLNO,
        [FULL NAME] , 
        [Unit],
        [Unit Desc],
        Manager, 
        Department, 
        JobCCNo,
		tblPreviousWinTrainingLog.QuizName,
		tblPreviousWinTrainingLog.MaxDate As CTDate
From	(
        Select  EmplNo,
        		QuizName,
        		Max(CTDate) As MaxDate
        From	tblPreviousWinTrainingLog
        Where   (@Quiz Is NULL Or A.QuizName = @Quiz)
                And (@FromDate Is NULL Or CTDate > @FromDate)
                And (@ToDate Is NULL Or CTDate < @ToDate)
        Group By EmplNo, QuizName
        ) As A
        Inner Join tblPreviousWinTrainingLog
          On  A.EmplNo = tblPreviousWinTrainingLog.EmplNo
          And A.QuizName = tblPreviousWinTrainingLog.QuizName
          And A.MaxDate = tblPreviousWinTrainingLog.CTDate
        Inner Join OcchEmp
          On OcchEmp.EmplNo = tblPreviousWinTrainingLog.EmplNo
Where   (@Unit Is NULL Or OcchEmp.Unit = @Unit)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

I could not find any error on the code.

but the sql gave error.
Server: Msg 170, Level 15, State 1, Procedure DueListWithPreviousTraining_Temp, Line 42
Line 42: Incorrect syntax near ')'.
which is on this line.
Where (@Unit Is NULL Or OcchEmp.Unit = @Unit)
Thx.

 

[gmmastros]

You got the error because you used Begin at the beginning of the stored procedure. There is no corresponding END to go with the begin. At the bottom of the code I posted, put end and run it again. Sorry about that.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

it showed error
Server: Msg 107, Level 16, State 2, Procedure DueListWithPreviousTraining_Temp, Line 17
The column prefix 'o' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure DueListWithPreviousTraining_Temp, Line 17
The column prefix 'A' does not match with a table name or alias name used in the query.
which in on
Select o.EMPLNO,

 

[gmmastros]

remove the O. from that line and try again.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

then
Server: Msg 107, Level 16, State 2, Procedure DueListWithPreviousTraining_Temp, Line 17
The column prefix 'A' does not match with a table name or alias name used in the query.

I think because the from A
Thx.

 

[gmmastros]

Change...

Where (@Quiz Is NULL Or A.QuizName = @Quiz)

To:

Where (@Quiz Is NULL Or QuizName = @Quiz)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

Here is sp;
Create PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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

Set @Quiz = NullIf(@Quiz, '')
Set @Unit = NullIf(@Unit, '')


Select EMPLNO,
[FULL NAME] ,
[Unit],
[Unit Desc],
Manager,
Department,
JobCCNo,
tblPreviousWinTrainingLog.QuizName,
tblPreviousWinTrainingLog.MaxDate As CTDate
From (
Select EmplNo,
QuizName,
Max(CTDate) As MaxDate
From tblPreviousWinTrainingLog
Where (@Quiz Is NULL Or QuizName = @Quiz)
And (@FromDate Is NULL Or CTDate > @FromDate)
And (@ToDate Is NULL Or CTDate < @ToDate)
Group By EmplNo, QuizName
) as A
Inner Join tblPreviousWinTrainingLog
On A.EmplNo = tblPreviousWinTrainingLog.EmplNo
And A.QuizName = tblPreviousWinTrainingLog.QuizName
And A.MaxDate = tblPreviousWinTrainingLog.CTDate
Inner Join OcchEmp
On OcchEmp.EmplNo = tblPreviousWinTrainingLog.EmplNo
Where (@Unit Is NULL Or OcchEmp.Unit = @Unit)

end

Server: Msg 207, Level 16, State 3, Procedure DueListWithPreviousTraining_Temp, Line 17
Invalid column name 'MaxDate'.
Server: Msg 209, Level 16, State 1, Procedure DueListWithPreviousTraining_Temp, Line 17
Ambiguous column name 'EmplNO'.

 

[gmmastros]

A couple more mistakes. I should probably have stayed home today. Anyway, try...

Create PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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                                                                   

Set @Quiz = NullIf(@Quiz, '')
Set @Unit = NullIf(@Unit, '')


Select  tblPreviousWinTrainingLog.EMPLNO,
        [FULL NAME] , 
        [Unit],
        [Unit Desc],
        Manager, 
        Department, 
        JobCCNo,
        tblPreviousWinTrainingLog.QuizName,
        tblPreviousWinTrainingLog.CTDate
From    (
        Select  EmplNo,
                QuizName,
                Max(CTDate) As MaxDate
        From    tblPreviousWinTrainingLog
        Where   (@Quiz Is NULL Or QuizName = @Quiz)
                And (@FromDate Is NULL Or CTDate > @FromDate)
                And (@ToDate Is NULL Or CTDate < @ToDate)
        Group By EmplNo, QuizName
        ) as A
        Inner Join tblPreviousWinTrainingLog
          On  A.EmplNo = tblPreviousWinTrainingLog.EmplNo
          And A.QuizName = tblPreviousWinTrainingLog.QuizName
          And A.MaxDate = tblPreviousWinTrainingLog.CTDate
        Inner Join OcchEmp
          On OcchEmp.EmplNo = tblPreviousWinTrainingLog.EmplNo
Where   (@Unit Is NULL Or OcchEmp.Unit = @Unit)

end

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

George:

Thanks . I did not feel well, too. The sp did run but did not return any record. I do not think I can think.

Take care.

Frances

 

[yan19454]

If I write this quiz, it will have result outcome.
SELECT DISTINCT
tblPreviousWinTrainingLog.EmplNO AS Expr1, MAX(tblPreviousWinTrainingLog.CTDate) AS maxdate, tblPreviousWinTrainingLog.QuizName AS QuizName,
OcchEmp.Unit, OcchEmp.JobCCNo, OcchEmp.Department
FROM tblPreviousWinTrainingLog INNER JOIN
OcchEmp ON tblPreviousWinTrainingLog.EmplNO = OcchEmp.EMPLNO
GROUP BY tblPreviousWinTrainingLog.EmplNO, tblPreviousWinTrainingLog.QuizName, OcchEmp.Unit, OcchEmp.JobCCNo, OcchEmp.Department
HAVING (tblPreviousWinTrainingLog.QuizName = 'Back Injury Competency') AND (MAX(tblPreviousWinTrainingLog.CTDate) > CONVERT(DATETIME,
'2006-07-11 00:00:00', 102)) AND (MAX(tblPreviousWinTrainingLog.CTDate) < CONVERT(DATETIME, '2006-11-11 00:00:00', 102)) AND
(OcchEmp.Unit = N'01')

If I translate to the stored procedue , it did not create any outcome. I think


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO





ALTER PROCEDURE dbo.DueListWithPreviousTraining_Temp
@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

Set @Quiz = NullIf(@Quiz, '')
Set @Unit = NullIf(@Unit, '')


SELECT DISTINCT
tblPreviousWinTrainingLog.EmplNO AS EmplNO , MAX(tblPreviousWinTrainingLog.CTDate) AS MaxDate, tblPreviousWinTrainingLog.QuizName AS QuizName,
OcchEmp.Unit, OcchEmp.JobCCNo, OcchEmp.Department
FROM tblPreviousWinTrainingLog INNER JOIN
OcchEmp ON tblPreviousWinTrainingLog.EmplNO = OcchEmp.EMPLNO
GROUP BY tblPreviousWinTrainingLog.EmplNO, tblPreviousWinTrainingLog.QuizName, OcchEmp.Unit, OcchEmp.JobCCNo, OcchEmp.Department
HAVING ( (@Quiz Is NULL Or tblPreviousWinTrainingLog.QuizName = @Quiz) AND (@FromDate Is NULL Or max(tblPreviousWinTrainingLog.CTDate) > @FromDate)
And (@ToDate Is NULL Or Max(tblPreviousWinTrainingLog.CTDate) < @ToDate) AND
(@Unit Is NULL Or OcchEmp.Unit = @Unit) )
end





GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


Since If I set having as where, it give me error. Right now I do not know what to do.

 

[yan19454]

George:

Will you please help me with that ? I do not know how to do that better ?

helpless !

Thx.

Frances

 

[gmmastros]

Frances,

Please post some sample data and expected results. It'll make it easier for us to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[yan19454]

DECLARE @RC int
DECLARE @quiz varchar(25)
DECLARE @fromdate datetime
DECLARE @todate datetime
DECLARE @unit nchar(5)
DECLARE @cc nvarchar(4000)
DECLARE @debug bit
SELECT @quiz = 'Back Injury Competency'
SELECT @fromdate = NULL
SELECT @todate = NULL
SELECT @unit = NULL
SELECT @cc = NULL
SELECT @debug = NULL
EXEC @RC = [Annual_Edu_Test].[dbo].[DueListWithPreviousTraining] @quiz, @fromdate, @todate, @unit, @cc, @debug
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: Annual_Edu_Test.dbo.DueListWithPreviousTraining'
SELECT @PrnLine = ' Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine

SELECT a.[FULL NAME] AS Expr1, a.Unit AS Expr2, a.[Unit Desc] AS Expr3, a.Department AS Expr4, a.Manager AS Expr5, a.JobCCNo AS Expr6
FROM OcchEmp a INNER JOIN
tblPreviousWinTrainingLog b ON a.EMPLNO = b.EmplNO
GROUP BY a.EMPLNO, a.[FULL NAME], a.Unit, a.[Unit Desc], a.Manager, a.Department, a.JobCCNo, b.QuizName, b.CTDate
HAVING (b.QuizName = 'Back Injury Competency') AND (MAX(b.CTDate) > '05/15/06') AND (MAX(b.CTDate) < '07/15/06')


occhemp view

[Full Name] , Unit, [Unit Desc], Dept, Manager, JObCCNO, EmplNO
AAA 01 What which EFT 01415 04545
AAB 02 What2 which2 EFT 01416 04546
AAc 02 What2 which3 EFT 01416 045467
AAE 02 What2 which2 EFT 01416 04549
AAD 02 What2 which3 EFT 01416 045468



tblCurrentWinTrainingLog
EmplNO CTDate QuizName
04545 8/4/2006 Back Injury Competency
04545 6/1/2007 Back Injury Competency
04546 1/26/2007 Sharps Injury Prevention
04546 6/15/2007 Slips and Falls
090100 1/26/2007 Back Injury Prevention
064295 1/26/2007 Slips and Falls


tblPreviousWinTrainingLog
emplNO
04545 7/14/2005 Back Injury Competency
04545 7/4/2005 Back Injury Competency
04546 7/14/2005 Back Injury Competency
04546 7/4/2005 Back Injury Competency
04546 8/4/2005 Back Injury Competency
062976 6/1/2006 Slips and Falls
090902 1/26/2006 Sharps Injury Prevention
101927 6/15/2006 Slips and Falls
090100 1/26/2006 Back Injury Prevention
064295 1/26/2006 Slips and Falls


I will let you select the unit and JobCCNO, due date and quizname. If the due date is not selected, they mean the current fiscal year which is 07/01/06-06/31/07

the employees will be due in the selected quiz in one year

ig;
04545 has the most recent quiz in 7/14/2005 for 'Back Injury Competency'( in tblPreviousWinTrainingLog table) and
has record in 8/4/2006 Back Injury Competency ( in tblCurrentWinTrainingLog table )

it will not in the due list

but 04546 have most recent quiz 08/04/05 for Back Injury Competency'( in tblPreviousWinTrainingLog table),
has not record in Back Injury Competency ( in tblCurrentWinTrainingLog table )
it will be in due list in 08/06


the result for the due list for Back Injury Competency in the fiscal year if the user do not select any criteria will be. By the way , the JObcCNO is multiple selection

AAB 02 What2 which2 EFT 01416 04546
AAc 02 What2 which3 EFT 01416 045467
AAE 02 What2 which2 EFT 01416 04549
AAD 01 What2 which3 EFT 01416 045468

Thx.