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.