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, 1 month ago by ssc_san.

Phil Parkin

SSC Guru

Points: 246985
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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: 101120 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: 246985 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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, 1 month 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!