quote:

---

Originally posted by Kimi86

Hi Folks,
My data looks like this Table name is Policy with 2 coulmns (policy and sub_cat)

policy sub_cat
1001 3 --1 repeats more than ones
1001 1
1001 4
1001 5
1001 1

1002 1 --1 and 2 both appear
1002 2
1002 3
1002 4

1003 3 --Nor 1 nor 2 appears
1003 6
1003 4
1003 7

1004 2 --Only 2 appears
1004 8
1004 6
1004 5


Now I need a third column (Call it 'Code')
Logic:
If 1 appears atleast ones for a policy key then 'CO'
If 2 appears atleast ones for a policy key then 'RO'
If 1 OR 2 neither appear for a policy key then 'RI'
If 1 AND 2 both appear then also 'CO'

So my result should be like this
policy Code
1001 CO
1002 CO
1003 RI
1004 PO

-- Here are the scripts to create test data
Create table policy
(policy int,
sub_cat int)

Insert into policy values (1001,3)
Insert into policy values (1001,1)
Insert into policy values (1001,4)
Insert into policy values (1001,5)
Insert into policy values (1001,1)
Insert into policy values (1002,1)
Insert into policy values (1002,2)
Insert into policy values (1002,3)
Insert into policy values (1002,4)
Insert into policy values (1003,3)
Insert into policy values (1003,6)
Insert into policy values (1003,4)
Insert into policy values (1003,7)
Insert into policy values (1004,2)
Insert into policy values (1004,8)
Insert into policy values (1004,6)
Insert into policy values (1004,5)

---

;WITH cte AS
(
	SELECT 
		policy,
		SUM(CASE WHEN sub_cat = 1 THEN 1 ELSE 0 END) OVER(PARTITION BY policy) AS Ones,
		SUM(CASE WHEN sub_cat = 2 THEN 1 ELSE 0 END) OVER(PARTITION BY policy) AS Twos
	FROM
		Policy
)
SELECT
	*,
	CASE 
		WHEN ones > 0 AND twos > 0 THEN 'RI'
		WHEN ones > 0 THEN 'CO'
		WHEN twos > 0 THEN 'RO'
		ELSE 'CO'
	END AS Code
FROM
	cte;
-- update cte set Code = 
	--CASE 
	--	WHEN ones > 0 AND twos > 0 THEN 'RI'
	--	WHEN ones > 0 THEN 'CO'
	--	WHEN twos > 0 THEN 'RO'
	--	ELSE 'CO'
	--END;

SELECT policy,
       CASE WHEN SUM(CASE WHEN sub_cat =1 THEN 1 ELSE 0 END) > 0 THEN 'CO'
            WHEN SUM(CASE WHEN sub_cat =2 THEN 1 ELSE 0 END) > 0 THEN 'RO'
            WHEN SUM(CASE WHEN sub_cat IN (1,2) THEN 1 ELSE 0 END) = 0 THEN 'RI'
       END AS Code
            FROM table
            GROUP BY policy