What Join? ...if any - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

What Join? ...if any

Author

Topic

chenko
Starting Member

24 Posts

Posted - 2007-07-11 : 07:31:36

[code]
SELECT Employee_Name, Employee_ID,
Employee_HolidayAllocation,
SUM(Holiday_Value) As HolidayTotal,
(Employee_HolidayAllocation - SUM(Holiday_Value)) As HolidayRemaining
FROM Employee
INNER JOIN Holidays ON Holidays.Employee_ID = Employee.ID
WHERE Holidays.Employee_ID = 113
AND Holiday_Date BETWEEN '01 April 2007' AND '31 March 2008'
GROUP BY Employee_ID, Employee_HolidayAllocation, Employee_Name
[/code]

This is the statment I am using now.

Returns a single result, assuming there are records in the "Holidays" table between the dates. But I want it to still show a record if there is no records between the dates.

When I go from 2006 to 2007 instead I get no records. Thats normal. But I would rather it return the employee name, id and allocation which always exists and just 0 for the other fields.

I thought a Left Join might sort this but it doesn't help, I cant get my head around it but I tried a Right, Left, Inner, left outer, right outer. all with no results but no errors.

Or do I totally have the wrong idea?

Thanks.:)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-11 : 07:34:39

[code]SELECT Employee_Name, Employee_ID,
Employee_HolidayAllocation,
SUM(Holiday_Value) AS HolidayTotal,
(Employee_HolidayAllocation - SUM(Holiday_Value)) AS HolidayRemaining
FROM Employee
LEFT JOIN Holidays
ON Holidays.Employee_ID = Employee.ID
AND Holiday_Date BETWEEN '01 April 2007' AND '31 March 2008'
WHERE Employee.ID = 113
GROUP BY Employee_ID, Employee_HolidayAllocation, Employee_Name[/code]

KH
[spoiler]Time is always against us[/spoiler]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-07-11 : 08:41:02

quote:

SELECT Employee_Name, Employee_ID,
Employee_HolidayAllocation,
SUM(Holiday_Value) AS HolidayTotal,
(Employee_HolidayAllocation - SUM(Holiday_Value)) AS HolidayRemaining
FROM Employee
LEFT JOIN Holidays
ON Holidays.Employee_ID = Employee.ID
AND Holiday_Date BETWEEN '01 April 2007' AND '31 March 2008'
WHERE Employee.ID = 113
GROUP BY Employee_ID, Employee_HolidayAllocation, Employee_Name

It is cleaner (and often more efficient -- especially in SQL 2000) to use a derived table. The key thing that tells you it should be rewritten is that you are grouping on Employee_HolidayAllocation and Employee_Name, when logically the only grouping required is to group by Employee_ID to get the results needed. Thus, use a derived table to calculate holiday hours per employee:


SELECT	
  Employee_Name,	
  Employee_ID,
  Employee_HolidayAllocation,
  ISNULL(Total.HolidayTotal,0) as HolidayTotal,
  (Employee_HolidayAllocation - ISNULL(Total.HolidayTotal,0)) AS HolidayRemaining
FROM 	
  Employee 
LEFT JOIN 
  (
   SELECT 
     Employee_ID, SUM(Holiday_Value) as HolidayTotal
   FROM 
     Holidays 
   WHERE 
     Holiday_Date BETWEEN '01 April 2007' AND '31 March 2008'
   GROUP BY 
     Employee_ID
    ) Total
	ON Total.Employee_ID = Employee.ID
WHERE 
  Employee.ID = 113

It should look a little clearer like this as well; we are selecting all employees and joining to a subquery that returns their total holiday hours for each.

We also use ISNULL() to return 0 instead of NULL if they have taken no holiday hours in that time frame.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources