Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

dt id
2016-08-05 1
2016-08-10 1
2016-08-11 1
2016-08-12 1
2016-08-13 1
2016-08-14 1
2016-08-16 1
2016-08-18 1
2016-08-19 1
2016-08-21 1
2016-08-22 1
2016-08-15 2
2016-08-16 2
2016-08-17 2
2016-08-18 2
2016-08-19 2
2016-08-20 3
2016-09-19 4

Expected Output

dt id
2016-08-10 1
2016-08-11 1
2016-08-12 1
2016-08-13 1
2016-08-14 1
2016-08-15 2
2016-08-16 2
2016-08-17 2
2016-08-18 2
2016-08-19 2

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

Advertisements