Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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 (@en> (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
sarthak rana
Transitive grouping with recursive sql
non_numeric_table
nnnnn
Task_7_Final
Exp8Q2
BRYAN_BD
wall
Libros
Hierarchy table to exclude root