SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 A script to generate multiple records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mattlightbourn
Starting Member

United Kingdom
37 Posts

Posted - 11/11/2009 :  05:36:49  Show Profile  Reply with Quote
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
Starting Member

United Kingdom
37 Posts

Posted - 11/11/2009 :  12:05:57  Show Profile  Reply with Quote
A little more information for you. I've messed around with the code and included the query which gives me the set that I am using as source. If anyone has any ideas, I would be greatful. Thanks

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


Go to Top of Page

mattlightbourn
Starting Member

United Kingdom
37 Posts

Posted - 11/12/2009 :  04:48:19  Show Profile  Reply with Quote
I saw someone has written a post where they wanted to spilt a column into multiple records where a column value was delimited by a ' '.

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
Go to Top of Page

mattlightbourn
Starting Member

United Kingdom
37 Posts

Posted - 11/12/2009 :  06:47:17  Show Profile  Reply with Quote
Is this getting any closer? Really need some help on 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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000