Dear friends,
This the query provided by my client. Here TYPE_OF_VISIT is very important.There are 3 types 1, 2, 4. For 1 and 4 my values are matching but when i am using if condition for Visit Type two value is not matching. I need to use the case condition for type 2 for creating the type 3 .
i am using this if condtion.
if(TYPE_OF_VISIT='2',if(IH_POS_POS_ID='62','3','2'),TYPE_OF_VISIT).
-- VISIT TYPE REVENUE
SELECT
CASE VST.TYPE_OF_VISIT WHEN 2 THEN CASE OPV.IH_OUC_CLN_IH_POS_POS_ID WHEN 62 THEN 3 ELSE 2 END ELSE VST.TYPE_OF_VISIT END TYPE_OF_VISIT,
SUM(CHS.SELFPAYER_LC - CHS.DISCOUNT) GUEST_SHARE,
SUM(CHS.PAYER1_LC) COMPANY_SHARE,
FROM
IDEAL.IH_BIL_CHARGE_SLIPS CHS
LEFT JOIN IDEAL.IH_EMR_VISITS VST ON VST.ID = CHS.IH_EMR_VST_ID
LEFT JOIN IDEAL.IH_EMR_OUTPATIENT_VISITS OPV ON OPV.ID = VST.IH_EMR_OUT_ID
LEFT JOIN IDEAL.IH_EMR_INPATIENT_VISITS INV ON INV.ID = VST.IH_EMR_INV_ID
WHERE
CHS.STATUS = 2
AND CHS.FULFILLED = 2
AND CHS.PKG_ITEM = 1
AND CHS.RFND_ITEM = 1
AND VST.STAGE != 7
AND TRUNC(CHS.DATE_DATE) = TRUNC(SYSDATE) - 1
GROUP BY
TRUNC(CHS.DATE_DATE,'DD'),
CASE VST.TYPE_OF_VISIT WHEN 2 THEN CASE OPV.IH_OUC_CLN_IH_POS_POS_ID WHEN 62 THEN 3 ELSE 2 END ELSE VST.TYPE_OF_VISIT END
/
