create multiple rows based on data

[mattlightbourn]

Dear all,

I’m stuck on a problem which I’m hoping someone might be able to help with in an SQL script.

I have a table with RAW Timesheet data which includes entries for Absences. When someone enters a timesheet on a particular date for a two week holiday, if I report on the amount of hours recorded in the first week, it brings into account the next week’s absences as well.
My solution is that, when the data is imported from the Timesheet system into the SQL database, I want it to identify Absence records greater than one day and then create multiple records for each day taken. This way, I will always get accurate reports regardless of the date range of a report.
I am not great at scripting and am a little out of my depth but thought I would include where I have got up to (stuck though it be) to see if someone can see how to do this better or correct my mistakes.

The Timesheet record comes from a Timesheet table which is imported from a TXT file. The TimeDate field is the date the timesheet is entered OR the date for the first day’s holiday. The AbsenceDays field is how many days.

What I want to do is create a new record for every single day BUT, as it is creating each record, check the date against a Calendar table which checks to see if the proposed new record date is a WorkDay or not. The calendar has a [Date] field and a WorkDay Y/N field which is set to No if it is a weekend or is a Public Holiday.

Each Record it creates INTO a new table called Absences (which I haven’t included – but know needs to go after the print DATEADD but don’t know what to add) and the TimeDate is modified to the date created from the @counter increment and each AbsenceDays is set to 1 with exception to any remainder on the last record (like in the example of 3.5 days – records for 1, 1, 1, 0.5 would be created).

Sorry if this all looks convoluted but I wanted to ensure I explained myself fully before posting. Thank you all in advance.

Kind Regards

Matt

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

[mattlightbourn]

I've done a little more work on this and explained a few things better. I also have provided the QUERY statement where it receives it data from.

Hope someone can help with this or point me in the right direction - it's the commands I don't know, must be a way.

Kind regards Matt

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

[mattlightbourn]

Is there a way that I could modify this for my use? Instead of delimited, I want to do it WHERE NumRecords > 1. I still need it to verify the date that it increments to from a join to the dbo.calendar.caldate looking to ensure dbo.calendar.workdays = 'Y' before proceeding.

Thanks for your help with this

Matt

(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

[mattlightbourn]

Is this looking any better? Really need help with this. Thanks

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;

[mattlightbourn]

Ok, looks like I've done it using a cursor routine but, omg, crazy slow!!! Any ideas of improvements on this?

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