Re: Help Needed
From: Pankaj <harpreet.noni_at_gmail.com>
Date: Wed, 11 Nov 2009 16:42:15 -0800 (PST)
Message-ID: <eecda6d7-1c38-43ec-9d6b-663cbb834a80_at_j35g2000vbl.googlegroups.com>
On Nov 10, 8:16�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> Pankaj says...
>
>
>
>
>
> > On Nov 10, 6:34�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> > wrote:
> > > In article <dc224e63-918d-4755-a6c5-bfa67fa035b9
> > > _at_h2g2000vbd.googlegroups.com>, Pankaj says...
>
> > > > On Nov 10, 5:28�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> > > > wrote:
> > > > > Pankaj says...
>
> > > > > > I have this enhancement where I have to EXCLUDE all users with roles <
> > > > > > 1000 but keeping following in mind
> > > > > > 1) role < 1000 and user should not be in any other role
> > > > > > OR
> > > > > > 2) role < 1000 and role BETWEEN 9000 and 9010 and user should not be
> > > > > > in any other role.
>
> > > > > These two conditions seem to clash with each other, everyone that meets
> > > > > the second criteria seem to have been covered by the first one.
>
> > > > > GM
>
> > > > Thanks Geoff. But user Moore becomes eligible for the first
> > > > requirement (his role < 1000 and does not have any other role). and
> > > > not for second (role < 1000 and does not have any role between 9000
> > > > and 9010).
>
> > > Moore currently only has a role of 45, so yes, he is eligible under the
> > > first requirement.
>
> > > If Moore also had a role of 9001, he would NOT be eligible under the FIRST
> > > requirement (even though he would be eligible under the second
> > > requirement) because the roles 9000-9010 are still part of "any other
> > > role".
>
> > > What are you trying to actually get? �Users that have roles that are only
> > > between 0-1000 and 9000-9010?
>
> > > GM- Hide quoted text -
>
> > > - Show quoted text -
>
> > Geoff,
>
> > Thats exactly what seems to be the dilemma here. I would explain
> > again. We have set of users
>
> > 1) with ONLY role < 1000
> > 2) with ONLY (role < 1000 and BETWEEN 9000 and 9010)
>
> > The requirement is to remove the users falling in above requirement,
> > so following users would be excluded
>
> > Should be EXCLUDED
>
> > role � User
> > 23 � � James
> > 24 � � James
> > 90 � � James
> > 23 � � Scott
> > 9001 Scott
>
> > But following user's should NOT BE EXCLUDED as they also belong to
> > other roles apart from what has been described in above two points.
>
> > Should be INCLUDED
>
> > role � � User
> > 23 � � � James1
> > 1002 � James1 � �--Should be included as it belongs to role other than
> > < 1000 and between 9000 and 9010
> > 9000 � James1
> > 9000 � Scott1 � � -- Should be included as it is only part of between
> > 9000 and 9010 and is not < 1000
> > 1000 � Brian1
> > 1006 � Brian2
>
> > I hope I was able to make myself more clearer.
>
> Create a sub-query:
>
> select user,
> � � sum(case when <less than 1000 condition>
> � � � � � � � � �then 1 else 0
> � � � � end) as count_under_1000,
> � � sum(case when <between 9000 and 9010 condition>
> � � � � � � � � �then 1 else 0
> � � � � end) as count_special_val,
> � � count(role) as count_all
> from <table>
> group by user
>
> Then select the records from this sub-query to match your logic based on
> the numbers in those calculated value columns.
>
> GM
>
> GM- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 11 Nov 2009 16:42:15 -0800 (PST)
Message-ID: <eecda6d7-1c38-43ec-9d6b-663cbb834a80_at_j35g2000vbl.googlegroups.com>
On Nov 10, 8:16�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com> wrote:
> Pankaj says...
>
>
>
>
>
> > On Nov 10, 6:34�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> > wrote:
> > > In article <dc224e63-918d-4755-a6c5-bfa67fa035b9
> > > _at_h2g2000vbd.googlegroups.com>, Pankaj says...
>
> > > > On Nov 10, 5:28�pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> > > > wrote:
> > > > > Pankaj says...
>
> > > > > > I have this enhancement where I have to EXCLUDE all users with roles <
> > > > > > 1000 but keeping following in mind
> > > > > > 1) role < 1000 and user should not be in any other role
> > > > > > OR
> > > > > > 2) role < 1000 and role BETWEEN 9000 and 9010 and user should not be
> > > > > > in any other role.
>
> > > > > These two conditions seem to clash with each other, everyone that meets
> > > > > the second criteria seem to have been covered by the first one.
>
> > > > > GM
>
> > > > Thanks Geoff. But user Moore becomes eligible for the first
> > > > requirement (his role < 1000 and does not have any other role). and
> > > > not for second (role < 1000 and does not have any role between 9000
> > > > and 9010).
>
> > > Moore currently only has a role of 45, so yes, he is eligible under the
> > > first requirement.
>
> > > If Moore also had a role of 9001, he would NOT be eligible under the FIRST
> > > requirement (even though he would be eligible under the second
> > > requirement) because the roles 9000-9010 are still part of "any other
> > > role".
>
> > > What are you trying to actually get? �Users that have roles that are only
> > > between 0-1000 and 9000-9010?
>
> > > GM- Hide quoted text -
>
> > > - Show quoted text -
>
> > Geoff,
>
> > Thats exactly what seems to be the dilemma here. I would explain
> > again. We have set of users
>
> > 1) with ONLY role < 1000
> > 2) with ONLY (role < 1000 and BETWEEN 9000 and 9010)
>
> > The requirement is to remove the users falling in above requirement,
> > so following users would be excluded
>
> > Should be EXCLUDED
>
> > role � User
> > 23 � � James
> > 24 � � James
> > 90 � � James
> > 23 � � Scott
> > 9001 Scott
>
> > But following user's should NOT BE EXCLUDED as they also belong to
> > other roles apart from what has been described in above two points.
>
> > Should be INCLUDED
>
> > role � � User
> > 23 � � � James1
> > 1002 � James1 � �--Should be included as it belongs to role other than
> > < 1000 and between 9000 and 9010
> > 9000 � James1
> > 9000 � Scott1 � � -- Should be included as it is only part of between
> > 9000 and 9010 and is not < 1000
> > 1000 � Brian1
> > 1006 � Brian2
>
> > I hope I was able to make myself more clearer.
>
> Create a sub-query:
>
> select user,
> � � sum(case when <less than 1000 condition>
> � � � � � � � � �then 1 else 0
> � � � � end) as count_under_1000,
> � � sum(case when <between 9000 and 9010 condition>
> � � � � � � � � �then 1 else 0
> � � � � end) as count_special_val,
> � � count(role) as count_all
> from <table>
> group by user
>
> Then select the records from this sub-query to match your logic based on
> the numbers in those calculated value columns.
>
> GM
>
> GM- Hide quoted text -
>
> - Show quoted text -
Geoff, this worked. Thanks a lot. Take care Received on Wed Nov 11 2009 - 18:42:15 CST