MEDICATIONS COSTS USING SQL

I use medication data from a simulated hospital database on the Sythea website for analyzing in this post.

The medications table have columns such as ‘start date’, ‘stop date’, ‘patientid’, ‘encounterid', ‘descriptions’, ‘base_cost’, ‘dispense’, ‘total cost’.

Another table called ‘encounters’ have information about the encounters that patients get medications with total claim costs for encounters. Two tables are merged together.

I found some abnormalities such as no ‘stop date’ that means medications might provide for abnormal duration of time leading to very high costs.

Therefore, I decide to filter data from encounters that last for 1 year (360 days).

I save out this table to csv file then connect to Tableau to analyze and visualize data.

From 1959 to 2024, there are 117 patients with 8,884 encounters for 6 types of visits in this dataset.

Number of encounters and total medication costs over years as following:

Medication costs by encounter types

Medication cost - total claim cost ratio by the encounter types.

In this dataset, there are many encounters in which the medication costs is higher than total claim costs. I can’t explain these abnormalities.

Abnormal medication cost ratio occurs among encounter types with numbers as following:

In these encounter sub-groups, the medication cost is higher than total claim costs, especially some encounters have ratios more than 20.