Function to calculate holidays

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

General SQL Server Forums

New to SQL Server Programming

Function to calculate holidays

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2006-05-31 : 07:27:56

Hello,

First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).

If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.

I'm trying to produce a function which will do the following :

1) Pass in the employee number
2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag
3) If full time, look up to tbl_Holidays and count number of days
4) If part time, look up to tbl_Holidays and count number of hours
5) After this, return the number of holidays booked

My code is as follows :
=============
CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )

RETURNS Float
AS
BEGIN

-- Declare fields
DECLARE @FullTime int

-- Determine if Part Time or Full Time
SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails

IF @FullTime = 1
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

IF @FullTime = 0
SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef
FROM dbo.tbl_Holidays
GROUP BY EmployeeNumber
HAVING (EmployeeNumber = @EmpNo)

END
==========

Can someone please let me know where I'm going wrong, or what I need to do to get this function done?

Thanks,
J

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-31 : 08:37:08

Doing Wrong
-- Return Float ... but returning int & some other set of records
-- After IF <Condition> But a Begin -- End Block
-- SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails
-- Should have the @EmpNo, otherwise creating a record set of [FullTime]s
-- "Having clause" is used incorrectly (Where has to be used in this case) - Learn the syntax of "Having"
-- U can avoid "If", by a where clause
-- U can avoid setting value to Fulltime and checking against it, by combining that tbl_EmployeeDetails with tbl_Holidays
-- Many more

Srinika

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-31 : 09:04:51

http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources