CREATE PROCEDURE [dbo].[pr_SegmentID_update_Edit]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  UPDATE ad
	SET nSegmentID = 
	CASE 
		WHEN cChargeOffReason = '10' AND nRecoveryScore >= 526 THEN 1
		WHEN cChargeOffReason = '10' AND nRecoveryScore < 526 THEN 2
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo = 'Y' AND 
						 c.accountid IS NOT NULL  AND nRecoveryScore BETWEEN 485 AND 525 THEN 3 
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo <> 'Y' AND 
						 c.accountid IS NOT NULL AND nRecoveryScore >= 526 THEN 4
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo = 'Y' AND 
						 c.accountid IS NOT NULL AND nRecoveryScore >= 526 THEN 5    
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo <> 'Y' AND 
						 c.accountid IS NULL AND nRecoveryScore >= 526 THEN 6
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo = 'Y' AND 
						 c.accountid IS NULL AND nRecoveryScore BETWEEN 485 AND 525 THEN 7
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo = 'Y' AND 
						 c.accountid IS NULL AND nRecoveryScore >= 526 THEN 8
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo <> 'Y' AND 
						 c.accountid IS NULL AND nRecoveryScore < 526 THEN 9
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo <> 'Y' AND 
						 c.accountid IS NOT NULL AND nRecoveryScore < 526 THEN 10
		WHEN cChargeOffReason <> '10' AND cVoluntaryRepo = 'Y' AND 
						 nRecoveryScore BETWEEN 436 AND 484 THEN 11
		 ELSE 12
	END
	FROM Stg_Accountdaily ad
	LEFT JOIN CONTACT c ON c.accountid = ad.accountid AND c.contactTypeid in(5,6,13,16)
	END