-- ****** Modified Version ******
 
declare @StartDate datetime
declare @Days int
declare @counter int
 
SET @StartDate = dbo.Timesheets.TimeDate
 
IF dbo.timesheets.AbsenceDays = <1 THEN
end
else
 
SET @counter = 0
 
-- The reason why counter is set to 0 and not 1 is because I want to create the initial record with the Timesheet date+0.  This is because I do not want to keep the original record that is being used to duplicate and create each day timesheet.
 
while(@counter <= dbo.timesheets.AbsenceDays)
begin
 
-- Somehow need to specify a relationship between dbo.Calendar.[Date] and dbo.timesheets.DateTime+@counter
 
CASE WHEN(dbo.Calendar.WorkDay = “Y” THEN continue ELSE @counter = @counter+1 redo case END)
 
-- Above case done to ensure that the date it is about to add is not a Sunday or Saturday or public holiday.  Don’t know how to format it correctly.
 
print DATEADD(day, @counter, @StartDate)
 
-- I know I can insert record here but don’t know how.  I want the same details as the original record being checked by the script but the TimeDate field to be changed to the new date and the AbsenceDays field to be set to 1 per records.  In addition, if the absences are 3.5 days, I would need FOUR records created – 1, 1, 1, 0.5.  When adding these records, how would I get it to create records which are not simply divided by 4? (namely 0.87 days per record)[/green]
 
SET @counter = @counter + 1
end

-- This is the source Record Set.  This is saved as a view:  AbsenceFinder
 
SELECT		TimeDate, 
		YearNum, 
		MonthNum, 
		ResourceName, 
		Studio_Company, 
		ChargeDesc, 
		AbHrs, 
CASE 
	WHEN ROUND((abhrs / (Hours_pw/Days_pw)),0) < 0 
		THEN 
			0 - ROUND((abhrs / (Hours_pw/Days_pw)),0)
	WHEN ROUND((abhrs / (Hours_pw/Days_pw)),0) > 1
		THEN
			ROUND((abhrs / (Hours_pw/Days_pw)),0)
		ELSE 1
	END
AS NumRecords
FROM dbo.FM_Timesheets
WHERE ChargeCategory = 'Holidays/Absences'
 
-- ************* This is the script so far ****************
 
DECLARE 	@StartDate datetime
DECLARE 	@Days int
DECLARE 	@counter int
SET 	@StartDate = 	dbo.FM_Timesheets.TimeDate
 
	-- be careful of US dates (pass as parameters to SP) 
 
SET 	@counter = 	0
SET 	@Days  = 	dbo.FM_Timesheets.NumRecords
WHILE	(@counter <= @Days)
BEGIN
PRINT	DATEADD(day, @counter, @StartDate) – don’t know what this does?
 
-- I’m guessing here, really don’t know how to create rows from script
 
IF SELECT dbo.Calendar.workdays = ‘Y’ FROM dbo.Calendar LEFT OUTER JOIN DATEADD(day, @counter, @StartDate) = dbo.Calendar.caldate THEN
 
UPDATE AbsenceFinder.TimeDate 		= DATEADD(day, @counter, @StartDate)
UPDATE AbsenceFinder.YearNum 		= YEAR(DATEADD(day, @counter, @StartDate))
UPDATE AbsenceFinder.MonthNum 		= MONTH(DATEADD(day, @counter, @StartDate))
UPDATE AbsenceFinder.ResourceName 	= ResourceName
UPDATE AbsenceFinder.StudioCompany	= StudioCompany
UPDATE AbsenceFinder.ChargeDesc		= ChargeDesc
UPDATE AbsenceFinder.AbHrs			= (Hours_pw / Days_pw) 
 
I need TO find a way of getting it TO look at remaining amount AND, IF less than a day, enter the remaining amount.  Example, someone puts 0.5 days, that IS 3.75 hours.  Full day 7.5.  
IF the sum of this persons timesheets (have TO put an ID ON there AS well TO ensure they are groupable together) < original 4.5 days (33.75 hours) AND remaining amount IS < (Hours_pw / Days_pw) then, value = remaining amount ELSE (Hours_pw / Days_pw).
 
INTO Absences
FROM AbsenceFinder
SET 	@counter = @counter + 1
ELSE
SET 	@counter = @counter + 1
END
END
 
Examples
Original
TimeDate   YearNum   MonthNum   ResourceName   StudioCompany    ChargeDesc   AbHrs
30/01/09   2009      01         Joe Bloggs     Bristol          Absence      33.75   (4.5 days)
 
After work done
TimeDate   YearNum   MonthNum   ResourceName   StudioCompany    ChargeDesc   AbHrs
30/01/09   2009      01         Joe Bloggs     Bristol          Absence        7.5
31/01/09   2009      01         Joe Bloggs     Bristol          Absence        7.5
02/02/09   2009      02         Joe Bloggs     Bristol          Absence        7.5
03/02/09   2009      02         Joe Bloggs     Bristol          Absence        7.5
04/02/09   2009      02         Joe Bloggs     Bristol          Absence       3.75

(http://www.projectdmx.com/tsql/tblnumbers.aspx).
 
SELECT customerid,
firstname,
lastname,
SUBSTRING(phone_numbers, n, CHARINDEX(' ', phone_numbers + ' ',
n) - n) AS phone,
n + 1 - LEN(REPLACE(LEFT(phone_numbers, n), ' ', '' )) AS
phone_idx
FROM Phones AS P
CROSS JOIN (SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100) AS Numbers(n)
WHERE SUBSTRING(' ' + phone_numbers, n, 1) = ' '
AND n < LEN(phone_numbers) + 1
ORDER BY customerid, phone_idx

IF dbo.Absenceview.NumRecords > 1 
	THEN
		SET	@AbsenceHours	=	dbo.Absenceview.Hours_pw / 
							dbo.Absenseview.Days_pw)
		SET	@RecCounter	=	dbo.Absenceview.NumRecords
		SET	@AdjustDay	= 0
		SET	@DateCheck	= 'N'
 
			IF dbo.Absencerview.AbHrs / @ AbsenceHours = RecCounter
				SET @LastRec = @AbsenceHours
			ELSE
				SET @LastRec = (@RecCounter-(dbo.Absenceview.AbHrs / 
							@AbsenceHours)) * @AbsenceHours
			END IF
 
		SET	@Looper = 1
		SET	@AbDate	= dbo.Absenceview.TimeDate
 
		WHILE @Looper <= (@RecCounter-1)
 
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum, 
				RecourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
			VALUES	
				(
				@AbDate,
				MONTH(@AbDate), 
				dbo.Absenceview.ResourceName,
				dbo.Absenceview.Studio_Company,
				dbo.Absenceview.ChargeDesc,
				@AbsenceHours
				);
 
-- Check the next date to see if it is a valid WorkDay by looking up proposed date in dbo.Calendar
 
				WHILE	@DateCheck = 'N'
 
					SET	@adDate = DATEADD(day, (@Looper+@AdjustDay), 
								@adDate)
 
					SET	@DateCheck = (SELECT dbo.Calendar.WorkDay FROM 
									dbo.Calendar WHERE 
								dbo.Calendar.CalDate = @adDate)
 
					SET 	@AdjustDay = @AdjustDay + 1
 
				END WHILE
 
-- ********************************************************************************
 
		END WHILE
 
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum, 
				RecourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
			VALUES
				(
				@AbDate,
				MONTH(@AbDate), 
				dbo.Absenceview.ResourceName,
				dbo.Absenceview.Studio_Company,
				dbo.Absenceview.ChargeDesc,
				@LastRec
				);
	ELSE
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum, 
				RecourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
			VALUES
				(
				dbo.Absenceview.TimeDate,
				dbo.Absenceview.MonthNum, 
				dbo.Absenceview.ResourceName,
				dbo.Absenceview.Studio_Company,
				dbo.Absenceview.ChargeDesc,
				dbo.Absenceview.AbHrs
				);
END IF;

IF EXISTS (SELECT 1
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE='BASE TABLE'
	AND TABLE_NAME='Absences')
		DROP TABLE Absences
	ELSE
 
CREATE TABLE Absences(
	TimeDate		DateTime,
	MonthNum		varchar(2),
	YearNum			varchar(25),
	ResourceName	varchar(max),
	Studio_Company	varchar(max),
	ChargeDesc		varchar(max),
	AbHrs			varchar(max),
	);
 
-- Populate with data
 
DECLARE @AbDate			Datetime
DECLARE @AbsenceHours	Float
DECLARE @LastRec		Float
DECLARE @RecCounter		Int
DECLARE @Looper			Int
DECLARE @AdjustDay		Int
DECLARE @DateCheck		 varchar(2)
 
DECLARE @curTimeDate DateTime
DECLARE @curResourceName varchar(max)
DECLARE @curChargeDesc varchar(max)
DECLARE @curAbsenceHours real
 
/*Create cursor to look at every record in Absenceview */
DECLARE @absence_cursor CURSOR 
 
SET @absence_cursor = CURSOR FOR 
SELECT TimeDate,ResourceName,ChargeDesc,AbHrs FROM dbo.Absenceview
 
OPEN @absence_cursor
 
FETCH NEXT FROM @absence_cursor
INTO @curTimeDate,@curResourceName,@curChargeDesc,@curAbsenceHours 
 
WHILE @@FETCH_STATUS = 0
BEGIN 
IF ((SELECT NumRecords 
	FROM dbo.Absenceview 
	WHERE TimeDate = @curTimeDate AND 
		ResourceName = @curResourceName AND
		ChargeDesc = @curChargeDesc AND
		AbHrs = @curAbsenceHours)  > 1) 
	BEGIN
		SET	@AbsenceHours	=((	SELECT	Hours_pw 
								FROM	dbo.Absenceview 
								WHERE	TimeDate = @curTimeDate AND 
										ResourceName = @curResourceName AND
										ChargeDesc = @curChargeDesc AND
										AbHrs = @curAbsenceHours) 
										/ 
							(	SELECT	Days_pw 
								FROM	dbo.Absenceview 
								WHERE	TimeDate = @curTimeDate AND 
										ResourceName = @curResourceName AND
										ChargeDesc = @curChargeDesc AND
										AbHrs = @curAbsenceHours))
 
		SET	@RecCounter	=	(	SELECT NumRecords FROM	dbo.Absenceview 
								WHERE	TimeDate = @curTimeDate AND 
										ResourceName = @curResourceName AND
										ChargeDesc = @curChargeDesc AND
										AbHrs = @curAbsenceHours)
		SET	@AdjustDay	= 0
		SET	@DateCheck	= 'N'
--
-- This is to check if the hours of LAST timesheet is <= full day hours.
-- @LastRec will either be the same as @AbsenceHours or less.
--
			IF ((SELECT AbHrs FROM dbo.Absenceview WHERE TimeDate = @curTimeDate AND 
				ResourceName = @curResourceName AND
				ChargeDesc = @curChargeDesc AND
				AbHrs = @curAbsenceHours ) / @AbsenceHours = @RecCounter) 
				BEGIN
					SET @LastRec = @AbsenceHours
				END	
				ELSE
				BEGIN
					SET @LastRec = (@RecCounter-(	SELECT AbHrs 
													FROM	dbo.Absenceview 
													WHERE	TimeDate = @curTimeDate AND 
										ResourceName = @curResourceName AND
										ChargeDesc = @curChargeDesc AND
										AbHrs = @curAbsenceHours)/ @AbsenceHours) * @AbsenceHours
				END
--
-- Set the first Date.  This will be the date the timesheet was entered
--
		SET	@Looper = 0
		SET	@AbDate	= (	SELECT TimeDate 
						FROM	dbo.Absenceview 
						WHERE	TimeDate = @curTimeDate AND 
								ResourceName = @curResourceName AND
								ChargeDesc = @curChargeDesc AND
								AbHrs = @curAbsenceHours)+ (@Looper + @AdjustDay)
--
-- This is to create additional required records but also, check the proposed date
-- against the dbo.calendar and increment the date, if necessary, to ensure the new
-- record contains the date of a valid working day.
--
		WHILE @Looper <= (@RecCounter-1)
 
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(SELECT
				@AbDate,
				MONTH(@AbDate), 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				@AbsenceHours
				FROM dbo.Absenceview
				WHERE TimeDate = @curTimeDate AND 
				ResourceName = @curResourceName AND
				ChargeDesc = @curChargeDesc AND
				AbHrs = @curAbsenceHours );
 
-- Check the next date to see if it is a valid WorkDay by looking up proposed date in dbo.Calendar
-- Confused as to the order of these events.  I want it to stop incrementing @AdjustDay if the
-- @DateCheck = 'Y' so that it can be used.
 
				WHILE @DateCheck = 'N'
				BEGIN 
					SET @AdjustDay = @AdjustDay + 1
 
					SET	@abDate = @abDate + (@Looper+@AdjustDay)
 
					SET	@DateCheck = (SELECT dbo.Calendar.WorkingDay FROM dbo.Calendar WHERE 
						dbo.Calendar.CalDate = @abDate)
				END 
 
--
-- Once the first sets of Absence records have been created with the value of @AbsenceHouse,
-- the last timesheet to create will be created with the value of @LastRec
---
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(
				SELECT 
				@AbDate,
				MONTH(@AbDate), 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				@LastRec
				FROM dbo.Absenceview
				WHERE TimeDate = @curTimeDate AND 
				ResourceName = @curResourceName AND
				ChargeDesc = @curChargeDesc AND
				AbHrs = @curAbsenceHours );
	END
	ELSE
	BEGIN
--
-- This is what happens when there is only one Absence timesheet.  It is to create a single
-- absence record as it is in the Absenceview without additional records created.
--
		INSERT INTO dbo.Absences
				(
				TimeDate, 
				MonthNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(
				SELECT
				TimeDate,
				MonthNum, 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				AbHrs
				FROM dbo.Absenceview 
				WHERE TimeDate = @curTimeDate AND 
				ResourceName = @curResourceName AND
				ChargeDesc = @curChargeDesc AND
				AbHrs = @curAbsenceHours );
END
 
FETCH NEXT FROM @absence_cursor
INTO @curTimeDate,@curResourceName,@curChargeDesc,@curAbsenceHours 
 
END
 
CLOSE @absence_cursor
 
DEALLOCATE @absence_cursor