Get 2 Day - AM, PM & FullDay exclude holiday&WKend

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 2008 Forums

Transact-SQL (2008)

Get 2 Day - AM, PM & FullDay exclude holiday&WKend

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-07-03 : 03:08:42

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

52326 Posts

Posted - 2013-07-03 : 03:47:50

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

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-07-03 : 04:27:39

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 04:39:48

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

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-07-03 : 05:33:50

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 05:42:22

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-03 : 05:49:04

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources