Re: Help Needed

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Wed, 11 Nov 2009 12:16:46 +1100
Message-ID: <MPG.2564bb8cdf6ca0a198979f_at_news.x-privat.org>

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 Received on Tue Nov 10 2009 - 19:16:46 CST