ugggh.sql

with ins_coverage_type as (
  select IP.id, 
    case 
    when exists (
      select *
      from insurance_coverage IC
      inner join insurance_policies IIP on IC.INSURANCE_POLICY_ID = IIP.id
      inner join TAX_ENTITIES ITE on IIP.TAX_ENTITY_ID = ITE.ID
      where IC.person_id = IP.id and 
        IIP.TAX_ENTITY_ID not in (3300, 3804, 5459, 5717, 3345, 4469, 3592, 135, 2590, 
           6113, 2256, 4270, 4303, 4375, 5, 4049, 5454, 5746, 6104, 6749, 2028, 6987, 2036, 
           6987, 6545, 6323, 6107, 6817, 3833, 3128, 5312) and
        upper(ITE.name) not like '%VISION%' and upper(ITE.name) not like ('%DENTAL%')) then 'Commercial'
    when exists (
      select *
      from insurance_coverage IC 
      inner join insurance_policies IIP on IC.INSURANCE_POLICY_ID = IIP.id
      inner join TAX_ENTITIES ITE on IIP.TAX_ENTITY_ID = ITE.ID
      where IC.person_id = IP.id and 
        IIP.TAX_ENTITY_ID in (3300, 3804, 5459, 5717, 3345, 4469, 3592, 135, 2590)) then 'Medicaid'
    when exists ( 
      select *
      from insurance_coverage IC
      inner join insurance_policies IIP on IC.INSURANCE_POLICY_ID = IIP.id
      inner join TAX_ENTITIES ITE on IIP.TAX_ENTITY_ID = ITE.ID
      where IC.person_id = IP.id and 
        IIP.TAX_ENTITY_ID in (6113, 2256, 4270, 4303, 4375)) then 'Medicare'
    else 'Uninsured' end ins_type
  from people IP
  where exists (
    select *
    from COB_TASKS COB
    where COB.PERSON_ID = IP.ID and COB.DATE_OF_TASK between '3/30/2015' and '10/2/2017' and COB.COB_ID = 3
  )
)
select 
  COB.COB_NUMBER, COB.DATE_OF_TASK, 
  BTE.NAME PROVIDER, 
  P.ID PATIENT_ID, 
  ICT.ins_type,
  case when upper(COB.TASK_COMMENTS) like '%PLUS%CARE%' then 1 else 0 end PC_REFERRAL,
  COND.CODE, 
  SUM(B.PAID_AMOUNT) TOTAL_COST
from cob_tasks COB 
inner join people P on COB.PERSON_ID = P.ID
left  join ins_coverage_type ICT on P.id = ICT.ID
inner join VISIT_TASKS VT on VT.PARENT_TASK_ID = COB.ID
left  join CONDITIONS COND on COND.ID = (
  select first 1 ICOND.ID
  from conditions ICOND
  where ICOND.CURRENT_ID >= 3 and ICOND.TASK_ID = VT.ID 
  order by ICOND.INDEX_NUMBER
)
inner join BILLS B on B.TASK_ID = VT.ID
inner join TAX_ENTITIES BTE on B.ACTUAL_TAX_ID = BTE.ID
where COB.DATE_OF_TASK between '3/30/2015' and '10/2/2017' and COB.COB_ID = 3
group by COB.COB_NUMBER, COB.DATE_OF_TASK, BTE.NAME, P.ID, ICT.ins_type, 
   case when upper(COB.TASK_COMMENTS) like '%PLUS%CARE%' then 1 else 0 end, COND.CODE