08 | May | 2015 | Improving my SQL BI Skills

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

Share this