common table expression - SQL: How to Remove record from CTE if another result set had record for that id -

common table expression - SQL: How to Remove record from CTE if another result set had record for that id -

a part can used in service entry , if used service entry part table have record of that.

in next part never used in service entry display applicable part scheme default config. when study run, servicedate between 10/1/2014 - 10/2/2014

instrument part number part description revision number date xnumber icq ia ep5 152970 rsh-load platform-arm (spring), 100 icq ia ep6 152970 rsh-load platform-arm (spring), 100 icq ia ep7 152970 rsh-load platform-arm (spring), 100

in next 10/1/2014 - 10/6/2014 part used in service entry scheme 6 , 7 there service date/time include revision number service entry part table , since on scheme 5 not used display default config part table.

instrument part number part description revision number date xnumber icq ia ep5 152970 rsh-load platform-arm (spring), 100 icq ia ep6 152970 rsh-load platform-arm (spring), 101 10/05/2014 icq ia ep7 152970 rsh-load platform-arm (spring), 101 10/05/2014

now if run study date ranges supplied query 10/1/2014 - 10/14/2014, can see scheme 6 had service entry on 10/05. since scheme 5 never had service entry still display default revision number part table.

instrument part number part description revision number date xnumber icq ia ep5 152970 rsh-load platform-arm (spring), 100 icq ia ep6 152970 rsh-load platform-arm (spring), 101 10/05/2014 icq ia ep6 152970 rsh-load platform-arm (spring), 101 10/10/2014 icq ia ep7 152970 rsh-load platform-arm (spring), 101 10/05/2014

so wrote next cte gives me every part applicable on scheme , revision number part table.

declare @run_log table( [instrument] nvarchar(max), [subsystem] nvarchar(max), [abbottpartnumber] nvarchar(max), [rslmspartid] int, [partdescription] nvarchar(max), [partrevisionnumber] nvarchar(max), --[serviceentryid] int, [date] datetime, [tsbnumber] nvarchar (max), [crnumber] nvarchar(max) ) insert @run_log select distinct system.systemfullname instrument, part.system subsystem, part.id partid, part.partdescription, part.revisionnumber partrevisionnumber, --null serviceentryid, null date, null tsbnumber, null crnumber part inner bring together instrumenttype on part.instrumenttypeid = instrumenttype.id inner bring together systemmodule on systemmodule.instrumenttypeid = part.instrumenttypeid inner bring together scheme on system.id = systemmodule.systemid system.platformid = 2 , part.active = 1 , instrumenttype.active = 1 , part.id = 152970 , part.parentid > 0 ;with runlogs as( select * @run_log r )

the next query returns part applicable on scheme if have record in service entry part table. if union results cte result set close desired study output trying if part has record in next query, need remove part record cte above

-- parts installed in entries inlc baseline and/or user selected select system.systemfullname instrument, part.system subsystem, part.id partid, part.partdescription, coalesce(serviceentrypart.partrevisionnumber,part.revisionnumber) partrevisionnumber, --serviceentrypart.serviceentryid, serviceentry.servicedatetime date, serviceentry.tsbnumber, serviceentry.crnumber part inner bring together serviceentrypart on serviceentrypart.partid = part.id inner bring together serviceentry on serviceentry.id = serviceentrypart.serviceentryid inner bring together systemmodule on serviceentrypart.systemmoduleid = systemmodule.id inner bring together scheme on system.id = systemmodule.systemid cross apply dbo.splitstrings_moden(serviceentrypart.servicetypeids, n',') m2 bring together dbo.splitstrings_moden('1', n',') p on (m2.item = p.item or '1'is null) system.platformid = 2 , part.active = 1 , part.parentid > 0 , part.id = 152970

edit

if union all, between cte , sec query results follows, close need rows number 7 , 9 should not displayed because cte , dont need them because parts used in service entry-service entry part, essentialy parts returned in sec query, if referenced in cte, need not inlcuded in results.

instrument subsystem rslmspartid partdescription partrevisionnumber date tsbnumber crnumber icq ia ep00 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep1 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep2 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep3 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep4 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep5 - dallas rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep6 - lake county rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep6 - lake county rsh 152970 arm (spring), tray/carrier observe null 2014-10-09 17:35:00.000 null null icq ia ep7 - wiesbaden rsh 152970 arm (spring), tray/carrier observe null null null null icq ia ep7 - wiesbaden rsh 152970 arm (spring), tray/carrier observe null 2014-10-09 17:10:00.000 null null icq ia eptraining - training rsh 152970 arm (spring), tray/carrier observe null null null null

add query of records exclude cte , utilize not exists() exclude records.

;with toexclude as( -- parts installed in entries inlc baseline and/or user selected select system.systemfullname instrument, part.system subsystem, part.id partid, part.partdescription, coalesce(serviceentrypart.partrevisionnumber,part.revisionnumber) partrevisionnumber, --serviceentrypart.serviceentryid, serviceentry.servicedatetime date, serviceentry.tsbnumber, serviceentry.crnumber part inner bring together serviceentrypart on serviceentrypart.partid = part.id inner bring together serviceentry on serviceentry.id = serviceentrypart.serviceentryid inner bring together systemmodule on serviceentrypart.systemmoduleid = systemmodule.id inner bring together scheme on system.id = systemmodule.systemid cross apply dbo.splitstrings_moden(serviceentrypart.servicetypeids, n',') m2 bring together dbo.splitstrings_moden('1', n',') p on (m2.item = p.item or '1'is null) system.platformid = 2 , part.active = 1 , part.parentid > 0 , part.id = 152970 ) , runlogs as( select * @run_log r not exists ( select * toexclude e r.instrument = e.instrument , r.subsystem = e.subsystem -- go on identity columns ) ) -- utilize cte here, e.g. select * runlogs;

you accomplish same thing few other ways, e.g. add together not exists or appropriate joins main query.

sql common-table-expression