--DROP TABLE dbo.Absences

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(4),
	ResourceName	varchar(max),
	Studio_Company	varchar(max),
	ChargeDesc		varchar(max),
	AbHrs			float,
	);

-- 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
DECLARE @Time_ID			int

/*Create cursor to look at every record in Absenceview */
DECLARE @absence_cursor CURSOR 

SET		@absence_cursor = CURSOR FOR 
SELECT	TimeDate,
		ResourceName,
		ChargeDesc,
		AbHrs,
		TS_ID 
FROM	dbo.Absenceview

OPEN	@absence_cursor

FETCH NEXT	FROM	@absence_cursor
			INTO	@curTimeDate,
					@curResourceName,
					@curChargeDesc,
					@curAbsenceHours,
					@Time_ID 

WHILE @@FETCH_STATUS = 0
BEGIN 
IF ((SELECT NumRecords 
	FROM	dbo.Absenceview 
	WHERE	TimeDate =		@curTimeDate		AND 
			ResourceName =	@curResourceName	AND
			ChargeDesc =	@curChargeDesc		AND
			AbHrs =			@curAbsenceHours	AND
			TS_ID =			@Time_ID)  
		=	1) 
	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 Absences
				(
				TimeDate, 
				MonthNum,
				YearNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(
				SELECT
				TimeDate,
				MonthNum,
				YearNum, 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				AbHrs
				FROM	dbo.Absenceview 
				WHERE	TimeDate =		@curTimeDate		AND 
						ResourceName =	@curResourceName	AND
						ChargeDesc =	@curChargeDesc		AND
						AbHrs =			@curAbsenceHours	AND
						TS_ID =			@Time_ID );
END
	ELSE
	BEGIN
--
-- For multi-record creation	
--	
		SET	@AbsenceHours	=((	SELECT	Hours_pw 
								FROM	dbo.Absenceview 
								WHERE	TimeDate =		@curTimeDate		AND 
										ResourceName =	@curResourceName	AND
										ChargeDesc =	@curChargeDesc		AND
										AbHrs =			@curAbsenceHours	AND
										TS_ID =			@Time_ID) 
										/ 
							(	SELECT	Days_pw 
								FROM	dbo.Absenceview 
								WHERE	TimeDate =		@curTimeDate		AND 
										ResourceName =	@curResourceName	AND
										ChargeDesc =	@curChargeDesc		AND
										AbHrs =			@curAbsenceHours	AND
										TS_ID =			@Time_ID))
										
		SET	@RecCounter	=	(	SELECT	NumRecords 
								FROM	dbo.Absenceview 
								WHERE	TimeDate =		@curTimeDate		AND 
										ResourceName =	@curResourceName	AND
										ChargeDesc =	@curChargeDesc		AND
										AbHrs =			@curAbsenceHours	AND
										TS_ID =			@Time_ID)
		SET	@AdjustDay	= 0
		SET	@DateCheck	= 'N'


--			Change value of @AbsenceHours if dbo.Absenceview.AbHrs is negative
		
			IF (	SELECT AbHrs 
					FROM	dbo.Absenceview 
					WHERE	TimeDate =		@curTimeDate		AND 
							ResourceName =	@curResourceName	AND
							ChargeDesc =	@curChargeDesc		AND
							AbHrs =			@curAbsenceHours	AND
							TS_ID =			@Time_ID ) 
						<	0
					BEGIN
						SET	@AbsenceHours =	0 - @AbsenceHours
			END
--
-- 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 exact match found, set @LastRec same as @AbsenceHours
				
			IF ((	SELECT AbHrs 
					FROM	dbo.Absenceview 
					WHERE	TimeDate =		@curTimeDate		AND 
							ResourceName =	@curResourceName	AND
							ChargeDesc =	@curChargeDesc		AND
							AbHrs =			@curAbsenceHours	AND
							TS_ID =			@Time_ID ) 
						/	@AbsenceHours = @RecCounter) 
					BEGIN
						SET	@LastRec =		@AbsenceHours
			END	
				ELSE
				BEGIN
				
				-- If fraction match found, set @LastRec as remainder of @AbsenceHours
					SET	@LastRec =		(@RecCounter -	((
								SELECT	AbHrs 
								FROM	dbo.Absenceview 
								WHERE	TimeDate =		@curTimeDate		AND 
										ResourceName =	@curResourceName	AND
										ChargeDesc =	@curChargeDesc		AND
										AbHrs =			@curAbsenceHours	AND
										TS_ID =			@Time_ID))
									/	@AbsenceHours)
									*	@AbsenceHours
			END
--
-- Set the first Date.  This will be the date the timesheet was entered
--
		SET	@Looper = 1
		SET	@AbDate	= (	SELECT TimeDate 
						FROM	dbo.Absenceview 
						WHERE	TimeDate =		@curTimeDate		AND 
								ResourceName =	@curResourceName	AND
								ChargeDesc =	@curChargeDesc		AND
								AbHrs =			@curAbsenceHours	AND
								TS_ID =			@Time_ID)
--
-- 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)
		BEGIN
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum,
				YearNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(SELECT
				@AbDate,
				MONTH(@AbDate),
				YEAR (@AbDate), 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				@AbsenceHours
				FROM	dbo.Absenceview
				WHERE	TimeDate =		@curTimeDate		AND 
						ResourceName =	@curResourceName	AND
						ChargeDesc =	@curChargeDesc		AND
						AbHrs =			@curAbsenceHours	AND
						TS_ID =			@Time_ID );

-- Check the next date to see if it is a valid WorkDay by looking up proposed date in dbo.Calendar
--
					SET	@abDate = (	SELECT TOP	(1)caldate
										FROM	dbo.Calendar
										WHERE	caldate > @abDate 
									AND			workingday = 'Y')

					SET		@Looper		= @Looper + 1
		END
--
-- Once the first sets of Absence records have been created with the value of @AbsenceHours,
-- the last timesheet to create will be created with the value of @LastRec
---
			INSERT INTO Absences
				(
				TimeDate, 
				MonthNum,
				YearNum, 
				ResourceName, 
				Studio_Company, 
				ChargeDesc, 
				AbHrs
				)
				(
				SELECT 
				@AbDate,
				MONTH(@AbDate),
				YEAR(@AbDate), 
				ResourceName,
				Studio_Company,
				ChargeDesc,
				@LastRec
				FROM	dbo.Absenceview
				WHERE	TimeDate =		@curTimeDate		AND 
						ResourceName =	@curResourceName	AND
						ChargeDesc =	@curChargeDesc		AND
						AbHrs =			@curAbsenceHours	AND
						TS_ID =			@Time_ID )
END
FETCH NEXT	FROM	@absence_cursor
			INTO	@curTimeDate,
					@curResourceName,
					@curChargeDesc,
					@curAbsenceHours,
					@Time_ID 
END

CLOSE		@absence_cursor

DEALLOCATE	@absence_cursor 

quote:

---

Originally posted by mattlightbourn

Hi all,

Does anyone have any script that they would run which creates a set of records in a table based upon the contents of another table on a line by line basis? Still need this issue to be resolved and have no hope of doing it by myself, I'm a bit lost in it.

Thanks

---