Answered by:
SQL Query to find missed nunmber from each group

-
Hi,
I have a set of records called "Level" and its values are A, B, C, D.
From the below list, I want to identify the missed level for each group.
ID Level Type
1 A Floor
1 B Floor
2 A Wind
2 C Wind
I need result like: [Missed level records]
ID Level Type
1 C NULL 1- doesnt has C and D
1 D NULL
2 B NULL 2- doesnt has A and C
2 D NULL
Pls suggest.
Regards,
-Sugumar Pannerselvam
Question
Answers
-
Hi,
Got output with below code. may helpful for others.
DECLARE @TblLevel TABLE(Level VARCHAR(10)) INSERT INTO @TblLevel(Level) VALUES('A'),('B'),('C'),('D') DECLARE @TblRecords TABLE(ID INT, Level CHAR(1), Type VARCHAR(10)) INSERT INTO @TblRecords(ID, Level, Type) VALUES(1,'A','Floor'),(1,'B','Floor'),(2,'A','Wind'),(2,'C','Wind') DECLARE @TblResult TABLE(ID INT, Level CHAR(1), Type VARCHAR(10)) INSERT INTO @TblResult(ID, Level, Type) SELECT DISTINCT ID, TblLevel.Level, Type FROM @TblRecords TblRec CROSS JOIN @TblLevel TblLevel SELECT TblResult.* FROM @TblResult TblResult LEFT OUTER JOIN @TblRecords TblRec ON TblResult.ID = TblRec.ID AND TblResult.Level = TblRec.Level WHERE TblRec.Level IS NULL ORDER BY 1
Looking for some best way to achieve this. As of now considered this as answer. :-)
Regards,
Sugumar Pannerselvam.
- Marked as answer by Sugumar Pannerselvam Thursday, August 14, 2014 9:48 PM
- Edited by Sugumar Pannerselvam Thursday, August 14, 2014 9:48 PM
-
- Marked as answer by Sugumar Pannerselvam Friday, August 15, 2014 1:03 PM
All replies
-
Hi,
Got output with below code. may helpful for others.
DECLARE @TblLevel TABLE(Level VARCHAR(10)) INSERT INTO @TblLevel(Level) VALUES('A'),('B'),('C'),('D') DECLARE @TblRecords TABLE(ID INT, Level CHAR(1), Type VARCHAR(10)) INSERT INTO @TblRecords(ID, Level, Type) VALUES(1,'A','Floor'),(1,'B','Floor'),(2,'A','Wind'),(2,'C','Wind') DECLARE @TblResult TABLE(ID INT, Level CHAR(1), Type VARCHAR(10)) INSERT INTO @TblResult(ID, Level, Type) SELECT DISTINCT ID, TblLevel.Level, Type FROM @TblRecords TblRec CROSS JOIN @TblLevel TblLevel SELECT TblResult.* FROM @TblResult TblResult LEFT OUTER JOIN @TblRecords TblRec ON TblResult.ID = TblRec.ID AND TblResult.Level = TblRec.Level WHERE TblRec.Level IS NULL ORDER BY 1
Looking for some best way to achieve this. As of now considered this as answer. :-)
Regards,
Sugumar Pannerselvam.
- Marked as answer by Sugumar Pannerselvam Thursday, August 14, 2014 9:48 PM
- Edited by Sugumar Pannerselvam Thursday, August 14, 2014 9:48 PM
-
- Marked as answer by Sugumar Pannerselvam Friday, August 15, 2014 1:03 PM