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

SQL

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

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