Post reply

Finding a NULL value in the record set

  • March 31, 2015 at 7:46 am

    #398262

    CREATE TABLE kiQCTest (

    OrderNum varchar(10),

    OrderLine int,

    QCPass bit)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine]) VALUES ('OD15-00010', 1)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine]) VALUES ('OD15-00010', 2)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine]) VALUES ('OD15-00010', 3)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine],[QCPass]) VALUES ('OD15-00010', 4, 1)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine],[QCPass]) VALUES ('OD15-00010', 5, 1)

    INSERT INTO kiQCTest ([OrderNum] ,[OrderLine],[QCPass]) VALUES ('OD15-00010', 6, 0)

    I want to write a select statement which will return one value. Thes resulting value is 'Quality Complete' if there are no NULL value in the QC Pass field in the dataset.

    Or it should return me 'QC Incomplete'.

  • March 31, 2015 at 8:01 am

    #1787735

    Something like this?

    select kqt.OrderNum

    ,QCStatus = (case when count(kqt.OrderNum) - count(kqt.QCPass) > 0 then 'QC Incomplete'

    else 'QC Complete'

    end)

    from dbo.kiQCTest kqt

    group by kqt.OrderNum

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply