Need a query which lists:
Patient Name (lname, fname), ID, DOS, Date of Adjustment, Amount of adjustment, Payor Category (patient, insurance or other) for:
- Any patient with 2015 date of service who has any adjustment made in 2016.
- some patients will have adjustments in 2015 but I only need to see those patients and DOS with adjustments made in 2016
- some patients will have one DOS with multiple adjustments, that’s OK, each adjustment and date of adjustment should show on a separate line.
This query illustrates the use of a CASE expression and the date function YEAR.
SELECT lastname, firstname, "ID" , srv_date, cre_date AS adj_date, crsplamt AS adj_amt, adjcode, (CASE WHEN payortype = 'I' THEN 'Insurance' WHEN payortype = 'S' THEN 'Patient' ELSE 'Other' END) AS 'Payer Category' FROM rv_creditsplits WHERE credtype = 'adjustment' AND YEAR(srv_date) = 2015 AND YEAR(cre_date) = 2016 ORDER BY lastname,firstname,"id",srv_date