Link to home
Start Free TrialLog in
Avatar of Culwatrnca11
Culwatrnca11Flag for United States of America

asked on

pull back only claims that meet a certain criteria

Using Oracle SQL Developer - I want to pull back only claims that meet a certain criteria, multiple lines on the same claim number where one line is SURGTG and the other SURGDAY with a copayment  > 0.  ignore everything else.  SURGTG and SURGDAY have to exist together on the same claim and only pull in those lines.

The code i have below pulls back single and multiple line items but dosnt focus on pulling back the above scenario only. See attachment for example. I only want to pull back what is highlighted in yellow, if a single line is SURGTG or SURGDAY ignore, If multiple claims are SURGTG or SURGDAY, ignore. I need to pull back only the claims that are SURGTG and SURGDAY together.

WHERE c.adjst_clm_id is not null and
              ST.NAME in ('Clean','Pending')                
               and C.SERVICE_START_DATE between TO_DATE('08/01/2018','MM/DD/YYYY') and TO_DATE('08/31/2019','MM/DD/YYYY')    
               and (cml.adj_table_name like 'SURGDAY%' or cml.adj_table_name like 'SURGTG%') and PX.COPAYMENT > 0

Open in new window

Scenario-example.xlsm
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Not very much to go on, but maybe as below.  Add/remove as needed from the WHERE clause.

    SELECT Claim_No,
        MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN PX.COPAYMENT ELSE NULL END) AS COPAYMENT1,
        MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN C.SERVICE_START_DATE ELSE NULL END) AS SERVICE_START_DATE1,
        MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN C.SERVICE_END_DATE ELSE NULL END) AS SERVICE_END_DATE1,
        MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN Line ELSE NULL END) AS COPAMENT1,
        MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN ADJUD_FORMULA_NAME ELSE NULL END) AS ADJUD_FORMULA_NAME1,
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN PX.COPAYMENT ELSE NULL END) AS COPAYMENT2,
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN C.SERVICE_START_DATE ELSE NULL END) AS SERVICE_START_DATE2,
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN C.SERVICE_END_DATE ELSE NULL END) AS SERVICE_END_DATE2,
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN Line ELSE NULL END) AS COPAMENT2,
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN ADJUD_FORMULA_NAME ELSE NULL END) AS ADJUD_FORMULA_NAME2    
    FROM ...
    WHERE c.adjst_clm_id is not null and
                  ST.NAME in ('Clean','Pending')                
                   and C.SERVICE_START_DATE between TO_DATE('08/01/2018','MM/DD/YYYY') and TO_DATE('08/31/2019','MM/DD/YYYY')    
                   and (cml.adj_table_name like 'SURGDAY%' or cml.adj_table_name like 'SURGTG%')
                   and PX.COPAYMENT > 0
    GROUP BY Claim_No
    HAVING MAX(CASE WHEN cml.adj_table_name like 'SURGDAY%' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN cml.adj_table_name like 'SURGTG%' THEN 1 ELSE 0 END) = 1
Avatar of Culwatrnca11

ASKER

Scott,

Would this help - Here is the code.  I cleaned it up a little.

SELECT DISTINCT CM1.INTERNAL_ID as CLAIM_NO 
               ,C.ADJST_CLM_ID
             
               ,PX.COPAYMENT
                ,C.SERVICE_START_DATE
                ,C.SERVICE_END_DATE

              
,ct4.abbr as Trait4
                ,CF.NAME AS FORMAT
               	,PX2.LINE

                ,ST.NAME AS STATUS
                ,APS.NAME AS AP_STATUS
                ,STS.NAME as CHECK_STATUS
                ,CASE    WHEN  ( ven.rpt_grpr_03 = 'NC8' OR ven.rpt_grpr_04 = 'RESOLUTE' OR  ven.vendor_name LIKE '%AMERICAN%SPECIALTY%HEALTH%PLAN%OF%CA%' ) AND COALESCE(c.tot_net_payable,0) <= 0 THEN 'Encounters' ELSE 'Claims' END AS INTEXT        
                  ,PE.PROC_CODE
                  ,CMA.ADJUD_FORMULA_NAME        
FROM HCCLAA.AP_CLAIM C        
LEFT JOIN HCCLAA.CLM_MAP CM1 ON CM1.CID = C.CLAIM_ID                
LEFT JOIN HCCLAA.AP_CLAIM_2 C2 ON C.CLAIM_ID = C2.CLAIM_ID                
LEFT JOIN HCCLAA.PATIENT P ON P.PAT_ID = C.PAT_ID                
LEFT JOIN HCCLAA.ZC_CLAIM_FORMAT CF ON C.CLAIM_FORMAT_C = CF.INTERNAL_ID                
LEFT JOIN HCCLAA.ZC_CLM_STATUS ST ON C.STATUS_C = ST.INTERNAL_ID                
LEFT JOIN HCCLAA.ZC_CLM_AP_STAT APS ON C.AP_STS_C = APS.INTERNAL_ID  

    LEFT OUTER JOIN HCCLAA.ap_claim_dofr_chks dofr ON c.claim_id = dofr.claim_id AND dofr.line = 1
    --LEFT OUTER JOIN HCCLAA.zc_check_sts chk ON dofr.pmt_info_chk_sts_c = chk.check_status_c
    LEFT OUTER JOIN HCCLAA.ap_check ckr ON dofr.pmt_info_chk_id = ckr.check_id
    LEFT OUTER JOIN HCCLAA.ZC_CHECK_STS STS ON CKR.CHECK_STATUS_C = STS.CHECK_STATUS_C 


left join HCCLAA.zc_clm_trait_1 ct1 on C2.clm_trait_1_c = ct1.clm_trait_1_c
left join HCCLAA.zc_clm_trait_2 ct2 on C2.clm_trait_2_c = ct2.clm_trait_2_c
left join HCCLAA.zc_clm_trait_3 ct3 on C2.clm_trait_3_c = ct3.clm_trait_3_c
left join HCCLAA.zc_clm_trait_4 ct4 on C2.clm_trait_4_c = ct4.clm_trait_4_c
left join HCCLAA.zc_clm_trait_5 ct5 on C2.clm_trait_5_c = ct5.clm_trait_5_c
LEFT JOIN HCCLAA.CLARITY_VENDOR V ON C.VENDOR_ID = V.VENDOR_ID
LEFT JOIN HCCLAA.clarity_vendor ven ON c.vendor_id = ven.vendor_id    
LEFT JOIN HCCLAA.AP_CLAIM_PX PX ON PX.CLAIM_ID = C.CLAIM_ID                
LEFT JOIN HCCLAA.CLARITY_EAP PE ON PE.PROC_ID = PX.PROC_ID                
LEFT JOIN HCCLAA.AP_CLAIM_PX_2 PX2 ON PX2.CLAIM_ID = C.CLAIM_ID AND PX.LINE = PX2.LINE                
LEFT JOIN HCCLAA.ZC_ALLOWED_CODE AC ON AC.ALLOWED_CODE_C = PX.ALLOWED_CODE_C                
LEFT JOIN HCCLAA.CL_UB_REV_CODE RV ON RV.UB_REV_CODE_ID = PX.UBC_REVENUE_CODE_ID                
LEFT JOIN HCCLAA.CL_CMA CMA ON CMA.ADJUD_FORMULA_ID = PX2.PAT_PAY_CMA 
LEFT JOIN HCCLAA.CLARITY_LOB LOB ON LOB.LOB_ID = C.CLM_LOB_ID                
--LEFT JOIN HCCLAA.CLARITY_EPP EPP ON EPP.BENEFIT_PLAN_ID = PX2.PAT_PAY_EPP        
LEFT JOIN HCCLAA.Clarity_CML cml ON px2.pat_pay_cml = cml.adj_table_cml_id
LEFT JOIN HCCLAA.CLARITY_CML_BCT BCT ON CML.ADJ_TABLE_CML_ID = BCT.ADJ_TABLE_CML_ID
LEFT JOIN HCCLAA.ap_claim_eob_cd cda  ON c.claim_id = cda.claim_id
LEFT JOIN HCCLAA.clarity_eob_code eob ON cda.eob_code_id = eob.eob_code_id   -- For Outstanding Claim Codes 

WHERE c.adjst_clm_id is not null and

              ST.NAME in ('Clean','Pending')                    
                and C.SERVICE_START_DATE between TO_DATE('08/01/2018','MM/DD/YYYY') and TO_DATE('08/31/2019','MM/DD/YYYY')    
                and CF.NAME = 'UB'  	 
               and (cml.adj_table_name like 'SURGDAY%' and PX.COPAYMENT > 0 or cml.adj_table_name like 'SURGTG%' and PX.COPAYMENT > 0)  
  

ORDER BY  CLAIM_NO ASC

Open in new window

So the code above can't yield what you need?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I set up a simple test case based on your Excel file.  It is missing all the joins since I don't have your tables.  Hopefully you can take what I posted and get the idea.

Try this:
select * from (
	select claim_no, copayment, service_start_date, service_end_date, line, adjud_formula_name
		,count( case when ADJUD_FORMULA_NAME like 'SURGDAY%' then 1 end) over(partition by claim_no) surgday_count
		,count( case when ADJUD_FORMULA_NAME like 'SURGTG%' then 1 end) over(partition by claim_no) surgtg_count
	from tab1
	where copayment>0
)
where surgday_count=1 and surgtg_count=1
/

Open in new window


My test case:
/*
drop table tab1 purge;
create table tab1(CLAIM_NO number, COPAYMENT number, SERVICE_START_DATE date, SERVICE_END_DATE date, Line number, ADJUD_FORMULA_NAME varchar2(30));

insert into tab1 values(210284633,20,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210287330,10,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210310523,250,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210311943,250,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210439510,200,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),3,'SURGDAY ($) <EMD> 25817');
insert into tab1 values(210472540,15,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),3,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210479371,300,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),6,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210488979,200,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EMD> 25817');
insert into tab1 values(210489767,250,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210491585,15,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210491982,200,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EMD> 25817');
insert into tab1 values(210515147,250,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210523484,15,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210524869,15,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),3,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210576239,5,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),28,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210579963,200,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),9,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210586211,15,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),7,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210596905,10,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),10,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210597525,10,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),4,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210597648,250,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210607952,200,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210609497,15,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210609961,250,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),4,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210612005,150,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210612322,20,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210612664,150,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),3,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210612716,15,to_date('08/02/2018','MM/DD/YYYY'),to_date('08/02/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210613300,50,to_date('08/01/2018','MM/DD/YYYY'),to_date('08/01/2018','MM/DD/YYYY'),4,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210613475,250,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),2,'SURGDAY ($) <EM> 25287');
insert into tab1 values(210614999,15,to_date('08/03/2018','MM/DD/YYYY'),to_date('08/03/2018','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
insert into tab1 values(213517260,15,to_date('08/06/2018','MM/DD/YYYY'),to_date('08/13/2018','MM/DD/YYYY'),4,'SURGDAY ($) <EM> 25287');
insert into tab1 values(213517260,15,to_date('08/06/2018','MM/DD/YYYY'),to_date('08/13/2018','MM/DD/YYYY'),18,'SURGDAY ($) <EM> 25287');
insert into tab1 values(265441588,50,to_date('12/01/2018','MM/DD/YYYY'),to_date('12/31/2018','MM/DD/YYYY'),16,'SURGDAY ($) <EM> 25287');
insert into tab1 values(265441588,50,to_date('12/01/2018','MM/DD/YYYY'),to_date('12/31/2018','MM/DD/YYYY'),9,'SURGDAY ($) <EM> 25287');
insert into tab1 values(271456230,250,to_date('02/25/2019','MM/DD/YYYY'),to_date('02/26/2019','MM/DD/YYYY'),8,'SURGDAY ($) <EM> 25287');
insert into tab1 values(271456230,250,to_date('02/25/2019','MM/DD/YYYY'),to_date('02/26/2019','MM/DD/YYYY'),12,'SURGDAY ($) <EM> 25287');
insert into tab1 values(271519206,10,to_date('02/20/2019','MM/DD/YYYY'),to_date('02/21/2019','MM/DD/YYYY'),3,'SURGDAY ($) <EM> 25287');
insert into tab1 values(271519206,10,to_date('02/20/2019','MM/DD/YYYY'),to_date('02/21/2019','MM/DD/YYYY'),4,'SURGDAY ($) <EM> 25287');
insert into tab1 values(290880723,150,to_date('02/04/2019','MM/DD/YYYY'),to_date('02/28/2019','MM/DD/YYYY'),7,'SURGDAY ($) <EM> 25287');
insert into tab1 values(290880723,150,to_date('02/04/2019','MM/DD/YYYY'),to_date('02/28/2019','MM/DD/YYYY'),1,'SURGDAY ($) <EM> 25287');
-- only want these
insert into tab1 values(14331516,200,to_date('10/19/2016','MM/DD/YYYY'),to_date('10/19/2016','MM/DD/YYYY'),1,'SURGTG ($) <EM> 25617');
insert into tab1 values(14331516,200,to_date('10/19/2016','MM/DD/YYYY'),to_date('10/19/2016','MM/DD/YYYY'),3,'SURGDAY ($) <EM> 25287');
commit;
*/

Open in new window

Scott,

You had mentioned not much to go by in what I had submitted since i only submitted the WHERE clause so I provided my code so you would have a better idea and something more to work with.
I ran Scott's SQL against my test case and it only returned one row due to the MAX and grouping by claim_no.

If you want the two rows back, like in your Excel file, try what I posted.

If you only want the one row back, let us know.  I don't think you need all the MAX and "group by having" stuff in there.
My query returns both result columns in one row, for both SURGDAY and SURGTG.  I figured it would be easy enough to split that one row into two rows.  I don't know the best way in Oracle, in T-SQL it's a CROSS APPLY.
Scott, how would i work your code into my existing query to get the result i am needing?

slightwv same question.
The important parts of mine are these lines:
,count( case when ADJUD_FORMULA_NAME like 'SURGDAY%' then 1 end) over(partition by claim_no) surgday_count
,count( case when ADJUD_FORMULA_NAME like 'SURGTG%' then 1 end) over(partition by claim_no) surgtg_count

where surgday_count=1 and surgtg_count=1

Open in new window


Just incorporate them into your query.

Something like this:
select CLAIM_NO 
	,ADJST_CLM_ID
	,COPAYMENT
	,SERVICE_START_DATE
	,SERVICE_END_DATE
	,Trait4
	,FORMAT
	,LINE
	,STATUS
	,AP_STATUS
	,CHECK_STATUS
	,INTEXT        
	,PROC_CODE
	,ADJUD_FORMULA_NAME        
from (
	SELECT DISTINCT CM1.INTERNAL_ID as CLAIM_NO 
		,C.ADJST_CLM_ID
		,PX.COPAYMENT
		,C.SERVICE_START_DATE
		,C.SERVICE_END_DATE
		,ct4.abbr as Trait4
		,CF.NAME AS FORMAT
		,PX2.LINE
		,ST.NAME AS STATUS
		,APS.NAME AS AP_STATUS
		,STS.NAME as CHECK_STATUS
		,CASE    WHEN  ( ven.rpt_grpr_03 = 'NC8' OR ven.rpt_grpr_04 = 'RESOLUTE' OR  ven.vendor_name LIKE '%AMERICAN%SPECIALTY%HEALTH%PLAN%OF%CA%' ) AND COALESCE(c.tot_net_payable,0) <= 0 THEN 'Encounters' ELSE 'Claims' END AS INTEXT        
		,PE.PROC_CODE
		,CMA.ADJUD_FORMULA_NAME        
		,count( case when CMA.ADJUD_FORMULA_NAME like 'SURGDAY%' then 1 end) over(partition by CM1.INTERNAL_ID) surgday_count
		,count( case when CMA.ADJUD_FORMULA_NAME like 'SURGTG%' then 1 end) over(partition by CM1.INTERNAL_ID) surgtg_count
	FROM HCCLAA.AP_CLAIM C        
		LEFT JOIN HCCLAA.CLM_MAP CM1 ON CM1.CID = C.CLAIM_ID                
		LEFT JOIN HCCLAA.AP_CLAIM_2 C2 ON C.CLAIM_ID = C2.CLAIM_ID                
		LEFT JOIN HCCLAA.PATIENT P ON P.PAT_ID = C.PAT_ID                
		LEFT JOIN HCCLAA.ZC_CLAIM_FORMAT CF ON C.CLAIM_FORMAT_C = CF.INTERNAL_ID                
		LEFT JOIN HCCLAA.ZC_CLM_STATUS ST ON C.STATUS_C = ST.INTERNAL_ID                
		LEFT JOIN HCCLAA.ZC_CLM_AP_STAT APS ON C.AP_STS_C = APS.INTERNAL_ID  
		LEFT OUTER JOIN HCCLAA.ap_claim_dofr_chks dofr ON c.claim_id = dofr.claim_id AND dofr.line = 1
		--LEFT OUTER JOIN HCCLAA.zc_check_sts chk ON dofr.pmt_info_chk_sts_c = chk.check_status_c
		LEFT OUTER JOIN HCCLAA.ap_check ckr ON dofr.pmt_info_chk_id = ckr.check_id
		LEFT OUTER JOIN HCCLAA.ZC_CHECK_STS STS ON CKR.CHECK_STATUS_C = STS.CHECK_STATUS_C 
		left join HCCLAA.zc_clm_trait_1 ct1 on C2.clm_trait_1_c = ct1.clm_trait_1_c
		left join HCCLAA.zc_clm_trait_2 ct2 on C2.clm_trait_2_c = ct2.clm_trait_2_c
		left join HCCLAA.zc_clm_trait_3 ct3 on C2.clm_trait_3_c = ct3.clm_trait_3_c
		left join HCCLAA.zc_clm_trait_4 ct4 on C2.clm_trait_4_c = ct4.clm_trait_4_c
		left join HCCLAA.zc_clm_trait_5 ct5 on C2.clm_trait_5_c = ct5.clm_trait_5_c
		LEFT JOIN HCCLAA.CLARITY_VENDOR V ON C.VENDOR_ID = V.VENDOR_ID
		LEFT JOIN HCCLAA.clarity_vendor ven ON c.vendor_id = ven.vendor_id    
		LEFT JOIN HCCLAA.AP_CLAIM_PX PX ON PX.CLAIM_ID = C.CLAIM_ID                
		LEFT JOIN HCCLAA.CLARITY_EAP PE ON PE.PROC_ID = PX.PROC_ID                
		LEFT JOIN HCCLAA.AP_CLAIM_PX_2 PX2 ON PX2.CLAIM_ID = C.CLAIM_ID AND PX.LINE = PX2.LINE                
		LEFT JOIN HCCLAA.ZC_ALLOWED_CODE AC ON AC.ALLOWED_CODE_C = PX.ALLOWED_CODE_C                
		LEFT JOIN HCCLAA.CL_UB_REV_CODE RV ON RV.UB_REV_CODE_ID = PX.UBC_REVENUE_CODE_ID                
		LEFT JOIN HCCLAA.CL_CMA CMA ON CMA.ADJUD_FORMULA_ID = PX2.PAT_PAY_CMA 
		LEFT JOIN HCCLAA.CLARITY_LOB LOB ON LOB.LOB_ID = C.CLM_LOB_ID                
		--LEFT JOIN HCCLAA.CLARITY_EPP EPP ON EPP.BENEFIT_PLAN_ID = PX2.PAT_PAY_EPP        
		LEFT JOIN HCCLAA.Clarity_CML cml ON px2.pat_pay_cml = cml.adj_table_cml_id
		LEFT JOIN HCCLAA.CLARITY_CML_BCT BCT ON CML.ADJ_TABLE_CML_ID = BCT.ADJ_TABLE_CML_ID
		LEFT JOIN HCCLAA.ap_claim_eob_cd cda  ON c.claim_id = cda.claim_id
		LEFT JOIN HCCLAA.clarity_eob_code eob ON cda.eob_code_id = eob.eob_code_id   -- For Outstanding Claim Codes 
	WHERE c.adjst_clm_id is not null
		and ST.NAME in ('Clean','Pending')                    
		and C.SERVICE_START_DATE between TO_DATE('08/01/2018','MM/DD/YYYY') and TO_DATE('08/31/2019','MM/DD/YYYY')    
		and CF.NAME = 'UB'  	 
		and (cml.adj_table_name like 'SURGDAY%' and PX.COPAYMENT > 0 or cml.adj_table_name like 'SURGTG%' and PX.COPAYMENT > 0)  
)
where surgday_count=1 and surgtg_count=1
ORDER BY  CLAIM_NO ASC

Open in new window

slightwv,

Thank you...   When I run the query i do not return data.

I removed the WHERE surgday_count=1 and surgtg_count=1 at the end and was able to get data back but getting everything, not the claims containing the surday and surgtg on the same claim. Any ideas?
Without sample data to set up my own test case, I can only guess based on the SQL you provided.

I posted a complete test case based on the data you provided.  Take a little time to understand what it does then you can figure out how to apply that to your tables and data.

Take the first one:
,count( case when CMA.ADJUD_FORMULA_NAME like 'SURGDAY%' then 1 end) over(partition by CM1.INTERNAL_ID) surgday_count
            
It counts all ADJUD_FORMULA_NAME entries that match 'SURGDAY%' grouped by INTERNAL_ID.  Then in the outer where clauses, it looks for rows that only have a single value.

If you load and run my simple test case it might be easier to understand.

If you need us to provide a copy/paste/run solution, we'll need a more complete test case.
SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
slightwv ,

Sorry about that..  Been out of the office, will take a look at your suggestion tomorrow and see if I can incorporate. But yes, i want to pull back the scenario result you showd with claim_no 5, any claims that only contains SurgDay and SurgTG in combination with the same claim number.
slightwv,

I have included some actual data that includes the scenario I am wanting to pull back. Shaved it down a little due to file size.  In this data i believe you should pull back 2 claims that meets the requirement i am looking for, everything else would be ignored.

Thank you
testdata.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Any reason you didn't accept my count window function as the solution since it is the main piece of your SQL?

Glad you got it working.  I had just set up a test with all the rows you provided and I used the same SQL I posted above and received the two claims:
  CLAIM_NO ADJUD_FORMULA_NAME
---------- ------------------------------
  14331516 SURGDAY ($) <EM> 25287
  14331516 SURGTG ($) <EM> 25617
 170578266 SURGTG ($) <EM> 25617
 170578266 SURGDAY ($) <EM> 25287

Open in new window


Not sure you really need:
and (CMA.ADJUD_FORMULA_NAME  like 'SURGDAY%' and PX.COPAYMENT > 0 or CMA.ADJUD_FORMULA_NAME like 'SURGTG%' and PX.COPAYMENT > 0 or

Thinking just the copayment>0 is all you need since the formula_name is covered in the COUNT function.

I would also look at your order of precedence with your 'and' and 'or' in there.
I couldn't get your  WHERE clause to work (below) wasn't finding surgday or surgtg for some reason and the solution that i showed in my last post produced the result.

where surgday_count=1 and surgtg_count=1              

I will take a look at your last suggestion regarding adjud_formula and see if that works as well.

Thank you again.
>>the solution that i showed in my last post produced the result

What you posted used "where x.surgday_count = 1 and x.surgtg_count = 1"

You just used a CTE instead of an inline view.  They are really the same query.  If you generate explain plans for both, I would expect them to be the same.

Again, I used my SQL against your newly provided data and received the two claims.  Were they not the claims you expected?