none
SQL Query to find missed nunmber from each group

    Question

  • 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

    Thursday, August 14, 2014 8:01 PM

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.


    Thursday, August 14, 2014 9:47 PM
  • SELECT ID, Level
    FROM   (SELECT DISTINCT ID FROM tbl) t
    CROSS JOIN (VALUES('A'), ('B'), ('C'), ('D')) AS l(Level)
    WHERE  NOT EXISTS (SELECT *
                       FROM   tbl t2
                       WHERE  t1.id = t2.id
                         AND  l.Level = t2.Level)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 14, 2014 9:49 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.


    Thursday, August 14, 2014 9:47 PM
  • SELECT ID, Level
    FROM   (SELECT DISTINCT ID FROM tbl) t
    CROSS JOIN (VALUES('A'), ('B'), ('C'), ('D')) AS l(Level)
    WHERE  NOT EXISTS (SELECT *
                       FROM   tbl t2
                       WHERE  t1.id = t2.id
                         AND  l.Level = t2.Level)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 14, 2014 9:49 PM