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”.

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 &lt;&gt; -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 &lt;&gt; -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