This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and require output data to find maximum consecutive wins for team India.
Input Data:
|
1 2 3 4 5 6 7 8 |
TeamOne TeamTwo MatchDate WonBy ------- ------- ---------- ----- Ind Aus 2017-08-22 Ind Ind Aus 2016-08-26 Ind Ind Aus 2014-08-14 Ind Ind Aus 2013-08-23 Aus Ind Aus 2012-08-25 Ind Ind Aus 2010-08-08 Ind |
Expected output:
|
1 2 3 4 5 |
TeamOne TeamTwo MatchDate WonBy ------- ------- ---------- ----- Ind Aus 2014-08-14 Ind Ind Aus 2012-08-25 Ind Ind Aus 2010-08-08 Ind |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE Matches ( TeamOne CHAR(3) ,TeamTwo CHAR(3) ,MatchDate DATE ,WonBy CHAR(3) ) GO INSERT INTO Matches VALUES ('Ind','Aus','08-22-2017','Ind') ,('Ind','Aus','08-26-2016','Ind') ,('Ind','Aus','08-14-2014','Ind') ,('Ind','Aus','08-23-2013','Aus') ,('Ind','Aus','08-25-2012','Ind') ,('Ind','Aus','08-08-2010','Ind') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
;WITH CTE AS ( SELECT TeamOne,TeamTwo,MatchDate,WonBy ,ISNULL(Lead(WonBy) OVER (ORDER BY (SELECT NULL)),0) AS NextWin FROM Matches ) ,CTE2 AS ( SELECT *,IIF(NextWin<> WonBy,1,0) AS a ,ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) AS b FROM cte ) ,CTE3 AS ( SELECT *, b - ISNULL(lag(b) OVER (ORDER BY (SELECT NULL)),0) AS C FROM CTE2 WHERE a = 1 ) SELECT t2.* FROM cte3 t1 RIGHT OUTER JOIN Matches t2 ON t1.MatchDate = t2.MatchDate WHERE t2.MatchDate <= (SELECT MatchDate FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3)) AND t2.WonBy = (SELECT WonBy FROM cte3 WHERE C = (SELECT MAX(C) FROM CTE3)) |
Please try the different solutions for this puzzle and share it via comment...
