SQL Puzzle | The five consecutive puzzle

You have to write a query that will give us 5 consecutive dates based on the id.

For details please check out the sample input and the expected output below-

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--


CREATE TABLE Dates
(
	dt DATE,
	id INT
)
GO

INSERT INTO Dates VALUES('05-AUG-16', 1);
INSERT INTO Dates VALUES('10-AUG-16', 1);
INSERT INTO Dates VALUES('11-AUG-16', 1);
INSERT INTO Dates VALUES('12-AUG-16', 1);
INSERT INTO Dates VALUES('13-AUG-16', 1);
INSERT INTO Dates VALUES('14-AUG-16', 1);
INSERT INTO Dates VALUES('16-AUG-16', 1);
INSERT INTO Dates VALUES('18-AUG-16', 1);
INSERT INTO Dates VALUES('19-AUG-16', 1);
INSERT INTO Dates VALUES('21-AUG-16', 1);
INSERT INTO Dates VALUES('22-AUG-16', 1);
INSERT INTO Dates VALUES('15-AUG-16', 2);
INSERT INTO Dates VALUES('16-AUG-16', 2);
INSERT INTO Dates VALUES('17-AUG-16', 2);
INSERT INTO Dates VALUES('18-AUG-16', 2);
INSERT INTO Dates VALUES('19-AUG-16', 2);
INSERT INTO Dates VALUES('20-AUG-16', 3);
INSERT INTO Dates VALUES('19-Sep-16', 4);

--

—

Solution 1

--

/*
**  Solution 1
*/

SELECT dt, id FROM 
(

       SELECT Id, dt, COUNT(*) OVER (PARTITION BY Rnk) rn FROM
       (
             SELECT * , DAY(dt) - ROW_NUMBER() OVER (PARTITION BY Id ORDER BY dt) rnk FROM Dates
       )r

)t WHERE rn = 5



--

Solution 2

--


/*
**  Solution 2
*/

SELECT Dt,Id FROM
(
       SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id) rr FROM 
       (
             SELECT DISTINCT d1.dt, d1.id FROM dates d1 join dates d2 on d1.id = d2.id where datediff(dd, d2.dt, d1.dt) = 1 
              or datediff(dd, d1.dt, d2.dt) = 1
       ) P
) as P where rr <= 5


--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com