Error in SQL Query

Hi Experts,
i have graduate accountants training tables as follows

tblGradlAcctTrainingCategories (CatID(pk),Category)
tblGradAcctTrainingSubject(SubID(pk),Subject,CatID(fk))
tblEmployee(EmployeeID(pk),Firstname,etc)
tblEmployeeJobTitle(EmployeeID(pk),JobTitleID)
tblGradAcctTrainingHours(TrainingHourID(pk),EmployeeID,SubID,CatID,StartDate,EndDate,NoOfHours)
tblEmployeeJobTitle(EmployeeID(fk),JobTitleID(fk).

I have a form where User selects a Accounting Training category and then selects a StartDate and EndDate and then User should view all the employees from employee table and all the subjects under the category and no of hours they have been trainined on each subject in a timetable style. I have created a SQL Query which i m attaching in snippet. This query works using joins but it does not dispaly all the employees. Just the employees from the tblGradAcctTrainingHours table. Where as i want to dispaly all the employee from the tblEmployeeJobTitle table where jobTitleID=3. And if no record exists then just zeros. Please have a look on the attached query.

Thanks,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Ex_GetGradAcctHours]
 
@CatID int,
@startDate varchar(100),
@endDate varchar(100)
 
As 
 
Begin
 
Declare @SubID   int
Declare @Desc   varchar(100)
Declare @sql   varchar(8000)
Declare @rowNo as int
Declare @totalHours as int
 
 
DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject where CatID=@CatID
  
 
 
select @rowNo=0
select @totalHours=0
 
SET @sql   = 'SELECT E.EmployeeID ID,E.Firstname + char(32)+ E.lastname as Name'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS = 0)
BEGIN
   select @rowNo=@rowNo+1
   
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + 'Cast(SUM(CASE ES.SubjectID WHEN '+ CAST(@subID AS VARCHAR(10)) + ' THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS ' +  char(39)+@Desc+char(39)
   
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', Cast(SUM(ES.NoOfHours) as varchar(8000)) AS Total '+
 
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
	   'where Convert(varchar(50),ES.DateFrom,103)= '+ char(39)+ @startDate + char(39) +' and Convert(varchar(50),ES.DateTo,103)='+ char(39)+ @endDate + char(39) +
	   'and ES.SubjectID in(SELECT SubjectID FROM tblGradAcctTrainingSubject where CatID='+ CAST(@CatID AS VARCHAR(10))+ ') GROUP BY E.EmployeeID,firstname,lastname'
print @sql
 
 
 
EXEC (@sql)
 
end

Open in new window

mmalik15Asked:
Who is Participating?
 
ErnariashCommented:

The Where Clause the reason your are filtering your results from tblGradAcctTrainingHours.
Please do something like this..
SET @sql = @sql + ', Cast(SUM(CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) as varchar(8000)) AS Total '+

'FROM tblGradlAcctTrainingCategories C '+
'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
' AND Convert(varchar(50),ES.DateFrom,103)= '+ char(39)+ @startDate + char(39) +' and Convert(varchar(50),ES.DateTo,103)='+ char(39)+ @endDate + char(39) +
' AND ES.SubjectID in(SELECT SubjectID FROM tblGradAcctTrainingSubject where CatID='+ CAST(@CatID AS VARCHAR(10))+ ') '
'RIGHT OUTER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+

'GROUP BY E.EmployeeID,firstname,lastname'
print @sql


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Ex_GetGradAcctHours]
 
@CatID int,
@startDate varchar(100),
@endDate varchar(100)
 
As 
 
Begin
 
Declare @SubID   int
Declare @Desc   varchar(100)
Declare @sql   varchar(8000)
Declare @rowNo as int
Declare @totalHours as int
 
 
DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject where CatID=@CatID
  
 
 
select @rowNo=0
select @totalHours=0
 
SET @sql   = 'SELECT E.EmployeeID ID,E.Firstname + char(32)+ E.lastname as Name'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS = 0)
BEGIN
   select @rowNo=@rowNo+1
   
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + 'Cast(SUM(CASE ES.SubjectID WHEN '+ CAST(@subID AS VARCHAR(10)) + ' THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS ' +  char(39)+@Desc+char(39)
   
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', Cast(SUM(CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) as varchar(8000)) AS Total '+
 
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
		' AND Convert(varchar(50),ES.DateFrom,103)= '+ char(39)+ @startDate + char(39) +' and Convert(varchar(50),ES.DateTo,103)='+ char(39)+ @endDate + char(39) +
		' AND ES.SubjectID in(SELECT SubjectID FROM tblGradAcctTrainingSubject where CatID='+ CAST(@CatID AS VARCHAR(10))+ ') '
      'RIGHT OUTER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
	
	   'GROUP BY E.EmployeeID,firstname,lastname'
print @sql
 
 
 
 
EXEC (@sql)
 
end

Open in new window

0
 
ElGreco76Commented:
You need an outer join to see all employees.

Try this:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Ex_GetGradAcctHours]
 
@CatID int,
@startDate varchar(100),
@endDate varchar(100)
 
As 
 
Begin
 
Declare @SubID   int
Declare @Desc   varchar(100)
Declare @sql   varchar(8000)
Declare @rowNo as int
Declare @totalHours as int
 
 
DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject where CatID=@CatID
  
 
 
select @rowNo=0
select @totalHours=0
 
SET @sql   = 'SELECT E.EmployeeID ID,E.Firstname + char(32)+ E.lastname as Name'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS = 0)
BEGIN
   select @rowNo=@rowNo+1
   
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + 'Cast(SUM(CASE ES.SubjectID WHEN '+ CAST(@subID AS VARCHAR(10)) + ' THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS ' +  char(39)+@Desc+char(39)
   
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', Cast(SUM(CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) as varchar(8000)) AS Total '+
 
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'RIGHT OUTER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
	   'where Convert(varchar(50),ES.DateFrom,103)= '+ char(39)+ @startDate + char(39) +' and Convert(varchar(50),ES.DateTo,103)='+ char(39)+ @endDate + char(39) +
	   'and ES.SubjectID in(SELECT SubjectID FROM tblGradAcctTrainingSubject where CatID='+ CAST(@CatID AS VARCHAR(10))+ ') GROUP BY E.EmployeeID,firstname,lastname'
print @sql
 
 
 
EXEC (@sql)
 
end

Open in new window

0
 
mmalik15Author Commented:
Thanks for the comment
it still gives me the same results. only shows me the employee from tblGradAcctTrainingHours table which has a join to tblGradAcctTrainingSubject on subjectID in the selected category but not all the employees in the employee table
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mmalik15Author Commented:
I have changed some joins and attaching a simpler version of query with still same problem, not fetching all the employeoes
SELECT EJ.EmployeeID ID,E.Firstname + char(32)+ E.lastname as Name, 
 
Cast(SUM(CASE ES.SubjectID WHEN 7 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 'Performance Management', 
Cast(SUM(CASE ES.SubjectID WHEN 8 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 'Audit & Assurance', 
Cast(SUM(CASE ES.SubjectID WHEN 9 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 
'Financial Management', 
Cast(SUM(CASE ES.SubjectID WHEN 10 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 'Taxation', 
Cast(SUM(CASE ES.SubjectID WHEN 11 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 'Holidays', 
Cast(SUM(CASE ES.SubjectID WHEN 12 THEN ES.NoOfHours ELSE 0 END) as varchar(8000)) AS 'Sickness', 
Cast(SUM(ES.NoOfHours) as varchar(8000)) AS Total 
 
FROM  tblEmployeeJobTitle EJ
 
left join  dbo.tblEmployee E on EJ.EmployeeID=E.EmployeeID
 
left join tblGradAcctTrainingHours ES ON E.EmployeeID = ES.EmployeeID 
 
INNER JOIN dbo.tblGradAcctTrainingSubject S ON ES.SubjectID=S.SubjectID
 
left join dbo.tblGradlAcctTrainingCategories C on S.CatID = C.CatID 
 
 
where Convert(varchar(50),ES.DateFrom,103)= '27/10/2008' and 
Convert(varchar(50),ES.DateTo,103)='10/11/2008'and C.CatID=2 and EJ.JobtitleID=1
GROUP BY EJ.EmployeeID,firstname,lastname

Open in new window

0
 
ElGreco76Commented:
There are still some problems with your joins. The starting table of your simpler query must be your employee table and uou must use outer joins.

Try this:
SELECT EJ.EmployeeID ID,E.Firstname + char(32)+ E.lastname as Name, 
 
Cast(SUM(CASE ES.SubjectID WHEN 7 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 'Performance Management', 
Cast(SUM(CASE ES.SubjectID WHEN 8 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 'Audit & Assurance', 
Cast(SUM(CASE ES.SubjectID WHEN 9 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 
'Financial Management', 
Cast(SUM(CASE ES.SubjectID WHEN 10 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 'Taxation', 
Cast(SUM(CASE ES.SubjectID WHEN 11 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 'Holidays', 
Cast(SUM(CASE ES.SubjectID WHEN 12 THEN (CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END) ELSE 0 END) as varchar(8000)) AS 'Sickness', 
Cast(SUM((CASE WHEN ES.NoOfHours IS NULL THEN 0 ELSE ES.NoOfHours END)) as varchar(8000)) AS Total 
 
FROM  dbo.tblEmployee E
 
left outer join  tblEmployeeJobTitle EJ on EJ.EmployeeID=E.EmployeeID
 
left outer join tblGradAcctTrainingHours ES ON E.EmployeeID = ES.EmployeeID 
 
left outer join dbo.tblGradAcctTrainingSubject S ON ES.SubjectID=S.SubjectID
 
left join dbo.tblGradlAcctTrainingCategories C on S.CatID = C.CatID 
 
 
where Convert(varchar(50),ES.DateFrom,103)= '27/10/2008' and 
Convert(varchar(50),ES.DateTo,103)='10/11/2008'and C.CatID=2 and EJ.JobtitleID=1
GROUP BY EJ.EmployeeID,firstname,lastname

Open in new window

0
 
ElGreco76Commented:
The last join must be an outer join too. I forgot to change it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.