SQL Puzzle | The Next Working Date Puzzle

In this puzzle you have to figure our the next working date based on the input date column called SpecialDate. Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE DateTable
(
	SpecialDate datetime
)
GO
        
INSERT INTO DateTable
VALUES
('2018-05-01 00:00:00'), ('2018-05-02 00:00:00'),    ('2018-05-03 00:00:00'),    ('2018-05-04 00:00:00') 


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1

--


SELECT * , DATEADD(dd,CASE WHEN DATEDIFF(dd,0,SpecialDate)%7 > 3 THEN 7-DATEDIFF(dd,0,SpecialDate)%7 ELSE 1 END,SpecialDate) NextDate FROM 
DateTable


--

Output

--

/*------------------------
OUTPUT
------------------------*/
SpecialDate             NextDate
----------------------- -----------------------
2018-05-01 00:00:00.000 2018-05-02 00:00:00.000
2018-05-02 00:00:00.000 2018-05-03 00:00:00.000
2018-05-03 00:00:00.000 2018-05-04 00:00:00.000
2018-05-04 00:00:00.000 2018-05-07 00:00:00.000

(4 row(s) affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com