Return row values where exists in table, "not scanned" where missed scan

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 9, 2014 at 11:35 am

  #145025

  Hi SSC,

  I have two tables - Production and Inspection. I want to return all production records because production will be 100% however, when Inspection missed a unit produced then I want to return "not scanned". NULL is not an option either unit exists or it does not. Below is not what I need but it's close.

  Thanks!

  select c.operator, c.unit, q.grfpdate, q.qcoperator, q.dateinspectedFP, hsOperator, hadateFP, haOperator

  from completedUnit c

  join assemblyQC q on c.sn = q.sn

  where c.operator = 'builder' and c.workorder = '17879' and exists(

  select sn

  from assemblyqc s

  where c.sn = s.sn)

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 9, 2014 at 12:59 pm

  #1728194

  Getting closer......

  select c.operator, c.unit, q.grfpdate, q.qcoperator, q.dateinspectedFP, hsOperator, hadateFP, haOperator,

  case when c.sn = q.sn then 'Scanned' else 'Not Scanned' end Inspected

  from completedUnit c

  join assemblyQC q on c.sn = q.sn

  where c.operator = 'Builder' and c.workorder = '17879'

  order by unit

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 9, 2014 at 1:15 pm

  #1728197

  This gets me nearer to my results but now I'm not getting my relational data from the Inspected table. Anyway, I'm getting closer!!! On another note I'm seeing performance issues.

  select c.operator, c.unit,

  case when exists(select sn from assemblyqc s where c.sn = s.sn) then 'Scanned' else 'Not Scanned' end Inspected

  from completedUnit c

  where c.operator = 'Builder' and c.workorder = '17879'

  order by unit

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 9, 2014 at 2:05 pm

  #1728212

  If you would post the DDL (CREATE TABLE statement) for the tables involved, some sample data (INSERT INTO statements) that is representative of your problem domain, the expected results based on the sample data I am sure we could provide you with additional assistance.

  Based solely on what you have posted so far, I really couldn't be of much help as I am not really sure what it is you are trying to accomplish.

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 10, 2014 at 11:33 am

  #1728471

  Lynn,

  Will this work? I really appreciate the assistance!!!

  CREATE TABLE

  [dbo].[Test_completedUnit](

  [unit] [nvarchar](4) NOT NULL,

  [operator] [nvarchar](50) NULL,

  [SN] [varchar](50) PRIMARY KEY NOT NULL,

  [Workorder][varchar](7)NOT NULL

  )

  INSERT INTO test_completedUnit

  (unit, workorder, operator, sn)

  SELECT '01','17879','VERHEY, CHARLES','05201417879001' UNION ALL

  SELECT '02','17879','VERHEY, CHARLES','05201417879002' UNION ALL

  SELECT '03','17879','VERHEY, CHARLES','05201417879003' UNION ALL

  SELECT '04','17879','VERHEY, CHARLES','05201417879004' UNION ALL

  SELECT '05','17879','VERHEY, CHARLES','05201417879005' UNION ALL

  SELECT '06','17879','VERHEY, CHARLES','05201417879006' UNION ALL

  SELECT '07','17879','VERHEY, CHARLES','05201417879007' UNION ALL

  SELECT '08','17879','VERHEY, CHARLES','05201417879008' UNION ALL

  SELECT '09','17879','VERHEY, CHARLES','05201417879009' UNION ALL

  SELECT '10','17879','VERHEY, CHARLES','05201417879010' UNION ALL

  SELECT '11','17879','VERHEY, CHARLES','05201417879011' UNION ALL

  SELECT '12','17879','VERHEY, CHARLES','05201417879012' UNION ALL

  SELECT '13','17879','VERHEY, CHARLES','05201417879013' UNION ALL

  SELECT '14','17879','VERHEY, CHARLES','05201417879014' UNION ALL

  SELECT '15','17879','VERHEY, CHARLES','05201417879015' UNION ALL

  SELECT '16','17879','VERHEY, CHARLES','05201417879016' UNION ALL

  SELECT '17','17879','VERHEY, CHARLES','05201417879017' UNION ALL

  SELECT '18','17879','VERHEY, CHARLES','05201417879018' UNION ALL

  SELECT '19','17879','VERHEY, CHARLES','05201417879019' UNION ALL

  SELECT '20','17879','VERHEY, CHARLES','05201417879020' UNION ALL

  SELECT '21','17879','VERHEY, CHARLES','05201417879021' UNION ALL

  SELECT '22','17879','VERHEY, CHARLES','05201417879022' UNION ALL

  SELECT '23','17879','VERHEY, CHARLES','05201417879023' UNION ALL

  SELECT '24','17879','VERHEY, CHARLES','05201417879024' UNION ALL

  SELECT '25','17879','VERHEY, CHARLES','05201417879025' UNION ALL

  SELECT '26','17879','VERHEY, CHARLES','05201417879026' UNION ALL

  SELECT '27','17879','VERHEY, CHARLES','05201417879027' UNION ALL

  SELECT '28','17879','VERHEY, CHARLES','05201417879028' UNION ALL

  SELECT '29','17879','VERHEY, CHARLES','05201417879029' UNION ALL

  SELECT '30','17879','VERHEY, CHARLES','05201417879030' UNION ALL

  CREATE TABLE [dbo].[Test_assemblyQC](

  [SN] [nvarchar](16) NOT NULL,

  [qcOperator] [nvarchar](20) NOT NULL,

  [dateinspectedFP] [datetime] NULL,

  [grFPDate] [datetime] NULL,

  [hsOperator] [varchar](50) NULL,

  [haOperator] [varchar](30) NULL,

  [hadateFP] [datetime] NULL)

  INSERT INTO Test_assemblyQC

  (QCOperator, SN, grfpdate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  SELECT 'Roger Willet','05201417879003','May 29 2014 6:26PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:08AM' UNION ALL

  SELECT 'Roger Willet','05201417879005','May 29 2014 6:31PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879006','May 29 2014 6:28PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879007','May 29 2014 6:33PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879008','May 29 2014 6:35PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879009','May 29 2014 6:38PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879010','May 29 2014 6:37PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879011','May 29 2014 6:41PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879012','May 29 2014 6:39PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879013','May 29 2014 6:44PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879014','May 29 2014 6:43PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879015','May 29 2014 7:54PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879017','May 29 2014 7:57PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879018','May 29 2014 7:56PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879019','May 29 2014 8:00PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879020','May 29 2014 7:59PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879021','May 29 2014 8:03PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879022','May 29 2014 8:01PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879023','May 29 2014 8:07PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879024','May 29 2014 8:05PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879025','May 29 2014 8:09PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879026','May 29 2014 8:08PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879027','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879028','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879029','May 29 2014 8:16PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM' UNION ALL

  SELECT 'Roger Willet','05201417879030','May 29 2014 8:14PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM' UNION ALL

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 10, 2014 at 11:44 am

  #1728474

  kabaari (7/10/2014)

  ---

  Lynn,

  Will this work? I really appreciate the assistance!!!

  CREATE TABLE

  [dbo].[Test_completedUnit](

  [unit] [nvarchar](4) NOT NULL,

  [operator] [nvarchar](50) NULL,

  [SN] [varchar](50) PRIMARY KEY NOT NULL,

  [Workorder][varchar](7)NOT NULL

  )

  INSERT INTO test_completedUnit

  (unit, workorder, operator, sn)

  SELECT '01','17879','VERHEY, CHARLES','05201417879001' UNION ALL

  SELECT '02','17879','VERHEY, CHARLES','05201417879002' UNION ALL

  SELECT '03','17879','VERHEY, CHARLES','05201417879003' UNION ALL

  SELECT '04','17879','VERHEY, CHARLES','05201417879004' UNION ALL

  SELECT '05','17879','VERHEY, CHARLES','05201417879005' UNION ALL

  SELECT '06','17879','VERHEY, CHARLES','05201417879006' UNION ALL

  SELECT '07','17879','VERHEY, CHARLES','05201417879007' UNION ALL

  SELECT '08','17879','VERHEY, CHARLES','05201417879008' UNION ALL

  SELECT '09','17879','VERHEY, CHARLES','05201417879009' UNION ALL

  SELECT '10','17879','VERHEY, CHARLES','05201417879010' UNION ALL

  SELECT '11','17879','VERHEY, CHARLES','05201417879011' UNION ALL

  SELECT '12','17879','VERHEY, CHARLES','05201417879012' UNION ALL

  SELECT '13','17879','VERHEY, CHARLES','05201417879013' UNION ALL

  SELECT '14','17879','VERHEY, CHARLES','05201417879014' UNION ALL

  SELECT '15','17879','VERHEY, CHARLES','05201417879015' UNION ALL

  SELECT '16','17879','VERHEY, CHARLES','05201417879016' UNION ALL

  SELECT '17','17879','VERHEY, CHARLES','05201417879017' UNION ALL

  SELECT '18','17879','VERHEY, CHARLES','05201417879018' UNION ALL

  SELECT '19','17879','VERHEY, CHARLES','05201417879019' UNION ALL

  SELECT '20','17879','VERHEY, CHARLES','05201417879020' UNION ALL

  SELECT '21','17879','VERHEY, CHARLES','05201417879021' UNION ALL

  SELECT '22','17879','VERHEY, CHARLES','05201417879022' UNION ALL

  SELECT '23','17879','VERHEY, CHARLES','05201417879023' UNION ALL

  SELECT '24','17879','VERHEY, CHARLES','05201417879024' UNION ALL

  SELECT '25','17879','VERHEY, CHARLES','05201417879025' UNION ALL

  SELECT '26','17879','VERHEY, CHARLES','05201417879026' UNION ALL

  SELECT '27','17879','VERHEY, CHARLES','05201417879027' UNION ALL

  SELECT '28','17879','VERHEY, CHARLES','05201417879028' UNION ALL

  SELECT '29','17879','VERHEY, CHARLES','05201417879029' UNION ALL

  SELECT '30','17879','VERHEY, CHARLES','05201417879030' UNION ALL

  CREATE TABLE [dbo].[Test_assemblyQC](

  [SN] [nvarchar](16) NOT NULL,

  [qcOperator] [nvarchar](20) NOT NULL,

  [dateinspectedFP] [datetime] NULL,

  [grFPDate] [datetime] NULL,

  [hsOperator] [varchar](50) NULL,

  [haOperator] [varchar](30) NULL,

  [hadateFP] [datetime] NULL)

  INSERT INTO Test_assemblyQC

  (QCOperator, SN, grfpdate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  SELECT 'Roger Willet','05201417879003','May 29 2014 6:26PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:08AM' UNION ALL

  SELECT 'Roger Willet','05201417879005','May 29 2014 6:31PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879006','May 29 2014 6:28PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879007','May 29 2014 6:33PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879008','May 29 2014 6:35PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879009','May 29 2014 6:38PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879010','May 29 2014 6:37PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879011','May 29 2014 6:41PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879012','May 29 2014 6:39PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879013','May 29 2014 6:44PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879014','May 29 2014 6:43PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879015','May 29 2014 7:54PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879017','May 29 2014 7:57PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879018','May 29 2014 7:56PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879019','May 29 2014 8:00PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879020','May 29 2014 7:59PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879021','May 29 2014 8:03PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879022','May 29 2014 8:01PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879023','May 29 2014 8:07PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879024','May 29 2014 8:05PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879025','May 29 2014 8:09PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879026','May 29 2014 8:08PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879027','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879028','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879029','May 29 2014 8:16PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM' UNION ALL

  SELECT 'Roger Willet','05201417879030','May 29 2014 8:14PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM' UNION ALL

  Have you tested the code you posted in an empty database? Just looking at the first CREATE TABLE/INSERT INTO group, I don't see it working.

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 10, 2014 at 11:56 am

  #1728479

  Lynn,

  Thanks! Problem found, tested and resolved.

  CREATE TABLE

  [dbo].[Test_completedUnit](

  [unit] [nvarchar](4) NOT NULL,

  [operator] [nvarchar](50) NULL,

  [SN] [varchar](50) PRIMARY KEY NOT NULL,

  [Workorder][varchar](7)NOT NULL

  )

  INSERT INTO test_completedUnit

  (unit, workorder, operator, sn)

  SELECT '01','17879','VERHEY, CHARLES','05201417879001' UNION ALL

  SELECT '02','17879','VERHEY, CHARLES','05201417879002' UNION ALL

  SELECT '03','17879','VERHEY, CHARLES','05201417879003' UNION ALL

  SELECT '04','17879','VERHEY, CHARLES','05201417879004' UNION ALL

  SELECT '05','17879','VERHEY, CHARLES','05201417879005' UNION ALL

  SELECT '06','17879','VERHEY, CHARLES','05201417879006' UNION ALL

  SELECT '07','17879','VERHEY, CHARLES','05201417879007' UNION ALL

  SELECT '08','17879','VERHEY, CHARLES','05201417879008' UNION ALL

  SELECT '09','17879','VERHEY, CHARLES','05201417879009' UNION ALL

  SELECT '10','17879','VERHEY, CHARLES','05201417879010' UNION ALL

  SELECT '11','17879','VERHEY, CHARLES','05201417879011' UNION ALL

  SELECT '12','17879','VERHEY, CHARLES','05201417879012' UNION ALL

  SELECT '13','17879','VERHEY, CHARLES','05201417879013' UNION ALL

  SELECT '14','17879','VERHEY, CHARLES','05201417879014' UNION ALL

  SELECT '15','17879','VERHEY, CHARLES','05201417879015' UNION ALL

  SELECT '16','17879','VERHEY, CHARLES','05201417879016' UNION ALL

  SELECT '17','17879','VERHEY, CHARLES','05201417879017' UNION ALL

  SELECT '18','17879','VERHEY, CHARLES','05201417879018' UNION ALL

  SELECT '19','17879','VERHEY, CHARLES','05201417879019' UNION ALL

  SELECT '20','17879','VERHEY, CHARLES','05201417879020' UNION ALL

  SELECT '21','17879','VERHEY, CHARLES','05201417879021' UNION ALL

  SELECT '22','17879','VERHEY, CHARLES','05201417879022' UNION ALL

  SELECT '23','17879','VERHEY, CHARLES','05201417879023' UNION ALL

  SELECT '24','17879','VERHEY, CHARLES','05201417879024' UNION ALL

  SELECT '25','17879','VERHEY, CHARLES','05201417879025' UNION ALL

  SELECT '26','17879','VERHEY, CHARLES','05201417879026' UNION ALL

  SELECT '27','17879','VERHEY, CHARLES','05201417879027' UNION ALL

  SELECT '28','17879','VERHEY, CHARLES','05201417879028' UNION ALL

  SELECT '29','17879','VERHEY, CHARLES','05201417879029' UNION ALL

  SELECT '30','17879','VERHEY, CHARLES','05201417879030'

  CREATE TABLE [dbo].[Test_assemblyQC](

  [SN] [nvarchar](16) NOT NULL,

  [qcOperator] [nvarchar](20) NOT NULL,

  [dateinspectedFP] [datetime] NULL,

  [grFPDate] [datetime] NULL,

  [hsOperator] [varchar](50) NULL,

  [haOperator] [varchar](30) NULL,

  [hadateFP] [datetime] NULL)

  INSERT INTO Test_assemblyQC

  (QCOperator, SN, grfpdate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  SELECT 'Roger Willet','05201417879003','May 29 2014 6:26PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:08AM' UNION ALL

  SELECT 'Roger Willet','05201417879005','May 29 2014 6:31PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879006','May 29 2014 6:28PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879007','May 29 2014 6:33PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879008','May 29 2014 6:35PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:13AM' UNION ALL

  SELECT 'Roger Willet','05201417879009','May 29 2014 6:38PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879010','May 29 2014 6:37PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:14AM' UNION ALL

  SELECT 'Roger Willet','05201417879011','May 29 2014 6:41PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879012','May 29 2014 6:39PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879013','May 29 2014 6:44PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879014','May 29 2014 6:43PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879015','May 29 2014 7:54PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:19AM' UNION ALL

  SELECT 'Roger Willet','05201417879017','May 29 2014 7:57PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879018','May 29 2014 7:56PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879019','May 29 2014 8:00PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879020','May 29 2014 7:59PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:24AM' UNION ALL

  SELECT 'Roger Willet','05201417879021','May 29 2014 8:03PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879022','May 29 2014 8:01PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:25AM' UNION ALL

  SELECT 'Roger Willet','05201417879023','May 29 2014 8:07PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879024','May 29 2014 8:05PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:29AM' UNION ALL

  SELECT 'Roger Willet','05201417879025','May 29 2014 8:09PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879026','May 29 2014 8:08PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879027','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879028','May 29 2014 8:12PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:30AM' UNION ALL

  SELECT 'Roger Willet','05201417879029','May 29 2014 8:16PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM' UNION ALL

  SELECT 'Roger Willet','05201417879030','May 29 2014 8:14PM','Roger Snider','May 30 2014 12:00AM','Roger Snider','May 30 2014 6:39AM'

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 10, 2014 at 2:50 pm

  #1728525

  Got it!!!!! Returns the results I want but performance issues. Will create view.

  select c.operator, c.unit, c.unitcompleted,

  (select s.qcOperator from assemblyQC s where s.sn = c.sn) as GR_Operator,

  (select s.grFPDate from assemblyQC s where s.sn = c.sn) as GR_Date,

  (select s.hsOperator from assemblyQC s where s.sn = c.sn) as HS_Operator,

  (select s.dateInspectedFP from assemblyQC s where s.sn = c.sn) as HS_Date,

  (select s.haOperator from assemblyQC s where s.sn = c.sn) as HA_Operator,

  (select s.haDateFP from assemblyQC s where s.sn = c.sn) as HA_Date,

  case when exists (select s.sn from assemblyqc s where c.sn = s.sn) then 'Scanned' else 'Not Scanned' end as Inspected

  from completedUnit c

  where operator = 'builder' and workorder = '17879'

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 10, 2014 at 4:11 pm

  #1728550

  Hadn't had a chance to work your problem before you posted your solution, but having found a little time I was able to modify your solution. I also had to modify yours to use the test tables you posted. One suggestion, be consistent in your coding. If you declare a column in your table as SN for example, don't refer to it in your code as sn. This works fine in a system using a case insensitive collation but it doesn't work if you port the tables and code as is to a case sensitive collation (which is what my development system is setup to use). Plus it looks messy.

  Here is the code, your solution and mine. I have also uploaded the execution plans for the code.

  select

  c.operator,

  c.unit,

  --c.unitcompleted, -- No column named this in the DDL provided

  --(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  (select s.qcOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Operator,

  (select s.grFPDate from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Date,

  (select s.hsOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Operator,

  (select s.dateinspectedFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Date,

  (select s.haOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Operator,

  (select s.hadateFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Date,

  case when exists (select s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.Test_completedUnit c

  where

  c.operator = 'VERHEY, CHARLES' and

  c.Workorder = '17879';

  select

  c.operator,

  c.unit,

  --c.unitcompleted, -- No column named this in the DDL provided

  --(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  GR_Operator,

  GR_Date,

  HS_Operator,

  HS_Date,

  HA_Operator,

  HA_Date,

  case when oa.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.Test_completedUnit c

  OUTER APPLY(select s.qcOperator, s.grFPDate, s.haOperator, s.hadateFP, s.hsOperator, s.dateinspectedFP, s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) oa (GR_Operator,

  GR_Date,

  HA_Operator,

  HA_Date,

  HS_Operator,

  HS_Date,

  SN)

  where

  c.operator = 'VERHEY, CHARLES' and

  c.Workorder = '17879';

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 10, 2014 at 4:21 pm

  #1728552

  Another issue that may affect performance, a data type mismatch. In the DDL you provided SN is declared differently between the two tables:

  [dbo].[Test_completedUnit]

  [SN] [varchar](50) PRIMARY KEY NOT NULL,

  [dbo].[Test_assemblyQC]

  [SN] [nvarchar](16) NOT NULL,

  In the first table it is declared as varchar(50) and the second nvarchar(16). This results in an implicit data conversion to nvarchar. Any indexes on the SN column will be ignored.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 10, 2014 at 7:50 pm

  #1728578

  I was also able to duplicate your results using a LEFT OUT JOIN. Interesting results in the execution plan.

  Code first:

  select

  c.operator,

  c.unit,

  --c.unitcompleted, -- No column named this in the DDL provided

  --(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  (select s.qcOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Operator,

  (select s.grFPDate from dbo.Test_assemblyQC s where s.SN = c.SN) as GR_Date,

  (select s.hsOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Operator,

  (select s.dateinspectedFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HS_Date,

  (select s.haOperator from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Operator,

  (select s.hadateFP from dbo.Test_assemblyQC s where s.SN = c.SN) as HA_Date,

  case when exists (select s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.Test_completedUnit c

  where

  c.operator = 'VERHEY, CHARLES' and

  c.Workorder = '17879';

  select

  c.operator,

  c.unit,

  --c.unitcompleted, -- No column named this in the DDL provided

  --(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  GR_Operator,

  GR_Date,

  HS_Operator,

  HS_Date,

  HA_Operator,

  HA_Date,

  case when oa.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.Test_completedUnit c

  OUTER APPLY(select s.qcOperator, s.grFPDate, s.haOperator, s.hadateFP, s.hsOperator, s.dateinspectedFP, s.SN from dbo.Test_assemblyQC s where s.SN = c.SN) oa (GR_Operator,

  GR_Date,

  HA_Operator,

  HA_Date,

  HS_Operator,

  HS_Date,

  SN)

  where

  c.operator = 'VERHEY, CHARLES' and

  c.Workorder = '17879';

  select

  c.operator,

  c.unit,

  --c.unitcompleted, -- No column named this in the DDL provided

  --(qcOperator, SN, grFPDate, haOperator, hadateFP, hsOperator, dateinspectedFP)

  s.qcOperator GR_Operator,

  s.grFPDate GR_Date,

  s.hsOperator HS_Operator,

  s.dateinspectedFP HS_Date,

  s.haOperator HA_Operator,

  s.hadateFP HA_Date,

  case when s.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.Test_completedUnit c

  left outer join dbo.Test_assemblyQC s

  on (s.SN = c.SN)

  where

  c.operator = 'VERHEY, CHARLES' and

  c.Workorder = '17879';

  Also, I modified the test tables so that SN had the same data definition between the tables, nvarchar(16).

  I ran the above code twice, once with no index on the SN column for table [dbo].[Test_assemblyQC] and then again after creating a clustered index on the SN column on the table.

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 11, 2014 at 11:17 am

  #1728841

  Yes!!!! I ran this and returned 116207 rows in 00:00:17. Are you open to connect on LinkedIN? BTW, Thanks!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 11, 2014 at 12:12 pm

  #1728857

  kabaari (7/11/2014)

  ---

  Yes!!!! I ran this and returned 116207 rows in 00:00:17. Are you open to connect on LinkedIN? BTW, Thanks!

  Glad to see it worked. Just for clarity, what was the final solution you implemented?

• kabaari

  SSCrazy

  Points: 2235

  More actions

  July 11, 2014 at 12:19 pm

  #1728859

  select

  c.operator,

  c.unit,

  c.unitcompleted,

  GR_Operator,

  GR_Date,

  HS_Operator,

  HS_Date,

  HA_Operator,

  HA_Date,

  case when oa.SN is not null then 'Scanned' else 'Not Scanned' end as Inspected

  from

  dbo.completedUnit c

  OUTER APPLY(select s.qcOperator, s.grFPDate, s.haOperator, s.hadateFP, s.hsOperator, s.dateinspectedFP, s.SN from dbo.assemblyQC s where s.SN = c.SN) oa (GR_Operator,

  GR_Date,

  HA_Operator,

  HA_Date,

  HS_Operator,

  HS_Date,

  SN)