TRY WITH THESE QUERIES..
create table #leave
(
LeaveId INT IDENTITY(1,1)
,EmployeeId INT
,FromDate Datetime
,ToDate Datetime
,[Status] VARCHAR(50)
)
insert into #leave values(1,'2011-10-25','2011-10-27','Approved')
insert into #leave values(2,'2011-10-26','2011-10-27','Approved')
insert into #leave values(3,'2011-10-27','2011-10-27','Rejected')
insert into #leave values(1,'2011-11-01','2011-10-01','Approved')
select * from #leave
IF OBJECT_ID('#sample') IS NOT NULL
DROP TABLE #sample
SELECT * INTO #sample FROM #leave WHERE 1=2
DECLARE @LeaveID int, @EmpID int
DECLARE @StDate DATETIME, @EndDate DATETIME
DECLARE @status varchar(50)
DECLARE cur CURSOR FOR SELECT * FROM #leave
OPEN cur
FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
PRINT @EndDate
WHILE(@@FETCH_STATUS = 0)
BEGIN
WHILE(@StDate <= @EndDate)
BEGIN
INSERT INTO #sample(EmployeeId,FromDate,[Status]) VALUES(@EmpID,@StDate,@status)
SET @StDate = DATEADD(DD,1,@StDate)
END
FETCH FROM cur INTO @LeaveID,@EmpID,@StDate,@EndDate,@status
END
CLOSE cur
SELECT EmployeeId,FromDate as 'Date',[Status] FROM #sample
HOPE IT WORKS....