SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Get 2 Day - AM, PM & FullDay exclude holiday&WKend
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 07/03/2013 :  03:08:42  Show Profile  Reply with Quote
Hi All,

Can anyone share with me stored procedure which i input two date and exclude the holiday & weekend but allow to add weekend if there is exception.

Example 1:

01/07/2013 08:00 - 07/07/2013 13:00
Holiday - 03/07/2013
Saturday working - 06/07/2013

01/07 1/2 day - Morning Off
02/07 1 day
03/07 0 day - holiday
04/07 1 day
05/07 1 day
06/07 1 day - Saturday working day
07/07 0 day - Sunday weekend (off)
-----------------------
Total Leave Taken: 4 1/2 Days


Example 2:

01/07/2013 08:00 - 06/07/2013 13:00
Saturday working - 06/07/2013

01/07 1/2 day - Morning Off
02/07 1 day
03/07 1 day
04/07 1 day
05/07 1 day
06/07 1/2 day - Noon Off (Sat working Day)

-----------------------
Total Leave Taken: 5 Days


To keep track weekend working day or holiday, i used table:
CREATE TABLE [dbo].[LMS_Events](
[hol_id] [int] IDENTITY(1,1) NOT NULL,
[legalentity_id] [int] NULL,
[Description] [nvarchar](500) NOT NULL,
[start_date] [datetime] NOT NULL,
[end_date] [datetime] NOT NULL,
[catid] [int] NULL,
CONSTRAINT [PK_LMS_Events] PRIMARY KEY CLUSTERED
(
[hol_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


catid = 1 mean holiday
catid = 2 mean weekend have to work

Please advise.

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/03/2013 :  03:47:50  Show Profile  Reply with Quote
so which will contain levaes taken? also for half day how will the entry be? for holidays spanning multiple days, will there be one record or multiple records in LMS_Events?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 07/03/2013 :  04:27:39  Show Profile  Reply with Quote
Hi,

I have this function but not yet exclude the weekend & holiday & deduct the leave if weekend set as working day:-

ALTER FUNCTION [dbo].[WorkTime] (@StartDate DATETIME, @FinishDate DATETIME)
--select dbo.[WorkTime]('01/Jul/2013 09:00','02/Jul/2013 13:00') -- 1.5 Days
--select dbo.[WorkTime]('01/Jul/2013 09:00','02/Jul/2013 18:00') -- 2 Days (This not working, i got 2.125 ????????????)
--select dbo.[WorkTime]('01/Jul/2013 14:00','02/Jul/2013 18:00') -- 1.5 Days
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @Temp FLOAT
SET @Temp=0

DECLARE @Temp2 FLOAT
SET @Temp2=0

DECLARE @FirstDay VARCHAR(9)
SET @FirstDay = CONVERT(VARCHAR(9),@StartDate, 112)

DECLARE @LastDay VARCHAR(9)
SET @LastDay = CONVERT(VARCHAR(9),@FinishDate, 112)

DECLARE @StartTime VARCHAR(9)
SET @StartTime = CONVERT(VARCHAR(9),@StartDate, 108)

DECLARE @FinishTime VARCHAR(9)
SET @FinishTime = CONVERT(VARCHAR(9),@FinishDate, 108)

DECLARE @WorkStart VARCHAR(9)
SET @WorkStart = '09:00:00'

DECLARE @WorkFinish VARCHAR(9)
SET @WorkFinish = '18:00:00'

IF (@StartTime<@WorkStart)
BEGIN
SET @StartTime = @WorkStart
END
IF (@FinishTime>@WorkFinish)
BEGIN
SET @FinishTime=@WorkFinish
END

DECLARE @CurrentDate VARCHAR(9)
SET @CurrentDate = CONVERT(VARCHAR(9),@StartDate, 112)
DECLARE @LastDate VARCHAR(9)
SET @LastDate = CONVERT(VARCHAR(9),@FinishDate, 112)

WHILE(@CurrentDate<=@LastDate)
BEGIN

IF (DATEPART(dw, @CurrentDate)!=1 AND DATEPART(dw, @CurrentDate)!=7)
BEGIN
IF (@CurrentDate!=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = (@Temp + (8*60))

END

ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate!=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @StartTime, @WorkFinish)

END

ELSE IF (@CurrentDate!=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = @Temp + DATEDIFF(MINUTE, @WorkStart, @FinishTime)

END

ELSE IF (@CurrentDate=@FirstDay) AND (@CurrentDate=@LastDay)
BEGIN
SET @Temp = DATEDIFF(MINUTE, @StartTime, @FinishTime)

END

END

SET @CurrentDate = CONVERT(VARCHAR(9),DATEADD(day, 1, @CurrentDate),112)
IF((@TEMP/60)=4)
BEGIN
SET @TEMP2 = (@TEMP/60)/8
END
ELSE
BEGIN
SET @TEMP2 = (@TEMP/60 -1)/8
END
END

RETURN @TEMP2


END




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/03/2013 :  04:39:48  Show Profile  Reply with Quote
So I still didnt get answer to my question.
Also I dont think you need a UDF for this. Also your UDF has few code dependent on servers regional settings which can cause issues in migration etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
225 Posts

Posted - 07/03/2013 :  05:33:50  Show Profile  Reply with Quote
Hi,

I have this simple sql, how can i return exclude days from holiday. I having problem coalesce...?

declare @daterange1 as DateTime
declare @daterange2 as DateTime
set @daterange1 = '15/Jul/2013'
set @daterange2 = '18/Jul/2013'

--Holiday let said 17/Jul/2013 ~ 18/Jul/2013
--holiday day should return 2 day

select
coalesce(sum(1+datediff(day,
(case when h.[start_date] < t.fromdate then t.fromdate else h.[start_date] end),
(case when t.todate > t.todate then t.todate else h.[end_date] end)
), 0)
from (select @daterange1 as fromdate, @daterange2 as todate) t left outer join
LMS_Holiday h
on h.[start_date] <= t.todate and h.end_date >= t.fromdate
where catid=5


Please advise.

Thank you.

Regards,
Micheale
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/03/2013 :  05:42:22  Show Profile  Reply with Quote
you still didnt answer my question
will holiday be stored as a single or two records in Holiday table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/03/2013 :  05:49:04  Show Profile  Reply with Quote
Ideally this is how you do the calculation of number oof days if holiday table has 1 record per holiday


declare @daterange1 as DateTime
declare @daterange2 as DateTime
set @daterange1 = '15/Jul/2013'
set @daterange2 = '18/Jul/2013'
SELECT DATEDIFF(dd,@daterange1,@daterange2)+1 - COUNT(*)
FROM Holiday
WHERE date>= @daterange1 AND date < = @daterange2


And if Holiday table has it as a date range (HolidayStart,HolidayEnd)
do something like

declare @daterange1 as DateTime
declare @daterange2 as DateTime
set @daterange1 = '15/Jul/2013'
set @daterange2 = '23/Jul/2013'
SELECT DATEDIFF(dd,@daterange1,@daterange2)+1 - COUNT(DATEDIFF(dd,HolidayStart,HolidayEnd) +1)
FROM Holiday
WHERE ((HolidayStart>=@daterange1
AND HolidayStart < = @daterange2)
OR (HolidayEnd >=@daterange1
AND HolidayEnd <=@daterange2)) 


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000