Finding a NULL value in the record set

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  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'.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  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

  ---