Finding the beginning and ending date

[unleashedmaniac]

Our employees have an interface to enter their vacation days. The vacation dates are stored in the database as one row per day, minus the weekend days. I'm trying to create a report that will show the starting and ending dates for each employee. Note: Each employee can have muliple vacation periods and the data in the database only records Monday - Friday.

Database sample data:

VacationDays table (contains EmployeeID and their DayOff)
[U]EID  DayOff    .[/U]
1    05/03/2010
1    05/04/2010
1    05/05/2010
1    05/06/2010
1    05/07/2010
1    05/10/2010
1    05/24/2010
1    05/25/2010
1    05/26/2010
2    05/21/2010
2    05/24/2010
2    05/25/2010
2    05/26/2010
3    05/12/2010
3    05/13/2010
4    05/27/2010
4    05/28/2010
4    05/31/2010
4    06/01/2010
4    06/02/2010

Desired output:

[U]Employee  VacationPeriod         .[/U]
John      05/03/2010 - 05/10/2010
John      05/24/2010 - 05/26/2010
Jane      05/21/2010 - 05/26/2010
Matt      05/12/2010 - 05/13/2010
Carl      05/27/2010 - 06/02/2010

I'm sure I'm going to have to loop through the records for each employee and find the start date and the end date by looking for a break in days that are not Saturday or Sunday.

[WebspoonUK]

Performing a query to achieve your desired resultset above would mean you would have to add min(DayOff), max(DayOff) and group by EID but this would not cater for multiple vacation bookings by the same employee.

The best way to achieve this would be to generate a vacation id in another table and add this into the VacationDays table. This would allow you to the group by the EID AND the vacation id to achieve your desired resultset.

Hope this helps

[unleashedmaniac]

Performing a query to achieve your desired resultset above would mean you would have to add min(DayOff), max(DayOff) and group by EID but this would not cater for multiple vacation bookings by the same employee.

The best way to achieve this would be to generate a vacation id in another table and add this into the VacationDays table. This would allow you to the group by the EID AND the vacation id to achieve your desired resultset.

Hope this helps

I wish it was that easy, but the application doesn't allow employees to create a vacation event and then add dates to it. The employee is prompted with a year calendar grid with a check box for each day they are going to be out of the office. Each check box correlates to a date row in the database. Employees can go into the application and submit their days off all at one time. [See attached calendar image.]

I think that I'm going to have create a temp table that creates a temporary VacationID. To figure out the VacationID, I'm going to have to loop through each employee's dates, find the first date and then look for a break in days by comparing rows in the database using DateDiff and looking for breaks of more than one day (Saturday and Sunday excluded).

The problem is that I'm a relative noob in SQL and I just can't figure out the complex WHILE or WHILE within a WHILE and the calculations.

Thanks... James

[WebspoonUK]

Try this...

declare @EmployeeId int, @LastEmployeeId int
declare @DayOff datetime, @LastDayOff datetime
declare @Counter int, @VacationId int

create table #output(VacationId int identity(1, 1) NOT NULL, EmployeeId int NOT NULL, StartDate datetime NULL, EndDate datetime NULL)

SET NOCOUNT ON

declare vacationDays CURSOR FAST_FORWARD FOR
select distinct EID, DayOff from VacationDays order by EID, DayOff

select top 1 @LastEmployeeId = EID FROM VacationDays order by EID, DayOff
select top 1 @LastDayOff = DayOff FROM VacationDays order by EID, DayOff
SET @Counter = 0

print @LastEmployeeId
print @LastDayOff

OPEN vacationDays
FETCH NEXT FROM vacationDays INTO @EmployeeId, @DayOff

WHILE @@FETCH_STATUS = 0
  BEGIN
	SET @Counter = @Counter + 1

	If @Counter = 1
	  BEGIN
		insert into #output (EmployeeId, StartDate, EndDate)
		values (@EmployeeId, @DayOff, NULL)
		SET @VacationId = scope_identity()		
	  END
	Else
	  BEGIN
		If @LastEmployeeId <> @EmployeeId
		  BEGIN
		    UPDATE #output set EndDate = @LastDayOff WHERE VacationId = @VacationId
			insert into #output (EmployeeId, StartDate, EndDate)
			values (@EmployeeId, @DayOff, NULL)
			SET @VacationId = scope_identity()
		  END
		ELSE
		  IF Datediff(d, @LastDayoff, @DayOff) > 2 and datepart(weekday, @LastDayOff) < 5
			BEGIN
			  print '@LastDayoff = ' + convert(varchar, @LastDayoff)
			  print '@Dayoff = ' + convert(varchar, @Dayoff)
			  UPDATE #output set EndDate = @LastDayOff WHERE VacationId = @VacationId
			  insert into #output (EmployeeId, StartDate, EndDate)
			  values (@EmployeeId, @DayOff, NULL)
			  SET @VacationId = scope_identity()
			END
	  END
	
	SET @LastEmployeeId = @EmployeeId
	SET @LastDayOff = @DayOff

	FETCH NEXT FROM vacationDays INTO @EmployeeId, @DayOff
  END

UPDATE #output set EndDate = @LastDayOff WHERE VacationId = @VacationId

CLOSE vacationDays
DEALLOCATE vacationDays

select e.Employee, convert(varchar, o.StartDate, 103) + ' - ' + convert(varchar, o.EndDate, 103) as VacationPeriod
from #output o
inner join Employee e on e.eid = o.EmployeeId

drop table #output