none
Filtering Categories RRS feed

  • Question

  • Hello, I am having trouble figuring out how to filter plans from a list of clients.

    For example.

    John Doe has two plans: Healthplus and NY Medicaid

    Jane Doe has only one plan: NY Medicaid

    I would like my report to only show John Doe and other clients with the Healthplus plan while also showing clients who only have the one NY Medicaid plan. I just cannot think of how to get it done. 

    Can anyone assist with this issue? I hope this makes sense.

    Wednesday, June 12, 2019 4:04 PM

Answers

All replies

  • please post:

    1) Queries to CREATE your table(s) including indexes
    2) Queries  to INSERT sample data.
    3) The desired result given the sample, as text or image of excel for example.
    4) A short description of the business rules, and how you got 1-2 of the results
    5) Which version of SQL Server you are using (this will help to fit the query to your version).

    Regards,


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Wednesday, June 12, 2019 6:06 PM
    Moderator
  • Hi Alchulo,

     

    Would you like this one ?

    IF OBJECT_ID('test') IS NOT NULL drop table  test
    go 
    create table test (
    [name] varchar(20),
    [plan] varchar(20))
    insert into test values 
    ('John Doe','Healthplus'),
    ('John Doe','NY Medicaid'),
    ('Jane Doe','NY Medicaid'),
    ('Lily Doe','Healthplus'),
    ('Lily Doe','NY Medicaid')
    
    ;with cte as (
    select *, count(*)over (partition by [name]) as ct 
    from test )
    
    select [name],[plan] from cte where name='John Doe' or ct=1
    /*
    name                 plan
    -------------------- --------------------
    Jane Doe             NY Medicaid
    John Doe             Healthplus
    John Doe             NY Medicaid
    */
    
    


    If above script doesn’t satisfy your requirement, please share us your table structure and some sample data. So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 13, 2019 6:17 AM
  • Thanks for the responses. I managed to find a way to do this. 


    • Marked as answer by Alchulo Monday, June 17, 2019 4:58 PM
    Friday, June 14, 2019 11:56 AM
  •  Hi Alchulo,

     

    Thank you for your reply.

     

    We are glad to hear that  you have an idea for your issue  ,please kindly mark the helpful replies or your own reply as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 17, 2019 6:38 AM