Case when for different conditions

[septemberbulb]

Hello, I am trying to apply the following conditions:

but for the fifth and sixth conditions, for the situation does not meet any above requirements, I am not quite sure how to deal with that : 

I am trying to add a type for each unique client ID

• if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A
• if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B
• if a client ID has records with segment equal to 'I'  and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C
• if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other
• if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A
• if a client ID does not meet any of above requirements but has  revenue1 <=0 and revenue2<=0 -> Type B

Customer_ID	Account_ID	Record_Date	Segment	Revenue1	Revenue2
A1	001	17-Dec-18	N	0	0
A1	001	17-Apr-19	N	0	0
A1	001	17-Aug-19	N	0	0
A1	001			0	0
A1	002	17-Apr-19	N	0	5000
A1	002	17-Feb-19	M	10000	4000
A1	003	1-Jan-20	P2	4010	3000
A1	003	1-Feb-20	N	4600	4444
A1	003	2-Feb-20	P2	4330	3509
A1	003	3-Feb-20	N	5000	5890
A2	004	4-Apr-21	I	0	0
A2	004	18-Oct-21	I	0	0
A2	005	17-Aug-20		0	0
A2	006	17-Aug-20	P2	1345	667
A2	006	19-Oct-20	N	1356	1389
A2	007	17-Dec-18	SS	0	0
A2	008	19-Aug-19	T	0	0
A2	008	21-Oct-19	I	0	0
A2	008	21-Dec-20	N	0	0
A2	008	19-Apr-21	P2	999	400
B1	009	15-Apr-19	N	8997	7689
B1	009	19-Aug-19	N	758	456
B1	009	21-Oct-19	N	-869	-467
B1	009	16-Dec-19	SS	0	0
B1	009	19-Oct-20	N	0	0
B1	009	21-Dec-20	N	0	0
B1	010	18-Apr-18	I	0	0
B1	010	15-Oct-18	I	0	0
B1	010	17-Jun-19	N	0	0
B2	011	16-Oct-17	I	-97	-97
B2	011	18-Dec-17	N	0	-98
B2	011	19-Feb-18	Q	0	0
B2	011	21-Apr-20	Q	0	0
B2	011	25-Jun-20	Q	0	0
B2	011	16-Oct-17	Q	0	0
B2	011	18-Apr-18	Q	0	0
B2	011	15-Apr-19	T	0	0
B2	011	25-Jun-20	T	0	0
B2	011	17-Aug-20	T	0	0
B2	012	21-Jun-21	T	0	0
B2	012	16-Aug-21	D	47	-900
B2	012	18-Oct-21	T	-90	-89
B2	012	20-Dec-21	T	0	-78
B2	012	19-Oct-20	N	13567	890
B2	013	17-Dec-18	SS	-150	50
B2	013	18-Feb-19	T	0	34909
B2	014	16-Dec-19	T	-1	56786
B2	014	17-Feb-20	T	-1	23
B2	014	25-Jun-20	I	-1456	-456
B2	014	17-Aug-20	I	-2456	-4738

Thank you

[Reeza]

Is a client identified by a combination of CustomerID and AccountID?

I'm guessing that it's actually an order of operation issues 

CASE goes in order of conditions, you may need to move your fourth condition to the last condition or it will catch the last two condition. 

This order:

• if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A
• if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B
• if a client ID has records with segment equal to 'I'  and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C
• if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A
• if a client ID does not meet any of above requirements but has  revenue1 <=0 and revenue2<=0 -> Type B
• if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other

If something like that doesn't work, please post what the Type is for each of your codes shown and include the code you have so far.

Spoiler

---

@septemberbulb wrote:

Hello, I am trying to apply the following conditions:

but for the fifth and sixth conditions, for the situation does not meet any above requirements, I am not quite sure how to deal with that : 

I am trying to add a type for each unique client ID

• if a client ID has records with segment in ('D', 'M', 'P2', 'P1') and not equal to ‘T’-> Type A
• if a client ID has records with segment equal to ‘T’ and none in ('D', 'M', 'P1', 'P2') -> Type B
• if a client ID has records with segment equal to 'I'  and none in ('D', 'M', 'P1', 'P2',’T’) -> Type C
• if a client ID has no records with segment in ('D', 'M', 'P2', 'P1',’T’) -> Other
• if a client ID does not meet any of above requirements but revenue1 >0 and revenue2>0 ->Type A
• if a client ID does not meet any of above requirements but has  revenue1 <=0 and revenue2<=0 -> Type B

Customer_ID	Account_ID	Record_Date	Segment	Revenue1	Revenue2
A1	001	17-Dec-18	N	0	0
A1	001	17-Apr-19	N	0	0
A1	001	17-Aug-19	N	0	0
A1	001			0	0
A1	002	17-Apr-19	N	0	5000
A1	002	17-Feb-19	M	10000	4000
A1	003	1-Jan-20	P2	4010	3000
A1	003	1-Feb-20	N	4600	4444
A1	003	2-Feb-20	P2	4330	3509
A1	003	3-Feb-20	N	5000	5890
A2	004	4-Apr-21	I	0	0
A2	004	18-Oct-21	I	0	0
A2	005	17-Aug-20		0	0
A2	006	17-Aug-20	P2	1345	667
A2	006	19-Oct-20	N	1356	1389
A2	007	17-Dec-18	SS	0	0
A2	008	19-Aug-19	T	0	0
A2	008	21-Oct-19	I	0	0
A2	008	21-Dec-20	N	0	0
A2	008	19-Apr-21	P2	999	400
B1	009	15-Apr-19	N	8997	7689
B1	009	19-Aug-19	N	758	456
B1	009	21-Oct-19	N	-869	-467
B1	009	16-Dec-19	SS	0	0
B1	009	19-Oct-20	N	0	0
B1	009	21-Dec-20	N	0	0
B1	010	18-Apr-18	I	0	0
B1	010	15-Oct-18	I	0	0
B1	010	17-Jun-19	N	0	0
B2	011	16-Oct-17	I	-97	-97
B2	011	18-Dec-17	N	0	-98
B2	011	19-Feb-18	Q	0	0
B2	011	21-Apr-20	Q	0	0
B2	011	25-Jun-20	Q	0	0
B2	011	16-Oct-17	Q	0	0
B2	011	18-Apr-18	Q	0	0
B2	011	15-Apr-19	T	0	0
B2	011	25-Jun-20	T	0	0
B2	011	17-Aug-20	T	0	0
B2	012	21-Jun-21	T	0	0
B2	012	16-Aug-21	D	47	-900
B2	012	18-Oct-21	T	-90	-89
B2	012	20-Dec-21	T	0	-78
B2	012	19-Oct-20	N	13567	890
B2	013	17-Dec-18	SS	-150	50
B2	013	18-Feb-19	T	0	34909
B2	014	16-Dec-19	T	-1	56786
B2	014	17-Feb-20	T	-1	23
B2	014	25-Jun-20	I	-1456	-456
B2	014	17-Aug-20	I	-2456	-4738

Thank you

---

[septemberbulb]

For example, for client ID A, it has multiple records as long as any records meet the condition, based on the logic, the result will mark the client ID A is Type A. But when I use case when,  the result only helps me generate a type after each row. it is not a aggerated view for each Client. Group by Client.

[Kurt_Bremser]

I see no value "A" anywhere in your data. How is a "client" defined? Solely by customer_id, or as a combination of customer_id and account_id?

[septemberbulb]

Sorry Let me repost this for a better view. 

[Reeza]

Show your code. 

Your code is very likely wrong but without seeing it, can't say how.