Find the active Group

Question

Post reply

Find the active Group

ssc_san

SSCommitted

Points: 1573

November 1, 2023 at 6:55 pm

Go to Answer

#4319169

Hello All,

I have a requirement where I am trying to get the active group number.

A groupnum can have multiple membid's and any of them can be active. I looking to find the groupnum's with only one "Active" memberid. If the memberid is active can be determined by not having any 'Cancelled' transtype. I tried using exists query to check, but looking any other efficient way.

Thank you for your help.

Test Data:

CREATE table #testMember
(id int IDENTITY(1,1),
grouingnum int,
membid varchar(10),
transtype varchar(50),
start_dt date,
insertdt date default getdate()
)

select * from #testMember
insert into #testMember( grouingnum, membid, transtype, start_dt)
select 0001,'A456', 'New Plan', getdate()
union all
select 0001,'A123', 'New Member', '12/10/2022' 
union all
select 0001,'A123', 'Plan Change', '01/10/2023' 
union all
select 0001,'A456', 'New Member', '02/01/2023'
union all
select 0001,'A123', 'Maintenance', '02/15/2023'
union all
select 0001,'A456', 'Plan Added', '02/25/2023'
union all
select 0001,'A123', 'Cancelled', '02/15/2023'
union all
-----
select 0002,'X123', 'New Member', '12/10/2022' 
union all
select 0002,'X123', 'Plan Change', '01/10/2023' 
union all
select 0002,'A456', 'New Member', '02/01/2023'
union all
select 0002,'A123', 'New Member', '02/15/2023'
union all
select 0002,'A456', 'Plan Added', '02/25/2023'
------
insert into #testMember( grouingnum, membid, transtype, start_dt)
select 0005,'X123', 'New Member', '12/10/2022' 
union all
select 0005,'X123', 'Plan Change', '01/10/2023' 
union all
select 0005,'A456', 'New Member', '02/01/2023'
union all
select 0005,'A123', 'New Member', '02/15/2023'
union all
select 0005,'A456', 'Plan Added', '02/25/2023'
union all
select 0005,'A123', 'Cancelled', '02/15/2023'
union all
select 0005,'A456', 'Cancelled', '08/15/2023'

This topic was modified 1 year ago by ssc_san.

Phil Parkin

SSC Guru

Points: 246959
More actions
November 2, 2023 at 9:16 am
Answer
#4319567

The query I provided returns the result you requested. Why do you think that it is not correct?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 23, 2023 at 3:38 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101093 More actions · Answer 1

I'm not sure I understand correctly, nor what result you expect to see from the sample data, but maybe this(?), based on my reading of your requirements so far:

select grouingnum
from #testMember
group by grouingnum
having count(distinct membid) - count(distinct case when transtype = 'Cancelled' then membid else null end) = 1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ssc_san SSCommitted Points: 1573 More actions · Answer 2

Thank you for the reply.

I was expecting groupnum = 1 and 5 as those two groups have only 1 active memberid.

Thank you!

Phil Parkin SSC Guru Points: 246959 More actions · Answer 3

Based on this logic

If the memberid is active can be determined by not having any 'Cancelled' transtype

This is the code I would use

SELECT DISTINCT tm.grouingnum
FROM #testMember tm
WHERE NOT EXISTS
(
    SELECT 1
    FROM #testMember tm2
    WHERE tm.grouingnum = tm2.grouingnum
          AND tm2.transtype = 'Cancelled'
);

But it does not return the 'grouingnums' you suggest, because all of them have 'cancelled' in transtype.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ssc_san SSCommitted Points: 1573 More actions · Answer 4

Thank you for the reply!

Apologies, I made an error in the test data. I updated the test data now.

groupingnum 1 has two members (A123, A456). A123 has a Cancelled transtype, whereas A456 is considered as Active membership since there is no Cancellation it.

I am looking to get the data of groupnums with one or more Active memberid's (no Cancel transtypes on them.)

Capture

This groupnum = 2 has 3 Active memberid's with no "Cancellations" on them.

Capture

Expected output is groupnum = 2

Thank you for your help.

This reply was modified 1 year ago by ssc_san. Reason: formatting

ssc_san SSCommitted Points: 1573 More actions · Answer 5

Thank you, I was just adding more information to my question.

Thanks again!