SQL case statement with multiple condtions

gvamsimba
gvamsimba used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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....
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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
gvamsimbaIT Consultant

Author

Commented:
Thanks Mark . I will try it out in the office tomorrow and will let you know
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

gvamsimbaIT Consultant

Author

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
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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

gvamsimbaIT Consultant

Author

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
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
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

gvamsimbaIT Consultant

Author

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'),
gvamsimbaIT Consultant

Author

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'),
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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 ?
gvamsimbaIT Consultant

Author

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'),
gvamsimbaIT Consultant

Author

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
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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" :)
gvamsimbaIT Consultant

Author

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'),
gvamsimbaIT Consultant

Author

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..
gvamsimbaIT Consultant

Author

Commented:
closing this as the solution worked for the data given. Have raised one more question with updated data and requirement.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial