SQLTeam.com | Weblogs | Forums

Find Orphaned Accessory Items on Live Contracts

sql2012

#1

I am trying to find any items on a hire contract that are
accessories (belong to specific subgroups) which are still showing as on-hire
(status 1), where there are no other items on-hire which are not part of the
accessories subgroups.

My basic select statement is as follows:

SELECT CONTNO, SUBGRP, ITEMNO, STATUS

FROM contitems

WHERE STATUS = 1 AND SUBGRP in ('1010/ACC','1040/ACC','0001/Keys')

But this is not sufficient as it does not tell me if there
are items from other subgroups at status 1 on the contracts returned – in which
case I would not want to see them.

So in these examples, I would want to see the
following as items in other subgroups are not at status 1:

CONTNO SUBGRP ITEMNO STATUS

0000300057 1040/140 5620A 2

0000300057 0001/Keys Keys 1

CONTNO SUBGRP ITEMNO STATUS

0000300477 1040/140 5522A 2

0000300477 0001/Keys Keys 1

0000300477 1040/ACC 714B 1

But I would not want to see the following as the items in other
subgroups are at status 1:

CONTNO SUBGRP ITEMNO STATUS

0000300073 0001/Keys Keys 1

0000300073 1040/140 5374A 1

I how this makes sense.