SQLTeam.com | Weblogs | Forums

Missing Records - SQL Help


#1

Hello All,
We have a table named TriageCheckList that saves records for a request and the list of items that are needed to process the request . There was an issue of some sort this week and there are now requests that are saved In the table for which an item with id 9 has missed

We want to identify all requests where a request has the itemnumber 9 missing so we can manually enter it into the table so the users don't face issues at the interface table

TriageCheckList Table Structure
ReqNo
CheckListItemNo
CompletedDt

Sample Data for a request without issue
123456,1,01/01/2018
123456,2,01/11/2018
123456,3,01/21/2018
123456,4,01/31/2018
123456,5,01/17/2018
123456,6,01/01/2018
123456,7,01/01/2018
123456,8,01/01/2018
123456,9,01/19/2018
123456,10,01/01/2018

Sample Data for a request with issue
123457,1,01/01/2018
123457,2,01/11/2018
123457,3,01/21/2018
123457,4,01/31/2018
123457,5,01/17/2018
123457,6,01/01/2018
123457,7,01/01/2018
123457,8,01/01/2018
123457,10,01/01/2018

How can we identify all requests within this table for which item number 9 is missing

Thanks in advance


#2

There are few ways to complete this :
here is one :

CREATE TABLE TriageCheckList
    ([ReqNo] int, [CheckListItemNo] int, [CompletedDt] datetime)
;
    
INSERT INTO TriageCheckList
    ([ReqNo], [CheckListItemNo], [CompletedDt])
VALUES
    (123456, 1, '2018-01-01 00:00:00'),
    (123456, 2, '2018-01-11 00:00:00'),
    (123456, 3, '2018-01-21 00:00:00'),
    (123456, 4, '2018-01-31 00:00:00'),
    (123456, 5, '2018-01-17 00:00:00'),
    (123456, 6, '2018-01-01 00:00:00'),
    (123456, 7, '2018-01-01 00:00:00'),
    (123456, 8, '2018-01-01 00:00:00'),
    (123456, 9, '2018-01-19 00:00:00'),
    (123456, 10, '2018-01-01 00:00:00'),
    (123457, 1, '2018-01-01 00:00:00'),
    (123457, 2, '2018-01-11 00:00:00'),
    (123457, 3, '2018-01-21 00:00:00'),
    (123457, 4, '2018-01-31 00:00:00'),
    (123457, 5, '2018-01-17 00:00:00'),
    (123457, 6, '2018-01-01 00:00:00'),
    (123457, 7, '2018-01-01 00:00:00'),
    (123457, 8, '2018-01-01 00:00:00'),
    (123457, 10, '2018-01-01 00:00:00')
;

SELECT [ReqNo]
FROM TriageCheckList

Except

SELECT ReqNo
FROM TriageCheckList
WHERE CheckListItemNo = 9;

Output:

ReqNo
123457

Dbfiddle here


#3

Hi

Another WAY to do this

We can find both left side right side not there

I put both the data into one table

Create Data Script
drop table #TriageCheckList 
go 

create table #TriageCheckList
(
ReqNo int null,
CheckListItemNo int null,
CompletedDt date
)
go 

insert into #TriageCheckList select 123456,1,'01/01/2018'
insert into #TriageCheckList select 123456,2,'01/11/2018'
insert into #TriageCheckList select 123456,3,'01/21/2018'
insert into #TriageCheckList select 123456,4,'01/31/2018'
insert into #TriageCheckList select 123456,5,'01/17/2018'
insert into #TriageCheckList select 123456,6,'01/01/2018'
insert into #TriageCheckList select 123456,7,'01/01/2018'
insert into #TriageCheckList select 123456,8,'01/01/2018'
insert into #TriageCheckList select 123456,9,'01/19/2018'
insert into #TriageCheckList select 123456,10,'01/01/2018'
insert into #TriageCheckList select 123457,1,'01/01/2018'
insert into #TriageCheckList select 123457,2,'01/11/2018'
insert into #TriageCheckList select 123457,3,'01/21/2018'
insert into #TriageCheckList select 123457,4,'01/31/2018'
insert into #TriageCheckList select 123457,5,'01/17/2018'
insert into #TriageCheckList select 123457,6,'01/01/2018'
insert into #TriageCheckList select 123457,7,'01/01/2018'
insert into #TriageCheckList select 123457,8,'01/01/2018'
insert into #TriageCheckList select 123457,10,'01/01/2018'
go
SQL
SELECT * 
FROM   (SELECT * 
        FROM   #triagechecklist 
        WHERE  reqno = 123456) a 
       FULL OUTER JOIN (SELECT * 
                        FROM   #triagechecklist 
                        WHERE  reqno = 123457) b 
                    ON a.checklistitemno = b.checklistitemno 
WHERE  a.checklistitemno IS NULL 
        OR b.checklistitemno IS NULL 

go