Sign in
 

none
SQL Query to find missed nunmber from each group

 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |