Applying Logic to Sets of Rows

I want to add logic that calculates price per claim. Below, there are two claims, one for patient 5, and another for patient 6. Original idea is to create a unique list of patient numbers in a separate table, then sort the original table by these unique patient numbers and run conditional statements to output a single value (reimbursement value).Then iterate through the unique table until completed. Does this sound like a feasible workflow? Not necessarily looking for specific code but more of a workflow/process

For example/context:

PatNo	RevCode	CPT	BilledCharges	DRG
5	141	null	100	880
5	636	J1234	50	null
6	111	null	8000	783
6	636	J1234	300	null

PSYCH look up table: if claim has DRG on table then calculate 75% of BilledCharges for claim.

DRG	Service Category
876	PSYCH
880	PSYCH
881	PSYCH
882	PSYCH
883	PSYCH
884	PSYCH
885	PSYCH
886	PSYCH
887	PSYCH

C- Section look up table: if claim has DRG on table pay $5000 for claim.

DRG	Service
765	C-SECTION
766	C-SECTION
783	C-SECTION
784	C-SECTION
786	C-SECTION
787	C-SECTION
785	C-SECTION
788	C-SECTION

If claim has RevCode 636, then add 50% of charges to claim reimbusment.

OUTPUT:

PatNo	Reimburs.
5	100
6	5150

Answer

So…

Patient 5’s reimbursement is…(75% of 100) + (50% of 50) = 100

Patient 6’s reimbursement is…(5000) + (50% of 300)

Assuming you’ve told us all the rules…

You can left join the tables, to check if values are present there or not, then use case expressions to apply the logic, and finally aggregate it to sum it all up…

SELECT
  YourTable.patno,
  SUM(
    CASE WHEN section.drg IS NOT NULL THEN 5000
         WHEN psych.drg   IS NOT NULL THEN 0.75 * yourTable.billedcharges
         WHEN yourTable.revcode = 636 THEN 0.5 * yourTable.billedcharges
         ELSE 0
    END
  ) 
FROM
  yourTable
LEFT JOIN
  section
    ON section.drg = yourTable.drg
LEFT JOIN
  psych
    ON psych.drg = yourTable.drg
GROUP BY
  yourTable.patno

Please forgive typos, I’m on my phone.