Find unique NULL records from data set

• NineIron

  SSChampion

  Points: 12529

  More actions

  November 23, 2016 at 6:55 am

  #319205

  I need to find patients who do not have a Primary Care Provider. The test table represents data that identifies patient visits, VisitID, and different provider types. In other words find the VisitID where there is no 'Y' in the "IsPrimaryCareProvider" field.

  Using this,

  where VisitID not in(selectdistinct VisitID

  from livefdb.dbo.RegAcct_Providers

  where IsPrimaryCareProvider='Y')

  in the where clause causing performance issues. There is one VisitID,F0-B20161112062935910, in #Test that does not have a 'Y' in the "IsPrimaryCareProvider" field.

  Any thoughts?

  create table #Test

  (

  VisitID varchar(30),

  IsPrimaryCareProvider char(1),

  IsAdmittingProvider char(1),

  IsAttendingProvider char(1),

  IsEmergencyProvider char(1)

  )

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,'Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,'Y',NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517','Y',NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161025185450517',NULL,NULL,NULL,'Y')

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,'Y','Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115150343245',NULL,'Y','Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115150343245','Y',NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115150343245',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,'Y',NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,'Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856','Y',NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161115163039856',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109','Y',NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,'Y')

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161111162048109',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,'Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161112062935910',NULL,NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118113444037',NULL,'Y',NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118113444037',NULL,NULL,'Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118113444037',NULL,NULL,NULL,'Y')

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118113444037','Y',NULL,NULL,NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118121157187',NULL,'Y','Y',NULL)

  insert into #Test(VisitID, IsPrimaryCareProvider, IsAdmittingProvider, IsAttendingProvider, IsEmergencyProvider) values('F0-B20161118121157187','Y',NULL,NULL,NULL)

• Neil Burton

  SSC-Insane

  Points: 22598

  More actions

  November 23, 2016 at 7:18 am

  #1914225

  Does this do what you want?

  SELECT

  VisitID

  FROM #Test t

  WHERE NOT EXISTS(

  SELECT 1 FROM #Test t1

  WHERE t1.IsPrimaryCareProvider ='Y'

  AND t1.VisitID = t.VisitID

  )

  GROUP BY

  VisitID

  ---

  On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
  —Charles Babbage, Passages from the Life of a Philosopher

  How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

• NineIron

  SSChampion

  Points: 12529

  More actions

  November 23, 2016 at 7:42 am

  #1914231

  Wonderful. Thanx.

• ScottPletcher

  SSC Guru

  Points: 100262

  More actions

  November 23, 2016 at 8:34 am

  #1914247

  This should be more efficient:

  SELECT VisitID

  FROM #Test

  GROUP BY VisitID

  HAVING MAX(CASE WHEN IsPrimaryCareProvider ='Y' THEN 1 ELSE 0 END) = 0

  SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• NineIron

  SSChampion

  Points: 12529

  More actions

  November 23, 2016 at 8:51 am

  #1914250

  Thanx.