STACK, Sql Server - rextester

STACK

CREATE TABLE [Leave Request Detail] (Req_Detail_Id varchar(100) ,[start_date] DATE, end_date DATE   , canceled varchar(5), Leave_Req_Id varchar(100))
INSERT INTO [Leave Request Detail]
SELECT 'det10001'  , '2013-01-05', '2013-01-05' ,'no' ,  'lvl10001' UNION All
SELECT 'det10002'  , '2013-01-06', '2013-01-06' ,'no' ,  'lvl10001' UNION All
SELECT 'det10003'  , '2013-01-07', '2013-01-07' ,'yes',  'lvl10001' UNION All
SELECT 'det10004'  , '2013-01-08', '2013-01-08' ,'no' ,  'lvl10001'

CREATE table #result (Report_id VARCHAR(50), [start_date] DATE, [end_date] DATE)
DECLARE
    @st VARCHAR(20),
    @en DATE,
    @can VARCHAR(5),
    @id_re VARCHAR(20),
    @lenght INT ,
    @id VARCHAR(20)

DECLARE find_records CURSOR FOR 
SELECT 
    start_date,
    end_date ,
    canceled,
    Leave_Req_Id
FROM [Leave Request Detail]
OPEN find_records
FETCH NEXT FROM find_records INTO @st, @en, @can, @id_re
WHILE @@fetch_status <> -1
BEGIN

SET @lenght = (LEN((SELECT MAX(Report_id) FROM #result))-3)
SET @id = RIGHT((SELECT MAX(Report_id) FROM #result), @lenght)

IF @can ='yes'
        BEGIN       
            INSERT INTO #result
            (
               Report_id, 
               start_date, 
               end_date
             )
             VALUES
             (
                CASE WHEN @lenght IS NULL THEN 'rep10001'
                     ELSE 'rep'+ CAST((CAST(@id AS INT) + 1) AS VARCHAR(10)) END, 
                (SELECT MIN(start_date) FROM [Leave Request Detail] WHERE start_date <=@st and canceled='no' and Leave_Req_Id=@id_re), 
                @en
            )
        END

FETCH NEXT FROM find_records INTO @st, @en, @can, @id_re
    END
    CLOSE find_records
    DEALLOCATE find_records

DECLARE missing_resords CURSOR FOR 
    SELECT 
        start_date,
        end_date ,
        canceled,
        Leave_Req_Id
    FROM [Leave Request Detail]
    WHERE canceled != 'yes'
    OPEN missing_resords
    FETCH NEXT FROM missing_resords INTO @st, @en, @can, @id_re
    WHILE @@fetch_status <> -1
    BEGIN

SET @lenght = (LEN((SELECT MAX(Report_id) FROM #result))-3)

IF (@st> (SELECT MIN(end_date) from #result))
        BEGIN
            INSERT INTO #result
            (
                Report_id, 
                start_date, 
                end_date
             )
             VALUES
             (
                CASE WHEN @lenght IS NULL THEN 'rep10001'
                     ELSE 'rep'+ CAST((CAST(@id AS INT) + 1) AS VARCHAR(10)) END, 
                @st, 
                @en
              )
        END

FETCH NEXT FROM missing_resords INTO @st, @en, @can, @id_re
        END
        CLOSE missing_resords
        DEALLOCATE missing_resords

select * from #result


run \| edit \| history \| help	0