1 Reply Latest reply on Feb 11, 2019 9:43 PM by ArseneXie

    SQL

    suvas.chandra

      I know this is not a SQL forum. But I have a business case which can I solve in Tableau very easily. But I cannot write 100% code in SQL.

       

      It will be nice if anyone can help me with this.

       

      ===========>>>>>

      After a provider(Doctor) provide a service to a member, provider ask XYZ health insurance company for a payment based on the terms of the insurance policy.

       

      Based on the above three tables, I need to find the answer in SQL for the below questions:

       

      1.How much is the total YTD amount from claims?

      2.Show YTD claim amount by member city?

      3.What % of member got service(YTD) from different city than their home city?

      4.Find providers(doctors) that do not have any YTD claims?

      5.Find providers(doctors)  that do not have any YTD claims from members outside of provider city?

      6.Rank the members by claim amount per provider.

        • 1. Re: SQL
          ArseneXie

          Hi,

           

          What kind of DB do you have?

           

          For Oracle:

          1.How much is the total YTD amount from claims?

          SELECT TO_CHAR(SERVE_DATE,'YYYY') YEAR,SUM(AMOUNT) YTD
          FROM CLAIMS
          GROUP BY TO_CHAR(SERVE_DATE,'YYYY')
          

           

          2.Show YTD claim amount by member city?

          SELECT M.CITY, TO_CHAR(C.SERVE_DATE,'YYYY') YEAR,SUM(C.AMOUNT) YTD
          FROM CLAIMS C, MEMBER M
          WHERE C.MEMBER_ID = M.MEMBER_ID
          GROUP BY M.CITY, TO_CHAR(C.SERVE_DATE,'YYYY')
          

           

          3.What % of member got service(YTD) from different city than their home city?

          SELECT MEMBER,
            YEAR,
            YTD_DIFF/YTD_ALL*100 DIFF_PERC
          FROM 
          (SELECT M.MEMBER, TO_CHAR(C.SERVE_DATE,'YYYY') YEAR,
            SUM(C.AMOUNT*DECODE(P.CITY,M.CITY,0,1)) YTD_DIFF,
            SUM(C.AMOUNT) YTD_ALL
          FROM CLAIMS C, MEMBER M, PROVIDER P
          WHERE C.MEMBER_ID = M.MEMBER_ID
          AND P.PROVIDER_ID = C.PROVIDER_ID
          GROUP BY M.MEMBER, TO_CHAR(C.SERVE_DATE,'YYYY'))
          

           

          4.Find providers(doctors) that do not have any YTD claims?

          SELECT P.PROVIDER_ID
          FROM PROVIDER P
          MINUS
          SELECT DISTINCT C.PROVIDER_ID
          FROM CLAIMS C
          

           

          5.Find providers(doctors)  that do not have any YTD claims from members outside of provider city?

          SELECT P.PROVIDER_ID
          FROM PROVIDER P
          WHERE NOT EXISTS(SELECT 1 
            FROM CLAIMS C,MEMBER M 
            WHERE C.PROVIDER_ID = P.PROVIDER_ID 
            AND C.MEMBER_ID=M.MEMBER_ID 
            AND M.CITY != P.CITY)
          

           

          6.Rank the members by claim amount per provider.

          SELECT PROVIDER_ID, MEMBER_ID, RANK() OVER(PARTITION BY PROVIDER_ID ORDER BY TTL_AMOUNT DESC) RN
          FROM
          (SELECT C.PROVIDER_ID, C.MEMBER_ID, SUM(C.AMOUNT) TTL_AMOUNT
          FROM CLAIMS C
          GROUP BY C.PROVIDER_ID, C.MEMBER_ID)
          
          1 of 1 people found this helpful