Find the active Group

  • 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 4 months ago by  ssc_san.
  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thank you for the reply.

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

     

    Thank you!

     

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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 4 months ago by  ssc_san. Reason: formatting
  • The query I provided returns the result you requested. Why do you think that it is not correct?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

    Thanks again!

  • 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