Tags

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


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

Pawan Kumar Khowal - Minus -1 Or Minimum Child Puzzle

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

Advertisements