SQL CASE statement

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. but below CASE is not bringing up 6400 as it has 2 rows.

can anyone pls modify it ?

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

 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
 6400      101
gvamsimbaAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Was thinking more like
;with CTE_Status as
( 
  SELECT Custid 
       , case when flag='N' and pgroupcode = cgroupcode then 1 else 0 end as [Failstatus]
       , case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' then 1 else 0 end as [PassStatus]
  from yourtable
) Select Custid, case when sum([PassStatus]) > 0 and sum([failStatus]) = 0 then 1 else 0 end as [Status]
  from CTE_Status
  group by Custid

Open in new window

0
 
Mike in ITIT System AdministratorCommented:
According to your Case statement if there are any CustIds that are in the list more than once, regardless of the rest, then it will fail the first part of the Case and will result in a 0 for the status.

But if you change your Case to this:
case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' and count(Flag) over (partition by custid order by pgroupcode,cgroupcode) = 1 then 1 else 0 end as [status]

Open in new window

It seems to work as you are explaining it. I changed the "count(*)" to be "count(Flag)" since you don't have a Flag set for the second 6400
0
 
ste5anSenior DeveloperCommented:
Just a comment: CASE is an expression, not a statement.
1
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
gvamsimbaAuthor Commented:
Hi Mike , no the Flag for 2 records for 6400 is Y
Sorry if it’s missing ..

Thanks
0
 
Mike in ITIT System AdministratorCommented:
Could you put the whole ample up then so that I can test against the entire sample instead of just part of it.

And if there are more than one CustId then the status will always be 0 because that part of the case is never met so it will default to the "Else"
0
 
gvamsimbaAuthor Commented:
Hi we r only looking for each customerid
0
 
Mike in ITIT System AdministratorCommented:
I need to know what the last line should be for the CustID 6400 you are not showing that.

If they are different from the other line then changing the Count(*) to be Count(CustID) should get you what you are looking for.
This is the table that I am using
+------+-----+-----+-----+---+
| 6671 | 101 | 101 | BCG | Y |
+------+-----+-----+-----+---+
| 6671 | 101 | 101 | BCG | N |
| 6671 | 115 | 115 | BCG | N |
| 6671 | 101 | 985 | BCG | N |
| 6400 | 101 | 101 | BCG | Y |
| 6400 | 101 | 111 | BCG | Y |
| 5500 | 101 | 101 | BCG | Y |
+------+-----+-----+-----+---+


This is the Select that I use:
SELECT Custid ,pgroupcode, cgroupcode, Catcode , Flag,
       case when flag='Y' and pgroupcode = cgroupcode and pgroupcode = '101' and count(Custid) over (partition by custid order by pgroupcode,cgroupcode) = 1 then 1 else 0 end as [status]
from #TempTable
order by CustId desc, cGroupCode

Open in new window

This is the result:
+------+-----+-----+-----+---+---+
| 6671 | 101 | 101 | BCG | Y | 0 |
+------+-----+-----+-----+---+---+
| 6671 | 101 | 101 | BCG | N | 0 |
| 6671 | 115 | 115 | BCG | N | 0 |
| 6671 | 101 | 985 | BCG | N | 0 |
| 6400 | 101 | 101 | BCG | Y | 1 |
| 6400 | 101 | 111 | BCG | Y | 0 |
| 5500 | 101 | 101 | BCG | Y | 1 |
+------+-----+-----+-----+---+---+
0
 
ste5anSenior DeveloperCommented:
This is ASCII art, not a table. Please help the experts by posting concise and complete examples. This includes table DDL (data definition language) and sample data INSERT statements as runnable SQL script.

btw, telling us on what RDBMS you run this, would also be helpful.
0
 
gvamsimbaAuthor Commented:
Hi Mike, below is the lines for 6400 if that helps ?

6400      101                             101                         BCG                       Y
6400      101                             101                         BCG                       Y

Thanks
0
 
gvamsimbaAuthor Commented:
Below is the full table.

| 6671 | 101 | 101 | BCG | Y |
 +------+-----+-----+-----+---+
 | 6671 | 101 | 101 | BCG | N |
 | 6671 | 115 | 115 | BCG | N |
 | 6671 | 101 | 985 | BCG | N |
 | 6400 | 101 | 101 | BCG | Y |
 | 6400 | 101 | 101 | BCG | Y |
 | 5500 | 101 | 101 | BCG | Y |
0
 
Mike in ITIT System AdministratorCommented:
Since the two lines that have CustID of 6400 are identical there is no way to differentiate them so the count will always be more than 1 so the status will always be 0.

@Ste5an I was showing what the results that I got based on what I understand from this question. If I need to show how I created the table as well I can do that, but since I didn't ask the question I did not feel the need to do so.
0
 
gvamsimbaAuthor Commented:
thanks Mike but can we not select DISTINCT record to avoid duplicate in case ?
0
 
awking00Commented:
And what should the output from the query look like from that table?
0
 
gvamsimbaAuthor Commented:
custid   caseflag
6671      0
6400      1
5500      1
0
 
awking00Commented:
with cte as
(select custid, 1 as status
 from yourtable
 where pgroupcode = cgroupcode
 and flag = 'Y'
 union
 select custid, 0 as status
 from yourtable
 where pgroupcode = cgroupcode
 and flag = 'N')
select custid, min(status) as caseflag
from cte
group by custid
order by custid desc;
0
 
awking00Commented:
It probably performs a little better using union all instead of union since that won't require a sort.
0
 
gvamsimbaAuthor Commented:
But will min(status) work as per my requirement ?

Thanks
0
 
awking00Commented:
Some people are not fond of common table expressions so you can just use it like a subquery -

select x.custid, min(x.status) as caseflag from
(select custid, 1 as status from yourtable where pgroupcode = cgroupcode and flag = 'Y'
 union all
 select custid, 0 as status from yourtable where pgroupcode = cgroupcode and flag = 'N') x
group by custid
order by custid desc;
0
 
awking00Commented:
The first part of the subquery will produce
6671   1
5500   1
6400   1
6400   1

Open in new window


The second part of the subquery will produce
6671   0
6671   0

Open in new window


So the min(status) for 5500 and 6400 is 1 and for 6771 is 0. When grouped by results in 3 lines
6671   0
6400   1
5500   1

Open in new window

0
 
gvamsimbaAuthor Commented:
Thanks Awking , I will try this in the office tomorrow
0
 
PortletPaulfreelancerCommented:
This result:
| custid | flag |
|--------|------|
|   5500 |    1 |
|   6400 |    1 |
|   6671 |    0 |

Open in new window

using the following query,
nb: It appears that you need to use count(distinct pgroupcode) and you can't do that using the over() clause, hence I suggest using a subquery instead:
--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

select
      d.custid
    , case when d.pgroups > 1 and d.flag_ns > 0 then 0 else 1 end flag
from (
      select
            custid
          , count(distinct pgroupcode) pgroups
          , sum(case when pgroupcode = cgroupcode and flag = 'N' then 1 else 0 end) flag_ns
      from table1
      group by custid
      ) d

Open in new window

from this table:
CREATE TABLE Table1
    ([Custid] int, [pgroupcode] int, [cgroupcode] int, [Catcode] varchar(3), [Flag] varchar(1))
;

INSERT INTO Table1
    ([Custid], [pgroupcode], [cgroupcode], [Catcode], [Flag])
VALUES
    (6671, 101, 101, 'BCG', 'Y'),
    (6671, 101, 101, 'BCG', 'N'),
    (6671, 115, 115, 'BCG', 'N'),
    (6671, 101, 985, 'BCG', 'N'),
    (6400, 101, 101, 'BCG', 'Y'),
    (6400, 101, 101, 'BCG', 'Y'),
    (5500, 101, 101, 'BCG', 'Y')
;

Open in new window

also see: http://sqlfiddle.com/#!18/7c754/5
0
 
gvamsimbaAuthor Commented:
Hi Paul, your case should be d.flag_ns = 0 instead of >0 bcos  the inner query results will be

6671 2 0
5500 1 0
6400 1 0


 case when d.pgroups > 1 and d.flag_ns > 0 then 0 else 1 end flag

Please let me know What you think

Many Thanks
0
 
Mark WillsTopic AdvisorCommented:
It might be easier to look at this the other way around.

Instead of CASE to see what qualifies, maybe a CASE to see what doesnt

Might be easier to find the 'fails', which is one condition (I think) and that is when pgroupcode = cgroupcode and flag = 'N'

That of course assumes that there is the qualifying entry of at least 1 pgroupcode = cgroupcode  = 101 and flag = 'Y'

Does that sound like a reasonable approach ?
0
 
gvamsimbaAuthor Commented:
Mark what is a custid which has got only one group and the flag as N ?
At the moment your condition is giving flag as 1 for this custid but this needs to be Zero.
Thanks
0
 
gvamsimbaAuthor Commented:
Your case expression needs to be modified slightly
0
 
gvamsimbaAuthor Commented:
Thanks Mark . I will try it in office tomorrow..

But I think the CASE in your original query if changed to >=1 it should work for all scenarios right ?  Looking at your new solution it seems to be good as well but I prefer the first one if you think tat will work for all conditions with tst minor change . What do you think ?

case when d.pgroups >=1
0
 
Mark WillsTopic AdvisorCommented:
I am not confident that it will ever satisfy the range of possibilities. The 'challenge' with the "case" and "count() over" windowed function is it can only react to the row it is on. Which is fine for the first part, but that count isnt really differentiating enough information to help decide if that count can identify a scenario like :
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'N'),
(6671, 985, 985, 'BCG', 'Y'),  
OR
(6671, 101, 101, 'BCG', 'Y'),
(6671, 101, 985, 'BCG', 'Y'),
(6671, 985, 985, 'BCG', 'N'),  

And because we are trying to get to the CustID level, it is easier to let the detail rows decide their own qualifying status, and then decide at the custid level if the custid qualifies.

It is actually a lot trickier to do than to say. You have some outstanding experts in this thread - a lot of them I would ask to help me, and a privilege to be acquainted with them through EE.
0
 
awking00Commented:
>>(6671, 101, 101, 'BCG', 'Y'),
 (6671, 101, 985, 'BCG', 'N'),
 (6671, 985, 985, 'BCG', 'Y'),  
 OR
 (6671, 101, 101, 'BCG', 'Y'),
 (6671, 101, 985, 'BCG', 'Y'),
 (6671, 985, 985, 'BCG', 'N'), <<
What would you expect the caseflag to be from each of the above scenarios?
0
 
gvamsimbaAuthor Commented:
Excellent
0
 
Mark WillsTopic AdvisorCommented:
@awking00, first one passes, second one fails, the FLAG  'N' in first doesnt matter, where as it does in second.
0
 
awking00Commented:
>>First one passes, second one fails, the FLAG  'N' in first doesnt matter, where as it does in second.<<
Which is precisely what I expected and what my proposed solution indicated. I don't think gvamsimba ever tried it as it appears to be correct for all possibilities.
0
 
Mark WillsTopic AdvisorCommented:
Nearly, we also needed for one of those to be part of pcroupcode of 101 it is not enough to check pg = cg and flag = 'y' without there being a pg = cg = 101 and flag = 'y'.  It was implicit in the case statement, even though it wasnt spelled out in the commentary of the question. Does that make sense ?
0
 
awking00Commented:
@Mark Wills, was your comment ID: 42493647 in response to my last comment? If so, can you provide a set of values for custid, pgroupcode, cgroupcode, and flag where it fails?
0
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.

All Courses

From novice to tech pro — start learning today.