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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

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

ElGreco76Commented:
The last join must be an outer join too. I forgot to change it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.