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