Test for Two Values within a Range 1

[jw45]

From sample the data below, I need to return the 'Grp' if any value in cItem contains '4' in the 3rd position and a citem begins with '120' within that group.


Grp Item Date cItem

1 123 5/15/00 014236
1 235 9/19/03 120236
1 173 2/23/01 014998
1 126 8/13/02 024329
1 143 1/19/06 014236
1 123 6/16/06 014236
2 349 3/13/04 014565
2 665 7/10/99 014789
2 723 6/06/05 014236
2 735 4/29/05 024236
2 123 2/21/00 014236
3 343 9/11/03 024236
3 199 4/20/02 120236
3 829 2/06/06 014236
4 893 6/20/02 120211
4 345 3/03/02 120236


In the data above I would want to return Grp 1 & 3.

Thanks,
JW

 

[r937]

select Grp
  from daTable
group
    by Grp  
having sum(iif(mid(cItem,3,1)='4',1,0))>0
   and sum(iif(left(cItem,3)='120',1,0))>0

r937.com | rudy.ca

 

[jw45]

Perfect...Many thanks!