Find out the next business days using Co-related subquery

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 910
Suppose we have something as under -

DATE	LEAVEDAYS	NXTWRKDAYS
2014-07-01 No NULL
2014-07-02 No NULL
2014-07-03 No NULL
2014-07-04 No NULL
2014-07-05 Yes NULL
2014-07-06 Yes NULL
2014-07-07 Yes NULL
2014-07-08 No NULL


We need to find out the next business days such that the output will look as

DATE	LEAVEDAYS	NXTWRKDAYS
2014-07-01 No 2014-07-02
2014-07-02 No 2014-07-03
2014-07-03 No 2014-07-04
2014-07-04 No 2014-07-05
2014-07-05 Yes 2014-07-08
2014-07-06 Yes 2014-07-08
2014-07-07 Yes 2014-07-08
2014-07-08 No 2014-07-09


The below co-related sub query will help to do so

declare @t table([DATE] DATE,LEAVEDAYS Varchar(10),NXTWRKDAYS DATE)

insert into @t Values

('2014-07-01','No',NULL),

('2014-07-02','No',NULL),

('2014-07-03','No',NULL),

('2014-07-04','No',NULL),

('2014-07-05','Yes',NULL),

('2014-07-06','Yes',NULL),

('2014-07-07','Yes',NULL),

('2014-07-08','No',NULL)

SELECT

t.date AS Date,

t.leavedays AS LeaveDays,

CASE WHEN t.LEAVEDAYS = 'No' THEN DATEADD(day,1, t.DATE)

WHEN t.LEAVEDAYS = 'Yes' THEN (Select top 1 t1.date from @t t1 WHERE t1.date > t.DATE and t1.LEAVEDAYS = 'No' ORDER BY t1.date)

ELSE null END AS NxtWorkingDay

FROM @t t

Comments or Responses

Login to post response