declare @TreatmentType varchar(10)= '02'
declare @EmployeeID varchar(10)= '00000219'
declare @StartDate varchar(10)= '20250115'
declare @CostType varchar(10)= '06'
declare @MedicalFamily varchar(10)= '0004'

select 
  distinct case when cover.TreatmentType = '03' 
  or cover.TreatmentType = '04' then (
    select 
      count(pa42.TreatmentType) as CountTreatmentType 
    from 
      PHRPA0042 pa42 
    where 
      pa42.TreatmentType = @TreatmentType 
      and pa42.EmployeeID = @EmployeeID 
      and pa42.ClaimStatus in('01', '02') 
      and (
        pa42.StartDate <= @StartDate 
        and pa42.StartDate >= (
          select 
            dbo.fn_formatdatetime(
              DATEADD(
                DAY, 
                convert(
                  decimal, 
                  '-' + convert(
                    varchar(50), 
                    cover.TransactionPeriod
                  )
                ), 
                GETDATE()
              ), 
              'yyyymmdd'
            ) AS DateRange
        )
      )
  ) else '0' end as trxcount, 
  cover.TransactionFrequency, 
  cover.TransactionPeriod, 
  cover.CovCompanyInLimit, 
  case when cover.WagetypeBase <> '' then (
    select 
      CAST(
        REPLACE(
          ISNULL(
            dbo.GetPEN(pa04.Amount), 
            '0'
          ), 
          ',', 
          '.'
        ) AS DECIMAL(22, 0)
      )* 5 AS Amount 
    from 
      PHRBNMEDTREATCOVER cov 
      left outer join PHRPYCU0305 CU0305 on CU0305.WageTypeCum = cov.WagetypeBase 
      left outer join phrpa0004 pa04 on pa04.WageType = CU0305.WageType 
    where 
      cov.TreatmentType = @TreatmentType 
      and cov.CostType = @CostType 
      and cov.MedicalFamily = @MedicalFamily 
      and cov.EndDate = '99991231' 
      and pa04.EmployeeID = @EmployeeID 
      and pa04.EndDate = '99991231' 
      and CU0305.EndDate = '99991231'
  ) else '0' end as Amount, 
  case when cover.PayClass <> '*' then (
    select 
      TCOVER.TransactionLimit 
    from 
      PHRBNMEDTREATCOVER TCOVER 
      left outer join PHRPA0003 pa03 on pa03.PayrollClass = TCOVER.PayClass 
    where 
      TCOVER.TreatmentType = @TreatmentType 
      and TCOVER.CostType = @CostType 
      and TCOVER.MedicalFamily = @MedicalFamily 
      and TCOVER.EndDate = '99991231' 
      and pa03.EmployeeID = @EmployeeID 
      and pa03.EndDate = '99991231'
  ) else cover.TransactionLimit end as TransactionLimit 
from 
  PHRBNMEDTREATCOVER cover 
where 
  cover.TreatmentType = @TreatmentType 
  and cover.CostType = @CostType 
  and cover.MedicalFamily = @MedicalFamily 
  and cover.EndDate = '99991231'