?
Solved

Error in  SQL Query

Posted on 2008-11-17
6
Medium Priority
?
173 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:mmalik15
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:ElGreco76
ID: 22976441
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
 

Author Comment

by:mmalik15
ID: 22976571
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
 
LVL 9

Accepted Solution

by:
Ernariash earned 2000 total points
ID: 22976710

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:mmalik15
ID: 22976712
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
 
LVL 1

Expert Comment

by:ElGreco76
ID: 22976809
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
 
LVL 1

Expert Comment

by:ElGreco76
ID: 22976821
The last join must be an outer join too. I forgot to change it.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question