Here is the query from a non-partitioned table.
define Rprt_ID = “75401550186”; – ‘DATAMSGCNT_DATAMSG_ID’ value
– Client Records
select C.CLIENT_PROCESSED_CNT “Client Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.CLIENT_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “Client % of Total”
, C.CLIENT_UPSERT_ACCEPTED_CNT “Client Accepted”
, round(case when C.CLIENT_PROCESSED_CNT <> 0 then 100*C.CLIENT_UPSERT_ACCEPTED_CNT/C.CLIENT_PROCESSED_CNT
else 0
end, 2) “% Client Accepted”
, C.CLIENT_UPSERT_WARNING_CNT “C Accepted W/ Warnings”
, round(case when C.CLIENT_PROCESSED_CNT <> 0 then 100*C.CLIENT_UPSERT_WARNING_CNT/C.CLIENT_PROCESSED_CNT
else 0
end, 2) “% Client Accepted W/ Warnings”
, C.CLIENT_UPSERT_REJECTED_CNT “Client Rejected”
, round(case when C.CLIENT_PROCESSED_CNT <> 0 then 100*C.CLIENT_UPSERT_REJECTED_CNT/C.CLIENT_PROCESSED_CNT
else 0
end, 2) “% Client Rejected”
– Service Episode Records
, C.SVC_EPSD_PROCESSED_CNT “Svc Episode Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.SVC_EPSD_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “Svc Epsd % of Total”
, C.SVC_EPSD_UPSERT_ACCEPTED_CNT “Svc Episode Accepted”
, round(case when C.SVC_EPSD_PROCESSED_CNT <> 0 then 100*C.SVC_EPSD_UPSERT_ACCEPTED_CNT/C.SVC_EPSD_PROCESSED_CNT
else 0
end, 2) “% Svc Episode Accepted”
, C.SVC_EPSD_UPSERT_WARNING_CNT “Svc Epsd Acptd W/ Warnings”
, round(case when C.SVC_EPSD_PROCESSED_CNT <> 0 then 100*C.SVC_EPSD_UPSERT_WARNING_CNT/C.SVC_EPSD_PROCESSED_CNT
else 0
end, 2) “% Svc Epsd Acptd W/ Warnings”
, C.SVC_EPSD_UPSERT_REJECTED_CNT “Svc Episode Rejected”
, round(case when C.SVC_EPSD_PROCESSED_CNT <> 0 then 100*C.SVC_EPSD_UPSERT_REJECTED_CNT/C.SVC_EPSD_PROCESSED_CNT
else 0
end, 2) “% Svc Epsd Rejected”
– Service Event Records
, C.SVC_EVNT_PROCESSED_CNT “Svc Event Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.SVC_EVNT_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “Svc Evt % of Total”
, C.SVC_EVNT_RECORD_LOADED_CNT “Svc Event Accepted”
, round(case when C.SVC_EVNT_PROCESSED_CNT <> 0 then 100*C.SVC_EVNT_RECORD_LOADED_CNT/C.SVC_EVNT_PROCESSED_CNT
else 0
end, 2) “% Svc Event Accepted”
, C.SVC_EVNT_UPSERT_WARNING_CNT “Svc Evt Acptd W/ Warnings”
, round(case when C.SVC_EVNT_PROCESSED_CNT <> 0 then 100*C.SVC_EVNT_UPSERT_WARNING_CNT/C.SVC_EVNT_PROCESSED_CNT
else 0
end, 2) “% Svc Evt Acptd W/ Warnings”
, C.SVC_EVNT_UPSERT_REJECTED_CNT “Svc Evt Rejected”
, round(case when C.SVC_EVNT_PROCESSED_CNT <> 0 then 100*C.SVC_EVNT_UPSERT_REJECTED_CNT/C.SVC_EVNT_PROCESSED_CNT
else 0
end, 2) “% Svc Evt Rejected”
– Diagnostic Records
, C.DIAGNOSTIC_PROCESSED_CNT “Diagnostic Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.DIAGNOSTIC_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “Diagnostic % of Total”
, C.DIAGN_UPSERT_ACCEPTED_CNT “Diagnostic Accepted”
, round(case when C.DIAGNOSTIC_PROCESSED_CNT <> 0 then 100*C.DIAGN_UPSERT_ACCEPTED_CNT/C.DIAGNOSTIC_PROCESSED_CNT
else 0
end, 2) “% Diagnostic Accepted”
, C.DIAGN_UPSERT_WARNING_CNT “Diagnostic Acptd W/ Warnings”
, round(case when C.DIAGNOSTIC_PROCESSED_CNT <> 0 then 100*C.DIAGN_UPSERT_WARNING_CNT/C.DIAGNOSTIC_PROCESSED_CNT
else 0
end, 2) “% Diagnostic Acptd W/ Warnings”
, C.DIAGN_UPSERT_REJECTED_CNT “Diagnostic Rejected”
, round(case when C.DIAGNOSTIC_PROCESSED_CNT <> 0 then 100*C.DIAGN_UPSERT_REJECTED_CNT/C.DIAGNOSTIC_PROCESSED_CNT
else 0
end, 2) “% Diagnostic Rejected”
– HONOS Records
, C.HONOS_PROCESSED_CNT “HONOS Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.HONOS_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “HONOS % of Total”
, C.HONOS_UPSERT_ACCEPTED_CNT “HONOS Accepted”
, round(case when C.HONOS_PROCESSED_CNT <> 0 then 100*C.HONOS_UPSERT_ACCEPTED_CNT/C.HONOS_PROCESSED_CNT
else 0
end, 2) “% HONOS Accepted”
, C.HONOS_UPSERT_WARNING_CNT “HONOS Acptd W/ Warnings”
, round(case when C.HONOS_PROCESSED_CNT <> 0 then 100*C.HONOS_UPSERT_WARNING_CNT/C.HONOS_PROCESSED_CNT
else 0
end, 2) “% HONOS Acptd W/ Warnings”
, C.HONOS_UPSERT_REJECTED_CNT “HONOS Rejected”
, round(case when C.HONOS_PROCESSED_CNT <> 0 then 100*C.HONOS_UPSERT_REJECTED_CNT/C.HONOS_PROCESSED_CNT
else 0
end, 2) “% HONOS Rejected”
---- Substance Use Records
, C.SU_PROCESSED_CNT “Substance Use Submitted”
, round(case when (C.FILE_RECORD_COUNT-1/(Header)/ )<> 0 then 100*C.SU_PROCESSED_CNT/(C.FILE_RECORD_COUNT-1)
else 0
end, 2) “Sbst Use % of Total”
, C.SU_UPSERT_ACCEPTED_CNT “Sbst Use Accepted”
, round(case when C.SU_PROCESSED_CNT <> 0 then 100*C.SU_UPSERT_ACCEPTED_CNT/C.SU_PROCESSED_CNT
else 0
end, 2) “% Sbst Use Accepted”
, C.SU_UPSERT_WARNING_CNT “SU Acptd W/ Warnings”
, round(case when C.SU_PROCESSED_CNT <> 0 then 100*C.SU_UPSERT_WARNING_CNT/C.SU_PROCESSED_CNT
else 0
end, 2) “% SU Acptd W/ Warnings”
, C.SU_UPSERT_REJECTED_CNT “SU Rejected”
, round(case when C.SU_PROCESSED_CNT <> 0 then 100*C.SU_UPSERT_REJECTED_CNT/C.SU_PROCESSED_CNT
else 0
end, 2) “% SU Rejected”
from AHIP.DC_MHAMRR_CONTROL_HEADER c
where c.DATAMSGCNT_DATAMSG_ID = &Rprt_ID – Data submission ID
;