none
T-SQL help

    Question

  • Hi All,

    I need to create report for optical care whose agreement type is like optical care. I need to show active member and allow only one person per membership to be selected that has claimed any dental treatment with a calendar month.

    I also need state wise data for the same. I need to avoid repeating  by excluding any membership that have been selected in the previous months. The age of person should be greater than 18 years of age.  I need to select specific item members for the achieving the end result.

    Please help me to write a query.

    Regards,

    kccrga

    Sunday, September 29, 2013 10:37 PM

All replies

  • Can you post table DDL & whatever query you have? Thanks.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, September 29, 2013 11:27 PM
    Moderator
  • Any help here????
    Monday, September 30, 2013 11:59 PM
  • Hi kccrga,

    It’s hard to guess the relationship between these tables, for find a solution quickly, please provide their relationship, some sample data and your desire result here for analysis.


    Allen Li
    TechNet Community Support

    Tuesday, October 01, 2013 5:56 AM
    Moderator
  • Can you add PRIMARY KEY, UNIQUE KEY & FOREIGN KEY definitions to the DDL?  Thanks.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Thursday, October 17, 2013 1:44 AM
    Moderator
  • Although you haven't provided enough information, I have written a query which should get you closer to your goal by at least pointing you in the right direction for your specified reporting needs.  I had to make some assumptions on what fields are used for your filters.  You can adjust as necessary.

    SELECT * FROM 
    (
    SELECT 
    ROW_NUMBER() OVER(PARTITION BY CLM.Membership_ID ORDER BY CLM.service_date) AS Row,
    MBSHP.membership_id,
    PRSN.person_id
     FROM 
    claim_line as CLM 
    INNER JOIN provider_claim as PR_CLM ON PR_CLM.provider_number_id = CLM.provider_number_id
    INNER JOIN Agreement as AGR ON AGR.agreement_type = PR_CLM.Agreement_Type
    INNER JOIN person_membership AS MBSHP ON CLM.Membership_ID = MBSHP.membership_id
    INNER JOIN Person as PRSN ON PRSN.person_id = MBSHP.person_id
    
    WHERE 
    AGR.description LIKE '%optical care%'
    AND MBSHP.[status_flag] = 'A' --assumption this is active status
    AND CLM.claim_type = 'D' --assumption that this is dental treatment
    AND PRSN.date_of_birth < DATEADD(year, -18, CAST(GETDATE() as DATE)) --person is > 18 years old
    AND CLM.service_date BETWEEN '2013-09-01' AND '2013-09-30'  -- For September 2013
    
    ) as T1
    WHERE Row = 1 --This will retrieve only the first person in the membership who has claimed dental in the time period selected
                  --This will accomplish the goal of not repeating in part 2 for the state wide report as well
    The only part I could not accomodate is the "need to select specific item members for the achieving the end result."  I cannot see how the Item table relates to the other tables.  I hope this helps you.

    Martina White

    • Proposed as answer by MajaS Saturday, October 26, 2013 2:56 AM
    Monday, October 21, 2013 10:32 PM