Tags
Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Khowal, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, T-SQL Query | [ The Complex Week Puzzle ], TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries, Week puzzle
T-SQL Query | [ The Complex Week Puzzle ]
Puzzle Statement
- We have a table called ProductInfo. Here have information about product and their end date
- We have a column called “EndDate”. We have to find out the new EndDate. Logic for new Enddate is explained below.
- If the next EndDate is not equal to previous date + 7 days , then we have replace all those previous dates by the max date in that group.
- E.g. Lets consider first four EndDates with a break
2014-01-19
2014-01-26 = (2014-01-19 + 7 Days)
2014-02-02 = (2014-01-26 + 7 Days)
2014-02-16 = (2014-02-02 + 7 Days) X (FALSE) , Now we have to replace all the previous values by 2014-02-02 as here the logic breaks.
Sample Input
| ProductId | ProductName | EndDate |
| 101 | RIN | 2014-01-19 |
| 101 | RIN | 2014-01-26 |
| 101 | RIN | 2014-02-02 |
| 101 | RIN | 2014-02-16 |
| 101 | RIN | 2014-03-02 |
| 101 | RIN | 2014-03-16 |
| 101 | RIN | 2014-04-20 |
| 101 | RIN | 2014-04-27 |
| 101 | RIN | 2014-05-11 |
| 101 | RIN | 2014-06-08 |
| 101 | RIN | 2014-06-15 |
| 101 | RIN | 2014-08-03 |
| 101 | RIN | 2014-08-17 |
| 101 | RIN | 2014-09-28 |
| 101 | RIN | 2014-10-19 |
| 101 | RIN | 2014-10-26 |
| 101 | RIN | 2014-11-02 |
| 101 | RIN | 2014-11-16 |
| 101 | RIN | 2014-11-23 |
Expected Output
| ProductId | ProductName | EndDate | NewDate |
| 101 | RIN | 2014-01-19 | 2014-02-02 |
| 101 | RIN | 2014-01-26 | 2014-02-02 |
| 101 | RIN | 2014-02-02 | 2014-02-02 |
| 101 | RIN | 2014-02-16 | 2014-02-16 |
| 101 | RIN | 2014-03-02 | 2014-03-02 |
| 101 | RIN | 2014-03-16 | 2014-03-16 |
| 101 | RIN | 2014-04-20 | 2014-04-27 |
| 101 | RIN | 2014-04-27 | 2014-04-27 |
| 101 | RIN | 2014-05-11 | 2014-05-11 |
| 101 | RIN | 2014-06-08 | 2014-06-15 |
| 101 | RIN | 2014-06-15 | 2014-06-15 |
| 101 | RIN | 2014-08-03 | 2014-08-03 |
| 101 | RIN | 2014-08-17 | 2014-08-17 |
| 101 | RIN | 2014-09-28 | 2014-09-28 |
| 101 | RIN | 2014-10-19 | 2014-11-02 |
| 101 | RIN | 2014-10-26 | 2014-11-02 |
| 101 | RIN | 2014-11-02 | 2014-11-02 |
| 101 | RIN | 2014-11-16 | 2014-11-23 |
| 101 | RIN | 2014-11-23 | 2014-11-23 |
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
Script Use the below script to generate the source table and fill them up with the sample data.
—
CREATE TABLE ProductInfo
(
ProductId INT
,ProductName VARCHAR(100)
,EndDate DATETIME
)
GO
INSERT INTO ProductInfo(ProductId,ProductName,EndDate) VALUES
(101 ,'RIN' ,'2014-01-19'),
(101 ,'RIN' ,'2014-01-26'),
(101 ,'RIN' ,'2014-02-02'),
(101 ,'RIN' ,'2014-02-16'),
(101 ,'RIN' ,'2014-03-02'),
(101 ,'RIN' ,'2014-03-16'),
(101 ,'RIN' ,'2014-04-20'),
(101 ,'RIN' ,'2014-04-27'),
(101 ,'RIN' ,'2014-05-11'),
(101 ,'RIN' ,'2014-06-08'),
(101 ,'RIN' ,'2014-06-15'),
(101 ,'RIN' ,'2014-08-03'),
(101 ,'RIN' ,'2014-08-17'),
(101 ,'RIN' ,'2014-09-28'),
(101 ,'RIN' ,'2014-10-19'),
(101 ,'RIN' ,'2014-10-26'),
(101 ,'RIN' ,'2014-11-02'),
(101 ,'RIN' ,'2014-11-16'),
(101 ,'RIN' ,'2014-11-23')
— |
Update May 8 | Solution 1 — Pawan Kumar Khowal
--
;WITH CTE1 AS
(
SELECT ProductId , ProductName , EndDate , ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY EndDate) rnk
FROM ProductInfo
)
,CTE2 AS
(
SELECT * , CASE WHEN
A.EndDate = (SELECT DATEADD(DAY, 7, EndDate) from CTE1 c3 WHERE c3.rnk = (SELECT MAX(c1.rnk) FROM CTE1 c1 WHERE c1.rnk < A.rnk ))
THEN 0 ELSE 1 END cols
FROM CTE1 A
)
,CTE3 AS
(
SELECT * , SUM(cols) OVER (ORDER BY rnk) grouper
FROM CTE2
)
,CTE4 AS
(
SELECT MIN(ProductId) ProductId , MIN(ProductName) ProductName , MIN(EndDate) StartDate , MAX (EndDate) EndDate FROM CTE3
GROUP BY grouper
)
SELECT b.ProductId , b.ProductName , b.EndDate , a.EndDate NewDate
FROM CTE1 b LEFT JOIN CTE4 a ON b.EndDate BETWEEN a.StartDate AND a.EndDate
--
|
Update May 8 | Solution 2 – Parveen Madaan
--
;WITH CTE1 AS
(
SELECT ProductId , ProductName , EndDate
, DATEDIFF(wk, (SELECT MIN(EndDate) FROM ProductInfo),EndDate) - ROW_NUMBER() OVER( ORDER BY EndDate) rnk
FROM ProductInfo
)
,CTE2 AS
(
SELECT MIN(ProductId) ProductId , MIN(ProductName) ProductName , MIN(EndDate) StartDate , MAX (EndDate) EndDate FROM CTE1
GROUP BY rnk
)
SELECT b.ProductId , b.ProductName , b.EndDate , a.EndDate NewDate
FROM CTE1 b LEFT JOIN CTE2 a ON b.EndDate BETWEEN a.StartDate AND a.EndDate
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Http://MSBISkills.com
Pawan Kumar Khowal
Improving my SQL BI Skills 
;with cte as (
select *, datediff(day,enddate,lead(enddate) over (order by enddate)) as t, lead(enddate) over (order by enddate) as d from ProductInfo)
, cte2 as (select *, ROW_NUMBER() over (order by ProductId)-ROW_NUMBER() over (partition by t order by ProductId) as s from cte)
select ProductId, ProductName, enddate, newdate=case
when t=7 then max(d) over (partition by t order by t, s) else enddate end
from cte2 order by enddate
LikeLike