Hi,
I have a table like below structure
declare @tbl table
(empid int,noOfDay int,leavedate datetime)
insert into @tbl values (001,2,'2016-04-22'),(002,1,'2016-01-18'),(003,3,'2005-07-14')
and data is like empid noOfDay leavedate
---------------------------------------
001 2 2016-04-22
002 1 2016-01-18
003 3 2005-07-14
i want my out put result in below format
empid noOfDay leavedate
---------------------------------------
001 1 2016-04-22
001 1 2016-04-22
002 1 2016-01-18
003 1 2005-07-14
003 1 2005-07-14
003 1 2005-07-14
Thanks in advanced.
Regards,
Manish
Dharmesh SinghPosted Apr 22, 2016, 4:43 AM
insert into @tbl values (001,2,'2016-04-22'),(002,1,'2016-01-18'),(003,3,'2005-07-14')
create table #temptable (empid int,noofday int,leavedate datetime)
declare @noofday int
declare @empid int,@count int=0
declare @date datetime
while ((select count (*) from @tbl)>0)
begin
select @empid=empid,@noofday=noOfDay,@date=leavedate from @tbl
set @count=1
while (@noofday>0)
begin
insert into #temptable(empid,noofday,leavedate)
select @empid,1, DATEADD(day,@count,@date)
set @count=@count+1
set @noofday=@noofday-1
end
delete from @tbl where empid=@empid
end
select * from #temptable
drop table #temptable
ali tuncerPosted Apr 22, 2016, 5:24 AM
another way :
;WITH temp AS
(
SELECT TOP (SELECT MAX(noOfDay)+1 FROM @tbl) rown = ROW_NUMBER()
OVER (ORDER BY [object_id])
FROM sys.all_columns
)
SELECT empid,noOfDay,leavedate FROM temp
CROSS JOIN @tbl AS d
WHERE temp.rown <= d.noOfDay
ORDER BY empid
Shakti Singh DulawatPosted Apr 22, 2016, 4:47 AM
Make a while loop till the end of the record
Count noOfDay in Each Row
Based on noOfDay INSERT THOSE record in temp table