-
Copy Record and duplicate to a new table
Hi I'mk new to the site and hope you can help...
I have inherited a databse, and its a mess.
I have one main problem that I have been unable to resolve.
I have 1 table with records in for sickness
ie
IdNo / Name / DateFrom / DateTo / NoOfDaysSick
02119 / Anyone / 01/04/08 / 05/04/08 / 5
What I need to do is to copy this data to another table but put one entry in for each day sick
ie
IdNo / Name / Date
02119 / Anyone / 01/04/08
02119 / Anyone / 02/04/08
02119 / Anyone / 03/04/08
02119 / Anyone / 04/04/08
02119 / Anyone / 05/04/08
I'm new to SQl so any help would be very greatful
-
Resolved this issue
I have managed to resolve this issue... thanks any way
declare @counter int
declare @SickRecIDNo int
declare @DateFrom datetime
declare @DateTo datetime
declare @WorkingDaysOff int
declare @Diagnosis nvarchar(100)
declare @DatesonCert nvarchar(50)
declare @LastDayShift bit
declare @Weekend bit
declare @EmployeeIDNo int
declare @SickCodeIDNo int
declare @SickRecordComment varchar(8000)
declare @TypeCertIDNo int
declare @LTSCert bit
declare @EOYrCodingIDNo int
declare @HoursLost decimal(18, 2)
declare @Accident bit
declare @TotalLTSDays int
-- Declare the cursor
declare mycursor cursor for
select * from [Sickness Records]
-- Open the cursor
open mycursor
fetch next from mycursor
into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Di agnosis,@DatesonCert,@LastDayShift,@Weekend,@Emplo yeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCert IDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident ,@TotalLTSDays
-- begin statements and select
while @@FETCH_STATUS = 0
begin
set @counter=1
while @counter<=@WorkingDaysOff
begin
insert SicknessRecords_Advanced
select @SickRecIDNo,dateadd(day,@counter,@DateFrom),@Date To,@WorkingDaysOff,@Diagnosis,@DatesonCert,@LastDa yShift,@Weekend,@EmployeeIDNo,@SickCodeIDNo,@SickR ecordComment,@TypeCertIDNo,@LTSCert,@EOYrCodingIDN o,@HoursLost,@Accident,@TotalLTSDays
set @counter=@counter+1
end
fetch next from mycursor
into @SickRecIDNo,@DateFrom,@DateTo,@WorkingDaysOff,@Di agnosis,@DatesonCert,@LastDayShift,@Weekend,@Emplo yeeIDNo,@SickCodeIDNo,@SickRecordComment,@TypeCert IDNo,@LTSCert,@EOYrCodingIDNo,@HoursLost,@Accident ,@TotalLTSDays
end
-- close the cursor and deallocate
close mycursor
deallocate mycursor
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|