Return distinct record with distinct value if duplicate date range 1

[larrydavid]

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,
Buster

 

[Olaf Doschke]

OK, let's put these two queries side by side:

SELECT m.MemberID,
vw.EventCategory,
vw.EventType,
vw.EventEffectiveDate,
vw.EventTerminationDate
FROM dbo.MemberEligibilityEventVW vw JOIN
dbo.Member m ON vw.MemberKey = m.MemberKey
...WHERE vw.EventType = 'Test'

SELECT
mb.MemberKey,
vw.EventCategory AS EC,
vw.EventEffectiveDate,
vw.EventTerminationDate,
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

I reordered fields a bit, but aside of that it shows you almost repeat the same query, the slight change is you have memberkey instead of id, but that's just for the joining anyway. The other difference is you group by. But you group by ALL columns. Are you aware what this causes? Think about that.

Bye, Olaf.

 

[Olaf Doschke]

As you say there are only two eventcategories anyway, then either using MIN or MAX would fit:

Declare @Members as Table (MemberKey Int, MemberID Char(9));
Declare @Events as Table (MemberKey Int, EventCategory Char(3), EventType Char(4), EventEffectiveDate Date, EventTerminationDate Date);

Insert Into @Members Values (1,'C01492201')
Insert Into @Events Values (1, 'ABC', 'Test', '20100825', '99991231')
   ,                       (1, 'XYZ', 'Test', '20100825', '20120731')
   ,                       (1, 'ABC', 'Test', '20130415', '20131031')
   ,                       (1, 'XYZ', 'Test', '20130415', '20131031')

SELECT m.MemberID, 
 MIN(vw.EventCategory) as EventCategory,
 vw.EventType, 
 vw.EventEffectiveDate,
 vw.EventTerminationDate
 FROM @Events vw JOIN
 @Members m ON vw.MemberKey = m.MemberKey 
 WHERE vw.EventType = 'Test'
 GROUP BY m.MemberID, vw.EventType, vw.EventEffectiveDate, vw.EventTerminationDate
 ORDER BY m.MemberID

It doesn't give your desired result, but looking at the data your desired result is wrong.

Bye, Olaf.

 

[larrydavid]

Hi Olaf,

Actually, using your example above with MAX(vw.EventCategory) is giving me exactly the output I need.

Thank you so much for your support and sharing your expertise. Another learning experience.

Best Regards,
Buster

 

[Olaf Doschke]

Just to reveal what I meant with my question about what grouping by all columns means: The columns you group by, the smaller the groups get. If you group by all (or almost all) columns of a table, your groups become as small as a single record per group. This causes the same as no grouping at all.

If there would be more fields you need to add from any table, eg some field coming from the same record as the MAX(vw.EventCategory) comes from, then you need a subquery to filter for a) the columns making up a group and b) the record with that category, to be able to join the full record and pick whatever fields from it.

Bye, Olaf.