Tags

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


T-SQL Query | [ The Complex Week Puzzle ]

Puzzle Statement

  1. We have a table called ProductInfo. Here have information about product and their end date
  2. We have a column called “EndDate”. We have to find out the new EndDate. Logic for new Enddate is explained below.
  3. 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.
  4. 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.

The Week Puzzle

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

Advertisements