How to fixed this bandi for Complicate situation [Resolved]

Posted by Jayakumars under ADO.NET on 11/26/2015 | Points: 10 | Views : 602 | Status : [Member] [MVP] | Replies : 2
Hi

Bandi

I need business day query for this situation

My Table data this

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


-- I need following output


--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

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 11/26/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

Resolved
MSSQL Version
----------------

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


Output
---------

Date	    LeaveDays	NxtWorkingDay

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


If you are using MySQL, please use DATE_ADD() ( http://www.w3schools.com/sql/func_date_add.asp ) instead of DATEADD function of SQL Server.

Hope that helps.

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajnilari2015 on: 11/29/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25

Up
0
Down
Glad that it has helped you (:

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response