**Tags**

Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Divide rows into two columns, 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, last non null puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, Pawan, Pawan Khowal, Pawan Kumar, Pawan Kumar Khowal, PL/SQL Challenges, Prev Value puzzle, Previous value puzzle, 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 ], The Biggest Gap Puzzle, The Gap Puzzle, The Gap Puzzle Puzzle, The Tree 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 | [ Minus -1 or Minimum Child Puzzle ]**

**Puzzle Description**

- We have 2 tables called MinusParent & MinusChildren.
- We have to find out ChildServiceId for each parentId present in MinusChildren Table. Well there are some rules to pick ChildServiceId. They are-
- Rule1#- If all ChildServiceId values for a parentId are -1 then we have to pick -1 as ChildServiceID and minimum childId corresponding to that parentID. E.g. – In case of ParentID = 1 we need following values

ChildID = 101, ChildServiceID = -1

4. Rule 2#- If a single childServiceId has a non -1 value for a ParentId, then we have to pick that value as a ChildServiceId and its ChildId. E.g. In case of ParentId = 2, We need following values

ChildID = 202, ChildServiceID = 23

5. Rule 3# – If there are multiple non -1 values present for a single ParentId then we have to pick the minimum childId and its corresponding childServiceId. E.g. In case of ParentId = 4, we need following values

ChildID = 402, ChildServiceID = 23

6. Please check out the sample input and expected output for detail

Pictorial presentation of the puzzle.

**Sample Input**

MinusParent

ParentID | Value |

1 | A |

2 | B |

3 | C |

4 | D |

5 | E |

6 | F |

MinusChildren

ChildId | ParentID | ChildServiceID | Timestampes |

101 | 1 | -1 | 00:46.8 |

102 | 1 | -1 | 00:46.8 |

103 | 1 | -1 | 00:46.8 |

104 | 1 | -1 | 00:46.8 |

105 | 1 | -1 | 00:46.8 |

201 | 2 | -1 | 00:46.8 |

202 | 2 | 23 | 00:46.8 |

203 | 2 | -1 | 00:46.8 |

204 | 2 | -1 | 00:46.8 |

205 | 2 | -1 | 00:46.8 |

301 | 3 | -1 | 00:46.8 |

302 | 3 | -1 | 00:46.8 |

303 | 3 | -1 | 00:46.8 |

304 | 3 | -1 | 00:46.8 |

305 | 3 | 14 | 00:46.8 |

401 | 4 | -1 | 00:46.8 |

402 | 4 | 23 | 00:46.8 |

403 | 4 | 25 | 00:46.8 |

404 | 4 | -1 | 00:46.8 |

501 | 5 | 21 | 00:46.8 |

502 | 5 | 23 | 00:46.8 |

601 | 6 | -1 | 00:46.8 |

**Expected Output**

ParentID | Value | ChildId | ChildServiceId |

1 | A | 101 | -1 |

2 | B | 202 | 23 |

3 | C | 305 | 14 |

4 | D | 402 | 23 |

5 | E | 501 | 21 |

6 | F | 601 | -1 |

**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) topawankkmr@gmail.com

**Script**

Use the below script to generate the source table and fill them up with the sample data.

-- CREATE TABLE MinusParent ( ParentID INT PRIMARY KEY ,Value VARCHAR(10) ) GO INSERT INTO MinusParent(ParentID,Value) VALUES (1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E'),(6,'F') GO CREATE TABLE MinusChildren ( ChildId INT ,ParentID INT REFERENCES MinusParent(ParentID) ,ChildServiceID INT ,Timestampes DATETIME ,PRIMARY KEY(ChildId,ParentId) ) GO INSERT INTO MinusChildren VALUES (101,1,-1,GETDATE()),(102,1,-1,GETDATE()),(103,1,-1,GETDATE()),(104,1,-1,GETDATE()),(105,1,-1,GETDATE()), (201,2,-1,GETDATE()),(202,2,23,GETDATE()),(203,2,-1,GETDATE()),(204,2,-1,GETDATE()),(205,2,-1,GETDATE()), (301,3,-1,GETDATE()),(302,3,-1,GETDATE()),(303,3,-1,GETDATE()),(304,3,-1,GETDATE()),(305,3,14,GETDATE()), (401,4,-1,GETDATE()),(402,4,23,GETDATE()),(403,4,25,GETDATE()),(404,4,-1,GETDATE()), (501,5,21,GETDATE()),(502,5,23,GETDATE()), (601,6,-1,GETDATE()) GO CREATE NONCLUSTERED INDEX Ix_ParentID ON MinusChildren (ParentID) GO CREATE NONCLUSTERED INDEX Ix_Filtered_ParentID_ChildId_ChildServiceId ON MinusChildren (ParentID,ChildId,ChildServiceId) WHERE ChildServiceId <> -1 GO --

**Update July 7 | Solution1 – Pawan Kumar Khowal**

-- SET STATISTICS IO ON SELECT P.ParentID , P.Value , ISNULL(D.ChildId,D1.ChildId) ChildId , ISNULL(ChildServiceID,-1) ChildServiceId FROM MinusParent p OUTER APPLY ( SELECT TOP (1) c.ChildId, c.ChildServiceID FROM MinusChildren c WHERE p.ParentID = c.ParentID AND c.ChildServiceID <> -1 ORDER BY ChildId ASC ) AS D CROSS APPLY ( SELECT MIN(ChildId) ChildId FROM MinusChildren m3 WHERE m3.ParentID = p.ParentID ) AS D1 --

Add a comment if you have any other solution in mind. We all need to learn. Keep Learning

**Http://MSBISkills.com**

**Pawan Kumar Khowal**

jerryjames08outlookcom

said:;WITH CTE1

AS

(

SELECT P.ParentID,Value,ChildId,ChildServiceID,ROW_NUMBER() OVER(Partition By P.ParentID ORDER BY ChildID,ChildServiceID ASC)R

FROM #Parent P LEFT OUTER JOIN #Child C

ON P.ParentID=C.ParentID

)

,CTE2

AS

(

SELECT P.ParentID,Value,ChildId,ChildServiceID,ROW_NUMBER() OVER(Partition By P.ParentID ORDER BY ChildServiceID ASC)R

FROM #Parent P LEFT OUTER JOIN #Child C

ON P.ParentID=C.ParentID

WHERE ChildServiceID-1

)

SELECT ISNULL(C2.ParentID,C1.ParentID)ParentID

,ISNULL(C2.Value,C1.Value)Value

,ISNULL(C2.ChildId,C1.ChildId)ChildId

,ISNULL(C2.ChildServiceID,C1.ChildServiceID)ChildServiceID

FROM CTE1 C1 LEFT OUTER JOIN CTE2 C2

ON C1.ParentID=C2.ParentID AND C1.R=1 AND C2.R=1

WHERE C1.R=1

LikeLike

jerryjames08outlookcom

said:;WITH CTE1

AS

(

SELECT P.ParentID,Value,ChildId,ChildServiceID,ROW_NUMBER() OVER(Partition By P.ParentID ORDER BY ChildID,ChildServiceID ASC)R

FROM #Parent P LEFT OUTER JOIN #Child C

ON P.ParentID=C.ParentID

)

,CTE2

AS

(

SELECT P.ParentID,Value,ChildId,ChildServiceID,ROW_NUMBER() OVER(Partition By P.ParentID ORDER BY ChildServiceID ASC)R

FROM #Parent P LEFT OUTER JOIN #Child C

ON P.ParentID=C.ParentID

WHERE ChildServiceID != -1

)

SELECT ISNULL(C2.ParentID,C1.ParentID)ParentID

,ISNULL(C2.Value,C1.Value)Value

,ISNULL(C2.ChildId,C1.ChildId)ChildId

,ISNULL(C2.ChildServiceID,C1.ChildServiceID)ChildServiceID

FROM CTE1 C1 LEFT OUTER JOIN CTE2 C2

ON C1.ParentID=C2.ParentID AND C1.R=1 AND C2.R=1

WHERE C1.R=1

LikeLiked by 1 person