-- *** Test Data ***
CREATE TABLE #s
(
	Account int NOT NULL
	,Start_DT date NOT NULL
	,End_DT date NOT NULL
	,Area_Name varchar(20) NOT NULL
);
INSERT INTO #s
VALUES (300815, '20140115',  '20140423', 'CHANNEL')
	,(300815, '20140115', '20140424', 'CHANNEL')
	,(300815, '20140115', '99991231', 'STREET')
	,(300815, '20140115', '20140424', 'CITY')
	,(300815, '20140424', '20140424', 'CHANNEL')
	,(300815, '20141231', '20150105', 'CHANNEL')
	,(300815, '20141231', '99991231', 'CHANNEL')
	,(400578, '20140115', '20140423', 'CHANNEL')
	,(400578, '20140115', '20140510', 'STREET')
	,(400578, '20140424', '20140510', 'CHANNEL')
	,(500187, '20141215', '99991231', 'CHANNEL')
	,(500187, '20141215', '99991231', 'CHANNEL')
	,(500187, '20141215', '99991231', 'STREET')

SELECT DISTINCT ACCOUNT, AREA_NAME, CAST(NULL AS char(1)) AS Disruption
INTO #t
FROM #s;
-- *** End Test Data ***

select * from #t;

WITH DeDupe
AS
(
	SELECT DISTINCT *
	FROM #s
)
,Disrupted
AS
(
	SELECT Account, Area_Name
	FROM DeDupe
	GROUP BY Account, Area_Name
	HAVING COUNT(*) > 1
)
UPDATE T
SET Disruption =
	CASE
		WHEN D.Account IS NULL THEN 'N'
		ELSE 'Y'
	END
FROM #t T
	LEFT JOIN Disrupted D
		ON T.Account = D.Account
			AND T.Area_Name = D.Area_Name;

select * from #t;