SQL case statement with multiple condtions

Hi, below is my sample data and I need to write a case statment with the below condtions -

1) if a custid has just ONE distinct pgroupcode as 101 and matching cgroupcode as 101 and flag as Y then my CASEs should return 1. PLease note that 101 can be hardcoded for this case statement.

2) however, if a custid has more than one pgroupcode with matching cgroupcode and flag as as Y for ALL the
pgroupcode and cgroupcodes then the CAse SHOULD return 1.

all other scenarios case should return 0

Can anyone please give me that CASE statement ?

for the below data, for custid 6671 case should return 0 as it has got 2 different pgroupcodes and one of them with matching cggroupcode has FLAG N. So case should return 0

for custids 5500 and 6400, case should return 1 as it has got only 1 PGgroupcode with matching cggroupcode with FLAG Y.

Many Thanks


Custid      pgroupcode      cgroupcode      Catcode          Flag
6671      101                             101             BCG                    Y
6671      101                             101           BCG                    N
6671      101                             985          BCG                    N
6671      115                             115          BCG                   N
5500      101                             101         BCG                   Y
6400      101                             101        BCG                   Y
gvamsimbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
You can use windowed functions to help get the counters

So, you would want to check all the conditions,
FLAG = Y,  
pgroupcode = cgroupcode
pgroupcode = 101,
and count(*) over (partition by custid order by pgroupcode,cgroupcode) = 1

SELECT Custid ,pgroupcode, cgroupcode, Catcode , Flag,
       case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' and count(*) over (partition by custid order by pgroupcode,cgroupcode) = 1 then 1 else 0 end as [status]
from yourtable

Open in new window

Havent tested so might have syntax error(s) and will do if needed....
0
Mark WillsTopic AdvisorCommented:
Just had the opportunity to run up the code and test. Seems to be working per your requirements. Only 5500 and 6400 end up with [status] = 1
0
gvamsimbaAuthor Commented:
Thanks Mark . I will try it out in the office tomorrow and will let you know
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gvamsimbaAuthor Commented:
Hi Mark, but if I have a duplicate the custid is not coming up ..example below..

6400      101                             101        BCG                   Y
6400      101                             101        BCG                   Y

Can you plz modify your case statement to capture this ?

Thanks
0
Mark WillsTopic AdvisorCommented:
Hmmm... I would suggest that it shouldn't come up by definition....

But we can eliminate duplicates first
; with CTE as 
( SELECT distinct Custid ,pgroupcode, cgroupcode, Catcode , Flag from yourtable)
  SELECT Custid ,pgroupcode, cgroupcode, Catcode , Flag
        ,case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' and count(*) over (partition by custid order by pgroupcode,cgroupcode) = 1 then 1 else 0 end as [status]
  from CTE

Open in new window

0
gvamsimbaAuthor Commented:
Hi Mark, but the issue is the below customer will still come up in distinct but I want this customer should be flagged as zero as this customer has another groupcode where the flag is N

6671      101                             101             BCG                    Y
0
Mark WillsTopic AdvisorCommented:
But 6671 does return [status] = 0

Not sure I understand

Here is my test data and query
create table #yourtable (custid int, pgroupcode int, cgroupcode int, catcode varchar(10), flag char(1))

insert #yourtable values 
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 101, 'BCG', 'N'),
(6671, 101, 985, 'BCG', 'N'),
(6671, 115, 115, 'BCG', 'N'),

(5500, 101, 101, 'BCG', 'Y'),
(6400, 101, 101, 'BCG', 'Y'),       -- new requirement
(6400, 101, 101, 'BCG', 'Y')
 


; with CTE as 
( SELECT distinct Custid ,pgroupcode, cgroupcode, Catcode , Flag from #yourtable)
  SELECT distinct Custid ,pgroupcode, cgroupcode, Catcode , Flag
        ,case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' and count(*) over (partition by custid order by pgroupcode,cgroupcode) = 1 then 1 else 0 end as [status]
  from CTE

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gvamsimbaAuthor Commented:
Hi Mark, the below data belonging to 6671 will be satisfied and hence returns status 1. But I want it return 0 as the FLAG is not Y for the matching conditions for BOTH pgroupcodes 101 and 985 with matching cgroupcode

(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'Y),
(6671, 985, 985, 'BCG', 'N'),
0
gvamsimbaAuthor Commented:
Mark, basically the third row below for 6671 which matches the condition pgroupcode  = cgroupcode and cgroupcode =BCG but the FLAG is N. Hence the condition failed for the groupcode 985. Hence the custid 6671 should be failed on case expression with 0

(6671, 101, 101, 'BCG', 'Y'),
 (6671, 101, 985, 'BCG', 'Y),
 (6671, 985, 985, 'BCG', 'N'),
0
Mark WillsTopic AdvisorCommented:
OK, then is it fair to say that if a Flag = 'N' in the set for 6671 then that set has status = 0

Or, is it more accurate to say in a set for 6671 where pgroupcode = cgroupcode if there is a flag 'N' then the set has status 0


So, if we had
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'Y),
(6671, 985, 985, 'BCG', 'Y'),
 then status = 1 for the first and 0 for the rest

OR , if we had
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'N'),
(6671, 985, 985, 'BCG', 'Y'),
 then status = 1 for the first and 0 for the rest or 0 for all ?
0
gvamsimbaAuthor Commented:
it is correct to say in a set for 6671 where pgroupcode = cgroupcode and cgroupcode =BCG  if there is a flag 'N' then the set has status 0.
status in this case will be 1 only if all the per_client_grp_codes match with FLAG Y for this CUSTID

below is my sample for 6671 and ALL 3 rows should return ZERO in CASE

(6671, 101, 101, 'BCG', 'Y'),
  (6671, 101, 985, 'BCG', 'Y),
  (6671, 985, 985, 'BCG', 'N'),
0
gvamsimbaAuthor Commented:
The below in your example the CASE should return status 1 for this custid as it met all the condtions

(6671, 101, 101, 'BCG', 'Y'),
 (6671, 101, 985, 'BCG', 'Y),
 (6671, 985, 985, 'BCG', 'Y'),

Hope this makes sense. Basically I am not worried about row level status. All I want is a status of 0 or 1 PER custid
0
Mark WillsTopic AdvisorCommented:
OK, just one more...
if we had
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'N'),
(6671, 985, 985, 'BCG', 'Y'),

results in custid has status = 1


I think your comment "Basically I am not worried about row level status. All I want is a status of 0 or 1 PER custid" makes a big difference" :)
0
gvamsimbaAuthor Commented:
Hi Mark , yes you are right in  this example below this customer will have status 1...
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'N'),
(6671, 985, 985, 'BCG', 'Y'),
0
gvamsimbaAuthor Commented:
Regarding my comment , I mean a customer who did not satisfy condition should have 0 status for all rows even if one row satisfies ..
Basically I will be doing a sum(status) > 0 for each person id..
0
gvamsimbaAuthor Commented:
closing this as the solution worked for the data given. Have raised one more question with updated data and requirement.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.