T-SQL Query | [ Minus -1 or Minimum Child Puzzle ]

Puzzle Description

1. We have 2 tables called MinusParent & MinusChildren.
2. We have to find out ChildServiceId for each parentId present in MinusChildren Table. Well there are some rules to pick ChildServiceId. They are-
3. 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