/*
Query: Calculate Leave working days in a table column as a default value

Prepared by: Sarabpreet Singh Anand

Contact: sarabpreet.anand@gmail.com
Version: 1B--updated 26-10-2012 12:20 AM IST

Website: www.sarabpreet.com
Twitter: @Sarab_SQLGeek
*/

SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;


IF OBJECT_ID (N'dbo.LeaveWD', N'FN') IS NOT NULL
DROP FUNCTION dbo.LeaveWD;
GO
Create FUNCTION dbo.LeaveWD (@Leave_Start datetime, @Leave_End datetime) 
RETURNS int
AS
BEGIN
 Declare @a	 as DateTime 
set @a = @Leave_Start

     declare @weekday int
     set @weekday = 0

WHILE @a <= @Leave_End
BEGIN
   IF (convert(int,DATEPART(dw,@a))) not in (1,7)
   Begin
   SET @weekday = (@weekday + 1)
   End
   set @a = DATEADD(d,1,@a)
END
     
     RETURN(@weekday)
END
GO

--to test
--SELECT dbo.LeaveWD('2012-10-20 00:01:44.613','2012-10-28 00:01:44.613');


Create table test_leaves
(
leave_working_day as (dbo.LeaveWD(Leave_Start,Leave_End)),
Leave_Start datetime,
Leave_End datetime
)

--sample data insert test
--insert into test_leaves(Leave_Start,Leave_End) values('2012-10-20 00:01:44.613','2012-10-28 00:01:44.613')


--getting results out of table
--select * from test_leaves