Calculating days from a holiday table

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

Calculating days from a holiday table

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-12-07 : 18:53:52

I have a vacation application that allows users to book their vacation time into upcoming years. The application also allows for admins to enter stat holidays. When a new year rolls around and peoples vacation time is reset, I want to take all vacation entries that exists in the new year and deduct the # of hours from their remaining vacation time.

The query is almost complete, except I am stuck trying to add time back in when a future vacation overlaps with a stat holiday.

Here are some sample tables and code:


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(1,'01-09-2012', '01-10-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(1,'01-19-2012', '01-20-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(2,'02-14-2012', '02-15-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(2,'03-06-2012', '03-07-2012')

DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

;WITH TEMP AS
(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) 
				-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@AbsenceHistory AH RIGHT JOIN @Employee E ON AH.EmployeeID = E.ID 
WHERE 
	E.ID IN ('1','2') AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
)
UPDATE V SET
	V.RemainingVacation = V.RemainingVacation - FutureEntries.FutureVacay --+(Stat holidays that overlap with entries in @AbsenceHistory)
	FROM
		@Vacation V INNER JOIN 
			(SELECT
				E.ID, 
				SUM(CASE WHEN T.StartDate >= @StartDate AND T.EndDate <= @EndDate THEN 
						T.FutureVacationEntries ELSE 0 END) AS FutureVacay
			FROM 
				TEMP T RIGHT JOIN @Employee E ON T.EmployeeID = E.ID
			WHERE 
				T.EmployeeID IN ('1','2')
			GROUP BY E.ID) AS FutureEntries ON V.EmployeeID = FutureEntries.ID
			
SELECT * FROM @Vacation

So in this example Employee 1, Bill, has 2 days booked in 2012;
01-09-2012 to 01-10-2012 and 01-19-2012 to 01-20-2012

There is a StatHoliday (01-10-2012) that overlaps with Bills first vacation entry. I would like to add that time back into his remaining vacation on the update, but I don't know how to accomplish that.

Something like:


UPDATE V SET
	V.RemainingVacation = V.RemainingVacation - FutureEntries.FutureVacay -- + (Stat holidays that overlap with entries in @AbsenceHistory)

Any suggestions on how to do this?

Thanks in advance!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 01:01:02

[code]
DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(1,'01-09-2012', '01-10-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(1,'01-19-2012', '01-20-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(2,'02-14-2012', '02-15-2012')
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate) VALUES(2,'03-06-2012', '03-07-2012')

DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

;WITH TEMP AS
(SELECT
AH.EmployeeID,
AH.StartDate,
AH.EndDate,
ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2)
-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8)
END),0) AS FutureVacationEntries
FROM
@Employee E
LEFT JOIN @AbsenceHistory AH
ON AH.EmployeeID = E.ID
AND
AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
OUTER APPLY (SELECT COUNT(*) AS SHCnt FROM @StatHoliday WHERE
StatDate BETWEEN AH.StartDate AND AH.EndDate) sh

WHERE
E.ID IN ('1','2')

)
UPDATE V SET
V.RemainingVacation = V.RemainingVacation - FutureEntries.FutureVacay --+(Stat holidays that overlap with entries in @AbsenceHistory)
FROM
@Vacation V INNER JOIN
(SELECT
E.ID,
SUM(CASE WHEN T.StartDate >= @StartDate AND T.EndDate <= @EndDate THEN
T.FutureVacationEntries ELSE 0 END) AS FutureVacay
FROM
TEMP T RIGHT JOIN @Employee E ON T.EmployeeID = E.ID
WHERE
T.EmployeeID IN ('1','2')
GROUP BY E.ID) AS FutureEntries ON V.EmployeeID = FutureEntries.ID

SELECT * FROM @Vacation

output
-------------------------------------------
ID EmployeeId RemainingVacation
1 1 96.00
2 2 96.00

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-12-09 : 04:49:09

Thanks visakh16!

A couple of requirements were added. The AbsenceHistory table needed a 'SpansWeekend' column added. Not everyone works mon-fri. So if there is a stat holiday that lands on a weekend and a user was working that weekend that needs to be taken into account.

The outer apply needs some logic depending on whether the user works weekends and whether a stat lands on a weekend.

I am trying to use a case, but not having any luck:


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 1)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 1)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'02-14-2012', '02-15-2012', 1)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 1)

DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) 
				-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(CASE WHEN AH.SpansWeekend = 1 THEN
		(SELECT COUNT(*) AS SHCnt FROM HRIS_StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate)
		ELSE
			(SELECT COUNT(*) AS SHCnt FROM HRIS_StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate AND
			DATENAME(dw, StatDate) <> 'Saturday' AND DATENAME(dw, StatDate) <> 'Sunday')
		END) SH WHERE 
	E.ID IN ('1','2') 
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 05:05:28

you cant use CASE WHEN like this. can you give some sample data for new scenario and explain what you want as output just as you did for original post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-12-09 : 11:41:42

Thanks for your help visakh16, it's much appreciated.

Here is some new sample data:


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-01-2012', '01-02-2012', 1) --Stat overlaps, spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 0) --Normal Entry
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 0) --Stat overlaps spans = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'01-13-2012', '01-16-2012', 1) --spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 0) --stat overlaps


DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-01-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) 
				-(CASE WHEN DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(SELECT COUNT(*) AS SHCnt FROM @StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate) SH
WHERE 
	E.ID IN ('1','2') 
)

The results should be:


EmployeeId   StartDate     EndDate     FutureVacationEntries
    1        2012-01-01   2012-01-02            8 
    1        2012-01-19   2012-01-20            16
    1        2012-01-09   2012-01-10            8
    2        2012-01-13   2012-01-16            32
    2        2012-03-06   2012-03-07            8

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 12:36:41

quote:

Originally posted by rypi

Thanks for your help visakh16, it's much appreciated.

Here is some new sample data:


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-01-2012', '01-02-2012', 1) --Stat overlaps, spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 0) --Normal Entry
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 0) --Stat overlaps spans = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'01-13-2012', '01-16-2012', 1) --spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 0) --stat overlaps


DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-01-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(SELECT COUNT(*) AS SHCnt FROM @StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate) SH
WHERE 
	E.ID IN ('1','2') 
)

The results should be:


EmployeeId   StartDate     EndDate     FutureVacationEntries
    1        2012-01-01   2012-01-02            8 
    1        2012-01-19   2012-01-20            16
    1        2012-01-09   2012-01-10            8
    2        2012-01-13   2012-01-16            32
    2        2012-03-06   2012-03-07            8

Thanks again!

Tweak as above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-12-09 : 14:09:50

That's not taking the spansweekend into account properly.
If I change the spansweekend flag on one of the entries below (in red) the results should change from 32 to 16.
The spansweekend flag may or may not be set, depending on the person using the system.


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-01-2012', '01-02-2012', 1) --Stat overlaps, spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 0) --Normal Entry
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 0) --Stat overlaps spans = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'01-13-2012', '01-16-2012', 0) --spansweekend = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 0) --stat overlaps


DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [date] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-01-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate date, @EndDate date
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-(DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(SELECT COUNT(*) AS SHCnt FROM @StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate) SH
WHERE 
	E.ID IN ('1','2') 
)



The results should be:


EmployeeId   StartDate     EndDate     FutureVacationEntries
    1        2012-01-01   2012-01-02            8 
    1        2012-01-19   2012-01-20            16
    1        2012-01-09   2012-01-10            8
    2        2012-01-13   2012-01-16            16 -- should be 16 if spansweekend = false
    2        2012-03-06   2012-03-07            8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-10 : 07:48:25

do this small tweak


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-01-2012', '01-02-2012', 1) --Stat overlaps, spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 0) --Normal Entry
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 0) --Stat overlaps spans = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'01-13-2012', '01-16-2012', 0) --spansweekend = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 0) --stat overlaps


DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [datetime] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-01-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-10-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-CASE WHEN SpansWeekend=0 THEN (DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) ELSE 0 END
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(SELECT COUNT(*) AS SHCnt FROM @StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate) SH
WHERE 
	E.ID IN ('1','2') 
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-12-12 : 13:51:25

Thanks for your help visakh16!

There's still an issue though.
I highlighted a few changes below to show the issue.

In this case a users vacation is from 01-13-2012 to 01-16-2012 and it doesn't span a weekend. There is a stat holiday on '01-14-2012'.
The query only calculates 8 hours for this entry, when it should be 16.


DECLARE @Employee TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL)
INSERT INTO @Employee(Name) VALUES('Bill')
INSERT INTO @Employee(Name) VALUES('Steve')

DECLARE @AbsenceHistory TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[SpansWeekend] [bit] NULL)
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-01-2012', '01-02-2012', 0) --Stat overlaps, spansweekend = true
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-19-2012', '01-20-2012', 0) --Normal Entry
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(1,'01-09-2012', '01-10-2012', 0) --Stat overlaps spans = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'01-13-2012', '01-16-2012', 0) --spansweekend = false
INSERT INTO @AbsenceHistory (EmployeeId, StartDate, EndDate, SpansWeekend) VALUES(2,'03-06-2012', '03-07-2012', 0) --stat overlaps


DECLARE @StatHoliday TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StatDate] [datetime] NOT NULL,
[Title] [varchar](150) NOT NULL)
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-01-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('01-14-2012', 'TestStat');
INSERT INTO @StatHoliday (StatDate, Title) VALUES ('03-07-2012', 'TestStat');

DECLARE @Vacation TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NOT NULL,
[RemainingVacation] [decimal] (6,2) NOT NULL)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(1, 120)
INSERT INTO @Vacation (EmployeeId, RemainingVacation) VALUES(2, 120)

DECLARE @StartDate datetime, @EndDate datetime
SET @StartDate = '01-01-2012'
SET @EndDate = '12-31-2012';

(SELECT 
	AH.EmployeeID,
	AH.StartDate,
	AH.EndDate,
	ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
				(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
				-CASE WHEN SpansWeekend=0 THEN (DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) ELSE 0 END
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END) 
				-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8) 
			END),0) AS FutureVacationEntries
FROM 
	@Employee E 
	LEFT JOIN @AbsenceHistory AH 
	ON AH.EmployeeID = E.ID 
	AND
	AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
	OUTER APPLY
	(SELECT COUNT(*) AS SHCnt FROM @StatHoliday 
			WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate) SH
WHERE 
	E.ID IN ('1','2') 
)


EmployeeID	StartDate	EndDate	FutureVacationEntries
1	2012-01-01 00:00:00.000	2012-01-02 00:00:00.000	0
1	2012-01-19 00:00:00.000	2012-01-20 00:00:00.000	16
1	2012-01-09 00:00:00.000	2012-01-10 00:00:00.000	16
2	2012-01-13 00:00:00.000	2012-01-16 00:00:00.000	8 --Should be 16
2	2012-03-06 00:00:00.000	2012-03-07 00:00:00.000	8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:07:20

[code]

(SELECT
AH.EmployeeID,
AH.StartDate,
AH.EndDate,
ISNULL((CASE WHEN AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate THEN
(((DATEDIFF(dd, AH.StartDate, AH.EndDate) + 1)
-CASE WHEN SpansWeekend=0 THEN (DATEDIFF(wk, AH.StartDate, AH.EndDate) * 2) ELSE 0 END
-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN SpansWeekend=0 AND DATENAME(dw, AH.EndDate) = 'Saturday' THEN 1 ELSE 0 END)-SHCnt) * 8)
END),0) AS FutureVacationEntries
FROM
@Employee E
LEFT JOIN @AbsenceHistory AH
ON AH.EmployeeID = E.ID
AND
AH.StartDate >= @StartDate AND AH.EndDate <= @EndDate
OUTER APPLY
(SELECT COUNT(*) AS SHCnt FROM @StatHoliday
WHERE StatDate BETWEEN AH.StartDate AND AH.EndDate
AND (DATENAME(dw, StatDate) NOT IN ('Saturday','Sunday') OR AH.SpansWeekend=1)) SH
WHERE
E.ID IN ('1','2')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources