Culwatrnca11
asked on
Teradata SQL Assistant - Need assistance with Sub Queries - Get out of spool space error.
I get a spool space error when running report if date range is larger so I have reduced range to return data. I need to run report from 1/1/2017 to current date. I am wondering if my sub queries are correct ? first time creating sub query and not sure if I understand the concept. My question is if I have sub queries does my "Where clause" go at the end of each sub query for the parameters I am focusing on or does it go at end of code? How do I incorporate my parameters into the "where clause" when using sub queries, etc. and prevent "spool space errors". Assistance greatly appreciated.
Focus of report is on the following to narrow what is pulled back:
where
AP.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
PX.POS_TYPE_C IN ('11','15','17','20','49', '50','71', '72')
CPT_CODE IN ('62320','62321','62322',' 62324','62 324','6232 5','62326' ,'62327',' 99151','99 152','9915 3','99154' ,'99155',' 99156','99 157')
Focus of report is on the following to narrow what is pulled back:
where
AP.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
PX.POS_TYPE_C IN ('11','15','17','20','49',
CPT_CODE IN ('62320','62321','62322','
select distinct
'SCAL' as "Region"
,clm.internal_id AS "Claim ID"
,ap.acct_num_with_ven as "Invoice"
,PATIENT.PAT_MRN_ID AS PAT_MRN
,Cast (patient.Birth_Date as date)
,extract(year from current_date) - extract(year from PATIENT.BIRTH_DATE) -
case when current_date (format 'MMDD') (char(4)) <
PATIENT.BIRTH_DATE (format 'MMDD') (char(4)) then 1
else 0 end as AGE
,case ap.status_c
when '1' then 'New'
when '2' then 'Pending'
when '3' then 'Denied'
when '4' then 'Clean'
when '5' then 'Void'
else ' '
end as "Claim Status"
,case when ap.ap_sts_c = 1 then 'Released to Accounts Payable '
when ap.ap_sts_c = 2 then 'Batched for Payment '
when ap.ap_sts_c = 3 then 'EOB Generated'
else 'Not Released to Accounts Payable '
end as "AP Status"
,cast(ap.adj_time as char(10)) as "Status Date"
,ap.ap_status_ap_dt as "Sent to AP Date"
,ckr.ap_run_date as "AP Run Date"
,chk.name as "Check Status"
,CASE
when (ven.vendor_name LIKE '%kaiser foundation hospital%'
OR ven.vendor_name LIKE '%the permanente medical group%'
OR ven.vendor_name LIKE 'kaiser foundation medical group%'
OR ven.vendor_name LIKE '%tpmg%'
OR VEN.VENDOR_EXTERNAL_ID = '1644129787' --HAWAII PERMANENTE MEDICAL
OR VEN.VENDOR_EXTERNAL_ID = '1644120262' --MEDIMPACT
OR VEN.VENDOR_EXTERNAL_ID = '1644120363' --EMPLOYERS MUTUAL INC
OR VEN.VENDOR_EXTERNAL_ID IN ('1644120366') --SHIELD HEALTHCARE
OR AP.VENNET_CONTRACT_ID IN ('150902', '150903') --APRIA HEALTHCARE INC
)
then 'Internal'
else 'External'
end as "Int_Ext"
,case
when ap.orig_rev_clm_id is null and ap.orig_adjst_clm_id is null then 'Original Claim'
when ap.orig_rev_clm_id is null and ap.adjst_clm_id is not null and ap.orig_adjst_clm_id is not null then 'Multiple Adj Interim Claim'
when ap.orig_rev_clm_id is not null then 'Reversal Claim'
when ap.orig_rev_clm_id is null and ap.adjst_clm_id is null and ap.orig_adjst_clm_id is not null then 'Adjusted Claim'
end as "Claim State"
--,hip.registry_name as "Work Queue Pool"
,upper(spec.name) as "Specialty"
,case
when ap.claim_format_c = 1 then 'CMS'
when ap.claim_format_c = 2 then 'UB'
else 'N/A'
end as "Format"
,epp.benefit_plan_name as "Benefit Plan"
,epm.payor_name as "Payor"
,pln.plan_grp_name as "Plan Group"
,ven.vendor_name as "Vendor"
,ven.vendor_external_id as "Vendor ID"
,lob.lob_name as "LOB Name"
,pos.pos_name as "Place of Service"
,ser.prov_name as "Provider"
,ap.service_start_date as "SVC Date"
,ap.service_end_date as "SVC End Date"
,ap.date_received as "Date Received"
,(current_date - ap.date_received - 1) as "Claim Age"
,emp.name as "Entry User"
,ap.entry_date as "Entry Date"
,px.pos_type_c || ' - ' || PT.NAME AS POS_TYPE
,ap.admission_type_c as "Admission type"
,ap.type_of_bill as "Type of Bill"
,coalesce(zc_mtpc.name, 'undefined') as "Payment Method"
,ap.tot_billed_amt as "Total Billed"
,ap.tot_net_payable as "Total Payable"
,ap.tot_allowed_amt as "Total Allowed"
,ap.tot_copay as "Total Copay"
,ap.tot_deductible as "Total Deductible"
,ap.tot_coins as "Total Coinsurance"
,ap.tot_pat_total as "Patient Total"
,ap.tot_prim_pat_port AS "Primary Portion"
,ap.tot_prim_ins_amt as "Primary Insurance"
, CPT_CODE_1
, CPT_CODE_2
, CPT_CODE_3
, CPT_CODE_4
, CPT_CODE_5
, CLM_DX_1
, CLM_DX_2
, CLM_DX_3
, CLM_DX_4
, CLM_DX_5
/*,case
when dx.line = 1 then 'DX_1'
when dx.line = 2 then 'DX_2'
when dx.line = 3 then 'DX_3'
when dx.line = 4 then 'DX_4'
when dx.line = 5 then 'DX_5'
else 'N/A'
END AS "DX_Line" */
--,edg.ref_bill_code
--,eap.proc_code
--,oproc.cpt_code
,cml.adj_table_name
from ap_claim ap
inner join clm_map clm on ap.claim_id = clm.cid
left outer join ap_claim_dofr_chks dofr on ap.claim_id = dofr.claim_id and dofr.line = 1
left outer join zc_check_sts chk on dofr.pmt_info_chk_sts_c = chk.check_status_c
left outer join ap_check ckr on dofr.pmt_info_chk_id = ckr.check_id
left outer join clarity_vendor ven on ap.vendor_id = ven.vendor_id
left outer join zc_specialty spec on ap.assoc_spec_c = spec.specialty_c
left outer join ib_receiver ib on ap.inbasket_message_id = ib.msg_id
left outer join clarity_hip hip on ib.registry_id = hip.registry_id
left outer join coverage cov on ap.coverage_id = cov.coverage_id
left outer join clarity_epp epp on ap.plan_id = epp.benefit_plan_id
left outer join clarity_epm epm on ap.payor_id = epm.payor_id
left outer join plan_grp pln on ap.plan_group_id = pln.plan_grp_id
left outer join clarity_lob lob on ap.clm_lob_id = lob.lob_id
left outer join clarity_pos pos on ap.loc_id = pos.pos_id
left outer join clarity_ser ser on ap.prov_id = ser.prov_id
left outer join clarity_emp emp on ap.entry_user_id = emp.user_id
left outer join ap_claim_2 ap2 on ap.claim_id = ap2.claim_id
left outer join zc_meth_to_pay_clm zc_mtpc on ap.meth_to_pay_clm_c = zc_mtpc.meth_to_pay_clm_c
left outer join patient on ap.pat_id = patient.pat_id
left outer join order_proc oproc on patient.pat_id = oproc.pat_id
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id=px.claim_id
--left outer join ap_claim_dx dx on ap.claim_id = dx.claim_id
--left outer join clarity_edg edg on dx.dx_id = edg.dx_id
left outer join ap_claim_px_2 px2 on ap.claim_id = px2.claim_id
--LEFT OUTER JOIN clarity_eap eap ON px.proc_id = eap.proc_id
left outer join pat_enc enc on oproc.pat_enc_csn_id = enc.pat_enc_csn_id
LEFT OUTER JOIN ZC_POS_TYPE PT ON PX.POS_TYPE_C=PT.POS_TYPE_C
LEFT OUTER JOIN clarity_cml cml ON enc.ben_adj_table_id = cml.adj_table_cml_id
LEFT OUTER JOIN
(
SELECT
PXX.CLAIM_ID
,MAX(CASE WHEN PXX.SEQ_NUM = 1 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_1
,MAX(CASE WHEN PXX.SEQ_NUM = 2 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_2
,MAX(CASE WHEN PXX.SEQ_NUM = 3 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_3
,MAX(CASE WHEN PXX.SEQ_NUM = 4 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_4
,MAX(CASE WHEN PXX.SEQ_NUM = 5 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_5
,MAX(CASE WHEN PXX.SEQ_NUM = 6 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_6
,MAX(CASE WHEN PXX.SEQ_NUM = 7 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_7
,MAX(CASE WHEN PXX.SEQ_NUM = 8 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_8
,MAX(CASE WHEN PXX.SEQ_NUM = 9 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_9
,MAX(CASE WHEN PXX.SEQ_NUM = 10 THEN PROC_CODE ELSE NULL END) AS CPT_CODE_10
FROM
(
SELECT
PX.CLAIM_ID
,PX.PROC_CODE
,ROW_NUMBER() OVER (PARTITION BY PX.CLAIM_ID ORDER BY PX.PROC_CODE ASC) AS SEQ_NUM
FROM (SELECT DISTINCT A.CLAIM_ID, C.PROC_CODE || ' - ' || C.PROC_NAME AS PROC_CODE
FROM AP_CLAIM_PX A
INNER JOIN AP_CLAIM B ON A.CLAIM_ID= B.CLAIM_ID --AND B.STATUS_C = 2
INNER JOIN CLARITY_EAP C ON A.PROC_ID = C.PROC_ID
) PX
) PXX
GROUP BY PXX.CLAIM_ID
) EAP ON AP.CLAIM_ID = EAP.CLAIM_ID
LEFT JOIN
(
SELECT
PXX.CLAIM_ID
,MAX(CASE WHEN PXX.SEQ_NUM = 1 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_1
,MAX(CASE WHEN PXX.SEQ_NUM = 2 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_2
,MAX(CASE WHEN PXX.SEQ_NUM = 3 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_3
,MAX(CASE WHEN PXX.SEQ_NUM = 4 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_4
,MAX(CASE WHEN PXX.SEQ_NUM = 5 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_5
,MAX(CASE WHEN PXX.SEQ_NUM = 6 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_6
,MAX(CASE WHEN PXX.SEQ_NUM = 7 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_7
,MAX(CASE WHEN PXX.SEQ_NUM = 8 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_8
,MAX(CASE WHEN PXX.SEQ_NUM = 9 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_9
,MAX(CASE WHEN PXX.SEQ_NUM = 10 THEN REF_BILL_CODE ELSE NULL END) AS CLM_DX_10
FROM
(
SELECT
PX.CLAIM_ID
,PX.REF_BILL_CODE
,ROW_NUMBER() OVER (PARTITION BY PX.CLAIM_ID ORDER BY PX.REF_BILL_CODE ASC) AS SEQ_NUM
FROM (SELECT DISTINCT A.CLAIM_ID , F.REF_BILL_CODE || ' - '|| F.DX_NAME AS REF_BILL_CODE
FROM AP_CLAIM_DX A
INNER JOIN AP_CLAIM B ON A.CLAIM_ID = B.CLAIM_ID --AND B.STATUS_C = 2
INNER JOIN CLARITY_EDG F ON A.DX_ID = F.DX_ID
) PX
) PXX
GROUP BY PXX.CLAIM_ID
) DX ON AP.CLAIM_ID = DX.CLAIM_ID
--left outer join arpb_transactions arpb on patient.pat_id = arpb.patient_id
--left outer Join clarity_tdl_tran tdl ON arpb.patient_id = tdl.int_pat_id-- Origional Inner Join
where
AP.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
and
PX.POS_TYPE_C IN ('11','15','17','20','49','50','71','72')
--AND
--CPT_CODE_1 IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157')
--and
--oproc.cpt_code IN ('62320','62321','62322','62324','62324','62325','62326','62327','99151','99152','99153','99154','99155','99156','99157')
ORDER BY clm.internal_id ASC;
The bottom line is let SQL Server perform tasks in smaller chunks instead of everything in one go. Divide and conquer!
Oh dear. NEVER use "select distinct" on a huge query - very bad for performance. Initial thought only, intened to look for more soon.
suggested reading: https://www.experts-exchange.com/articles/12282/Select-Distinct-is-returning-duplicates.html
in particular the links at the end
suggested reading: https://www.experts-exchange.com/articles/12282/Select-Distinct-is-returning-duplicates.html
in particular the links at the end
How much repetition do you get from this query? i.e. are the rows distinct?
Qry-B This query allows the LEFT JOIN to work correctly
Qry-C This query ignores the left join because the where clause overrides any unmatched rows between ap and px
{+edit} please note change to previous line "of results" added
{+edit 2} sorry changed qry-b & qry-c may need to refresh (pt alias removed)
SELECT
ap.acct_num_with_ven AS "Invoice"
, CASE ap.status_c
WHEN '1' THEN 'New'
WHEN '2' THEN 'Pending'
WHEN '3' THEN 'Denied'
WHEN '4' THEN 'Clean'
WHEN '5' THEN 'Void'
ELSE ' ' END AS "Claim Status"
, CASE
WHEN ap.ap_sts_c = 1 THEN 'Released to Accounts Payable '
WHEN ap.ap_sts_c = 2 THEN 'Batched for Payment '
WHEN ap.ap_sts_c = 3 THEN 'EOB Generated'
ELSE 'Not Released to Accounts Payable ' END AS "AP Status"
, CAST(ap.adj_time AS char(10)) AS "Status Date"
, ap.ap_status_ap_dt AS "Sent to AP Date"
, CASE
WHEN ap.orig_rev_clm_id IS NULL AND
ap.orig_adjst_clm_id IS NULL THEN 'Original Claim'
WHEN ap.orig_rev_clm_id IS NULL AND
ap.adjst_clm_id IS NOT NULL AND
ap.orig_adjst_clm_id IS NOT NULL THEN 'Multiple Adj Interim Claim'
WHEN ap.orig_rev_clm_id IS NOT NULL THEN 'Reversal Claim'
WHEN ap.orig_rev_clm_id IS NULL AND
ap.adjst_clm_id IS NULL AND
ap.orig_adjst_clm_id IS NOT NULL THEN 'Adjusted Claim' END AS "Claim State"
, CASE
WHEN ap.claim_format_c = 1 THEN 'CMS'
WHEN ap.claim_format_c = 2 THEN 'UB'
ELSE 'N/A' END AS "Format"
, ap.service_start_date AS "SVC Date"
, ap.service_end_date AS "SVC End Date"
, ap.date_received AS "Date Received"
, (current_date - ap.date_received - 1) AS "Claim Age"
, ap.entry_date AS "Entry Date"
, ap.admission_type_c AS "Admission type"
, ap.type_of_bill AS "Type of Bill"
, ap.tot_billed_amt AS "Total Billed"
, ap.tot_net_payable AS "Total Payable"
, ap.tot_allowed_amt AS "Total Allowed"
, ap.tot_copay AS "Total Copay"
, ap.tot_deductible AS "Total Deductible"
, ap.tot_coins AS "Total Coinsurance"
, ap.tot_pat_total AS "Patient Total"
, ap.tot_prim_pat_port AS "Primary Portion"
, ap.tot_prim_ins_amt AS "Primary Insurance"
FROM ap_claim ap
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
Is it fast? Does it run out of spool space?Qry-B This query allows the LEFT JOIN to work correctly
SELECT
ap.acct_num_with_ven AS "Invoice"
, px.pos_type_c
FROM ap_claim ap
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id = px.claim_id
AND PX.POS_TYPE_C IN ('11', '15', '17', '20', '49', '50', '71', '72')
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
Qry-C This query ignores the left join because the where clause overrides any unmatched rows between ap and px
SELECT
ap.acct_num_with_ven AS "Invoice"
, px.pos_type_c
FROM ap_claim ap
LEFT OUTER JOIN ap_claim_px px ON ap.claim_id = px.claim_id
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
AND PX.POS_TYPE_C IN ('11', '15', '17', '20', '49', '50', '71', '72')
Is there a difference OF RESULTS between Qry-B and Qry-C? if not do you really need that left join? (use an inner join instead){+edit} please note change to previous line "of results" added
{+edit 2} sorry changed qry-b & qry-c may need to refresh (pt alias removed)
REMOVE everything you do not actually use.
There are MANY joined tables - is EVERY one of those actually used?
(in the select clause or the where clause or required for joining of tables)
There are many instances of double-dashes in your code (e.g. all dx.* columns) but you still join to the subquery dx
Why?
Do you REALLY need DISTINCT here?
There are MANY joined tables - is EVERY one of those actually used?
(in the select clause or the where clause or required for joining of tables)
There are many instances of double-dashes in your code (e.g. all dx.* columns) but you still join to the subquery dx
Why?
Do you REALLY need DISTINCT here?
SELECT /* DISTINCT ???????????? is this needed really */
A.CLAIM_ID
, F.REF_BILL_CODE + ' - ' + F.DX_NAME AS REF_BILL_CODE
FROM AP_CLAIM_DX A
INNER JOIN AP_CLAIM B ON A.CLAIM_ID = B.CLAIM_ID --AND B.STATUS_C = 2
INNER JOIN CLARITY_EDG F ON A.DX_ID = F.DX_ID
looks like the join to alias B is redundant now - so remove it
@Nitin.
I disagree completely about the use of subqueries. Not only are they useful, in many cases they are essential to a query and they can benefit performance. Also note this is Teradata not SQL Server. The syntax is similar but there are differences: e.g. "current_date" instead of getdate()
I disagree completely about the use of subqueries. Not only are they useful, in many cases they are essential to a query and they can benefit performance. Also note this is Teradata not SQL Server. The syntax is similar but there are differences: e.g. "current_date" instead of getdate()
ASKER
Hi PortletPaul,
I focused on your first post first "How much repetition do you get from this query? i.e. are the rows distinct?" - returned data extremely fast after execution 802322 rows (00:00:09) then I pulled in 2000 lines. I re ran again and pulled in all 802322 rows at 00:12:01 never ran out of spool space. Not much repetition from what I can see but if there was any it is because there is different information in some of the other fields (ie AP Status, Claim State, Entry Date) .
look at your other suggestions
I focused on your first post first "How much repetition do you get from this query? i.e. are the rows distinct?" - returned data extremely fast after execution 802322 rows (00:00:09) then I pulled in 2000 lines. I re ran again and pulled in all 802322 rows at 00:12:01 never ran out of spool space. Not much repetition from what I can see but if there was any it is because there is different information in some of the other fields (ie AP Status, Claim State, Entry Date) .
look at your other suggestions
ASKER
I went noticed the date range was within last week so I changed it to 1/1/2017 to current and re ran just to make sure no spooling error. Original query was able to still handle it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Regarding your existing query: it lives on the knife edge of failure, but it is your query (and reputation) that that you risk.
Your choice to make.
Your choice to make.
ASKER
Query B vs C.. Both Quieries return same data, B seems to take longer to run vs C
ASKER
B actually generated a Ran out of memory error returning row 3406805
ASKER
PortletPaul,
Thank you for walking me through each step, breaking them down so I can see the results etc.. much apprecated. My goal is to do it right..
In your final query trying to run... its telling me clm does not exist. I removed the "etc" that you had put in showing me how each section is put together. Not sure why it cant find slm, the from clause looks right.
Thank you for walking me through each step, breaking them down so I can see the results etc.. much apprecated. My goal is to do it right..
In your final query trying to run... its telling me clm does not exist. I removed the "etc" that you had put in showing me how each section is put together. Not sure why it cant find slm, the from clause looks right.
ASKER
another question...
FROM (
SELECT
ap.acct_num_with_ven
, ap.adj_time
, ap.ap_status_ap_dt
, ap.service_start_date
, ap.service_end_date
, ap.date_received
, ap.entry_date
, ap.admission_type_c
, ap.type_of_bill
, ap.tot_billed_amt
, ap.tot_net_payable
, ap.tot_allowed_amt
, ap.tot_copay
, ap.tot_deductible
, ap.tot_coins
, ap.tot_pat_total
, ap.tot_prim_pat_port
, ap.tot_prim_ins_amt
, clm.internal_id
FROM ap_claim ap
INNER JOIN clm_map clm ON ap.claim_id = clm.cid
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
) D
this Subquery.. the )D on the last line.. this is the name of the query, correct?
FROM (
SELECT
ap.acct_num_with_ven
, ap.adj_time
, ap.ap_status_ap_dt
, ap.service_start_date
, ap.service_end_date
, ap.date_received
, ap.entry_date
, ap.admission_type_c
, ap.type_of_bill
, ap.tot_billed_amt
, ap.tot_net_payable
, ap.tot_allowed_amt
, ap.tot_copay
, ap.tot_deductible
, ap.tot_coins
, ap.tot_pat_total
, ap.tot_prim_pat_port
, ap.tot_prim_ins_amt
, clm.internal_id
FROM ap_claim ap
INNER JOIN clm_map clm ON ap.claim_id = clm.cid
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
) D
this Subquery.. the )D on the last line.. this is the name of the query, correct?
D on the last line.. this is the name of the query, correct?
Correct
(d for "derived". Using a subquery in this fashion is also known as a "derived table')
Good luck.
Please remember to do the "fancy stuff" like case expressions/calculations and user friendly column names LAST
Personally I never use column aliases containing spaces, it keeps the code easier to read in my view - but that is entirely up to you.
Correct
(d for "derived". Using a subquery in this fashion is also known as a "derived table')
Good luck.
Please remember to do the "fancy stuff" like case expressions/calculations and user friendly column names LAST
Personally I never use column aliases containing spaces, it keeps the code easier to read in my view - but that is entirely up to you.
ASKER
Here they go Alias crazy which makes it extremely hard to follow someone elses code.
I tried running your last code but get an error message on "clm" says it does not exist.
SELECT
'SCAL' AS "Region"
, clm.internal_id AS "Claim ID"
, ap.acct_num_with_ven AS "Invoice"
, PATIENT.PAT_MRN_ID AS PAT_MRN
, CAST(patient.Birth_Date AS DATE)
--,extract(year from current_date) - extract(year from PATIENT.BIRTH_DATE) -
-- case when current_date (format 'MMDD') (char(4)) <
-- PATIENT.BIRTH_DATE (format 'MMDD') (char(4)) then 1
-- else 0 end as AGE
, CASE ap.status_c
WHEN '1' THEN 'New'
WHEN '2' THEN 'Pending'
WHEN '3' THEN 'Denied'
WHEN '4' THEN 'Clean'
WHEN '5' THEN 'Void'
ELSE ' ' END AS "Claim Status"
, CASE
WHEN ap.ap_sts_c = 1 THEN 'Released to Accounts Payable '
WHEN ap.ap_sts_c = 2 THEN 'Batched for Payment '
WHEN ap.ap_sts_c = 3 THEN 'EOB Generated'
ELSE 'Not Released to Accounts Payable ' END AS "AP Status"
, CAST(ap.adj_time AS CHAR(10)) AS "Status Date"
, ap.ap_status_ap_dt AS "Sent to AP Date"
, ckr.ap_run_date AS "AP Run Date"
, chk.name AS "Check Status"
FROM (
SELECT
ap.acct_num_with_ven
, ap.adj_time
, ap.ap_status_ap_dt
, ap.service_start_date
, ap.service_end_date
, ap.date_received
, ap.entry_date
, ap.admission_type_c
, ap.type_of_bill
, ap.tot_billed_amt
, ap.tot_net_payable
, ap.tot_allowed_amt
, ap.tot_copay
, ap.tot_deductible
, ap.tot_coins
, ap.tot_pat_total
, ap.tot_prim_pat_port
, ap.tot_prim_ins_amt
, clm.internal_id
FROM ap_claim ap
INNER JOIN clm_map clm ON ap.claim_id = clm.cid
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
) D
if I select and run from the select to the end of of current_date it runs in that instance but not if I include FROM ( to the )D
I tried running your last code but get an error message on "clm" says it does not exist.
SELECT
'SCAL' AS "Region"
, clm.internal_id AS "Claim ID"
, ap.acct_num_with_ven AS "Invoice"
, PATIENT.PAT_MRN_ID AS PAT_MRN
, CAST(patient.Birth_Date AS DATE)
--,extract(year from current_date) - extract(year from PATIENT.BIRTH_DATE) -
-- case when current_date (format 'MMDD') (char(4)) <
-- PATIENT.BIRTH_DATE (format 'MMDD') (char(4)) then 1
-- else 0 end as AGE
, CASE ap.status_c
WHEN '1' THEN 'New'
WHEN '2' THEN 'Pending'
WHEN '3' THEN 'Denied'
WHEN '4' THEN 'Clean'
WHEN '5' THEN 'Void'
ELSE ' ' END AS "Claim Status"
, CASE
WHEN ap.ap_sts_c = 1 THEN 'Released to Accounts Payable '
WHEN ap.ap_sts_c = 2 THEN 'Batched for Payment '
WHEN ap.ap_sts_c = 3 THEN 'EOB Generated'
ELSE 'Not Released to Accounts Payable ' END AS "AP Status"
, CAST(ap.adj_time AS CHAR(10)) AS "Status Date"
, ap.ap_status_ap_dt AS "Sent to AP Date"
, ckr.ap_run_date AS "AP Run Date"
, chk.name AS "Check Status"
FROM (
SELECT
ap.acct_num_with_ven
, ap.adj_time
, ap.ap_status_ap_dt
, ap.service_start_date
, ap.service_end_date
, ap.date_received
, ap.entry_date
, ap.admission_type_c
, ap.type_of_bill
, ap.tot_billed_amt
, ap.tot_net_payable
, ap.tot_allowed_amt
, ap.tot_copay
, ap.tot_deductible
, ap.tot_coins
, ap.tot_pat_total
, ap.tot_prim_pat_port
, ap.tot_prim_ins_amt
, clm.internal_id
FROM ap_claim ap
INNER JOIN clm_map clm ON ap.claim_id = clm.cid
WHERE ap.SERV_DATE BETWEEN '03/12/2017' AND CURRENT_DATE
) D
if I select and run from the select to the end of of current_date it runs in that instance but not if I include FROM ( to the )D
ASKER
Did I select wrong thing, dont want to close ticket I want to award point as solved..
ASKER
Thank you for the assistance.. Worked all evening on my query and now able to run for the time frame without getting spool space errors. Your feedback was very helpful.
Really pleased this helped you.
I'm quite sure your queries will be better from now on - because I'm sure you will not want to go through that pain again :=)
Cheers,
Paul
I'm quite sure your queries will be better from now on - because I'm sure you will not want to go through that pain again :=)
Cheers,
Paul
It is not clear if this is part of the stored procedure or you are just running this script in Management Studio. In either case I would suggest (depending on volume of data) to create either physical temp tables (#) or the table variables (@).
For example, for the following two things you are trying to achieve, I would create a table variable, put these values in it and INNER JOIN on that table.
PX.POS_TYPE_C IN ('11','15','17','20','49',
CPT_CODE IN ('62320','62321','62322','
From the look at it I suggest you implement the temp table / table variable approach and then keep on improving on it. When you create temp table / table variable, you can also index these tables on join columns thereby significantly improving the performance.