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