Question

One of the hospital’s financial analysts needs to compare actual treatment charges charged to

patients to the established normal service charges for a treatment. Display the service_id and
service_charge columns from the scevice_table, and the actual_charge from the treatment table.
The analyst is concerned with treatments where the actual (abs) difference between the service and
actual charge is more than $50.00 above or below the normal serve charge (either high or low). Also
display a computed column that is the difference between the service and actual charge (as a
positive number). Use appropriate column sizes and headings and format the charges with a dollar
sign ($) and appropriate comma and decimal point with two digits to the right of the decimal point
so far I got
select s.service_id, service_charge, actual_charge
from service s JOIN treatment t ON (s.service_id = t.service_id)

Answer 1

The SQL query needed to compare actual treatment charges to established service charges with more than a $50 difference is an extension of the JOIN statement provided, including formatting for currency and filtering with a WHERE clause to capture only the relevant records.

Step-by-step explanation:

To fulfill the hospital financial analyst's request of comparing actual treatment charges to the established normal service charges (service charge) with a difference of more than $50, we must complete the SQL statement. You've already joined the two tables correctly; now you just need to calculate the absolute difference between the two charges and format the output. The SELECT statement needs to include a computed column for the difference and use the ABS() function to ensure it's a positive number, as well as a WHERE clause to filter the results. Here's how you can modify your SQL statement:

SELECT s.service_id,
FORMAT(service_charge, 2) AS "Formatted Service Charge",
FORMAT(actual_charge, 2) AS "Formatted Actual Charge",
FORMAT(ABS(service_charge - actual_charge), 2) AS "Difference"
FROM service_table s
JOIN treatment_table t ON s.service_id = t.service_id
WHERE ABS(service_charge - actual_charge) > 50;

Be sure to replace "service_table" and "treatment_table" with the actual table names if they are different. The FORMAT() function is used above to format the charges to display a dollar sign, comma, and two decimal places as requested.