-- ****** 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;