Hello,

Here's what I have, which is getting me close...

SELECT m.MemberID, 
vw.EventCategory,
vw.EventType, 
vw.EventEffectiveDate,
vw.EventTerminationDate
FROM dbo.MemberEligibilityEventVW vw JOIN
dbo.Member m ON vw.MemberKey = m.MemberKey JOIN
(
SELECT  mb.MemberKey,vw.EventEffectiveDate,vw.EventTerminationDate, vw.EventCategory AS EC
FROM dbo.MemberEligibilityEventVW vw JOIN
dbo.Member mb ON vw.MemberKey = mb.MemberKey
WHERE vw.EventType = 'Test' 
GROUP BY  mb.MemberKey, vw.EventCategory, vw.EventEffectiveDate, vw.EventTerminationDate
)x
   ON  m.MemberKey = x.MemberKey
   AND vw.EventCategory = x.EC 
   WHERE vw.EventType = 'Test'
   ORDER BY m.MemberID ASC

   If records have the same dates (EventEffectiveDate, EventTerminationDate) and [EventCategory] in ('ABC', 'XYZ'), then return a distinct record with [EventCategory] of 'XYZ', else whatever appears for that record ('ABC' or 'XYZ') for that date range. It will always be one of these two event categories.
   [EventType] will always be the same, probably an unnecessary column for this example but part of the record and thought it might help for grouping example.

   CURRENT OUTPUT:
   [MemberID] [EventCategory] [EventType] [EventEffectiveDate] [EventTerminationDate]
   C01492201  ABC             TEST        2010-08-25           9999-12-31 
   C01492201  XYZ             TEST        2010-08-25           2012-07-31 
   C01492201  ABC             TEST        2013-04-15           2013-10-31 
   C01492201  XYZ             TEST        2013-04-15           2013-10-31

   DESIRED OUTPUT:
   [MemberID] [EventCategory] [EventType] [EventEffectiveDate] [EventTerminationDate]
   C01492201  ABC             TEST        2010-08-25           9999-12-31 
   C01492201  ABC             TEST        2010-08-25           9999-12-31 
   C01492201  XYZ             TEST        2013-04-15           2013-10-31 

So a distinct record for the last row is what I'm going for in this case. 

Hope this makes sense.

Any help is greatly appreciated. 

Thanks,

With cte As
(SELECT m.MemberID, 
vw.EventCategory,
vw.EventType, 
vw.EventEffectiveDate,
vw.EventTerminationDate,
Row_Number() Over
  (Partition By m.MemberID, vw.EventCategory, vw.EventEffectiveDate, vw.EventTerminationDate
  Order By vw.EventType Desc) As rn
FROM dbo.MemberEligibilityEventVW vw JOIN
dbo.Member m ON vw.MemberKey = m.MemberKey JOIN
(
SELECT  mb.MemberKey,vw.EventEffectiveDate,vw.EventTerminationDate, vw.EventCategory AS EC
FROM dbo.MemberEligibilityEventVW vw JOIN
dbo.Member mb ON vw.MemberKey = mb.MemberKey
WHERE vw.EventType = 'Test' 
GROUP BY  mb.MemberKey, vw.EventCategory, vw.EventEffectiveDate, vw.EventTerminationDate
)x
   ON  m.MemberKey = x.MemberKey
   AND vw.EventCategory = x.EC 
   WHERE vw.EventType = 'Test')
SELECT MemberID, 
EventCategory,
EventType, 
EventEffectiveDate,
EventTerminationDate
From cte
ORDER BY MemberID ASC
Where rn = 1;

Tom

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 1 hour 27 minutes ago

;WITH x AS
(
	SELECT
		m.MemberID,
		vw.EventEffectiveDate, 
		vw.EventTerminationDate, 
		vw.EventType
	FROM dbo.MemberEligibilityEventVW vw 
		INNER JOIN dbo.Member m ON vw.MemberKey = m.MemberKey
	WHERE EventType = 'Test'
	GROUP BY
		m.MemberID,
		vw.EventEffectiveDate, 
		vw.EventTerminationDate, 
		vw.EventType
	HAVING COUNT(*) > 1
)
SELECT
	MemberID,
	'XYZ', 
	EventEffectiveDate, 
	EventTerminationDate, 
	EventType
FROM x
UNION ALL
SELECT
	m.MemberID,
	vw.EventCategory, 
	vw.EventEffectiveDate, 
	vw.EventTerminationDate, 
	vw.EventType
FROM dbo.MemberEligibilityEventVW vw 
	INNER JOIN dbo.Member m ON vw.MemberKey = m.MemberKey
WHERE
	vw.EventType = 'Test' 
	AND
	NOT EXISTS (SELECT 1 
		FROM x 
		WHERE vw.EventEffectiveDate = x.EventEffectiveDate AND
		vw.EventTerminationDate = x.EventTerminationDate)

Hi Tom,

Small correction.

Row_Number() Over
  (Partition By m.MemberID, vw.EventCategory, vw.EventEffectiveDate, vw.EventTerminationDate
  --Order By vw.EventType Desc) As rn
  Order By vw.EventCategory Desc) As rn