CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlselect
AToya
AToya

Reputation: 575

SQL: Removing rows from Select Statement on Insert

I have a stored procedure that returns a table of items that are missing for a specified ID. For example: exec getMissingItems '1' will return a list of the items that are missing for ID = 1 such as : 'A', 'B', 'C'. Now I am tracking when these items are received so they are stored into the database as 'A Received' & B received and so forth. I want to be able to only show the items that have not yet been received, for instance if I call exec getMissingItems '1' now, it will only return 'C'.

All of the information is being stored into a database table

TABLE1
ID |  Event
1  | A Missing
1  | B Missing
1  | C Missing
1  | A Received
1  | B Received

So currently getMissingItems is simply calling:

SELECT Event FROM TABLE1 WHERE Event LIKE '%Missing'

Which returns a table of the items missing, but still returns them even if they are missing

RETURNED TABLE
   Event
A Missing
B Missing
C Missing

Upvotes: 1

Views: 91

Answers (2)

logixologist
logixologist

Reputation: 3834

Edited answer

See if this works a little better for you...

CREATE TABLE #temp (id, event, missing, recieved)
INSERT INTO #temp 
SELECT Id, Event, case when event like '%missing%' then 1 else 0 END 
CASE WHEN event like '%recieved%' then 1 else 0
 FROM TABLE1 

SELECT Event from Table1 t join #temp tt on t.id = tt.id
WHERE missing =1 and recieved = 0

Upvotes: 0

Charles
Charles

Reputation: 342

This should work for you. You need to left join based on the ID and the parsed identifier from the event. Then find the rows that are unmatched that have "Missing" in the event.

Here is a SQL fiddle link to this example -- http://sqlfiddle.com/#!3/2d668/1/0 

create table #table1
(
    ID int,
    [Event] varchar(100)
);

go

insert into #table1 values (1, 'A Missing');
insert into #table1 values (1, 'B Missing');
insert into #table1 values (1, 'C Missing');
insert into #table1 values (1, 'A Received');
insert into #table1 values (1, 'B Received');

go

with cte as
(
    select id, [Event], substring([Event], 1, patindex('% %', [Event]) -1) as ItemId
    from #table1
)

select a.Event
from cte a
    left join cte b on 
        a.id = b.id and                 -- IDs must match
        a.ItemId = b.ItemId and         -- the ItemId from the parsed Event must match on the left side
        a.Event like '%Missing' and     -- items that match have a 'Missing' on the "left"
        b.Event like '%Received'        -- items that match have a 'Received' on the "right"
where b.ID is null                      -- rows that did not match on the right
and a.Event like '%Missing'             -- row has missing in the event on the left side


drop table #table1;

go

Upvotes: 3

Related Questions