with claimBase as ( select c.claimId, c.claimStatus, c.claimStatusDate, c.pendingDate, c.acknowledgedDate, c.lastDraftSavedDate from tbl_TPM_Claims c with (nolock) where c.ownerId = and c.ownerType = and c.pendingDate is not null -- and c.pendingDate >= datefromparts(year(getdate()), month(getdate()), 1) -- and c.pendingDate < dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1)) ), podAgg as ( select pod.claimId, sum( case when pod.status <> 'DENIED' then coalesce(pod.totalRebate,0) else 0 end ) * -1 as potentialRecoveryIncluded, sum( case when pod.status = 'DENIED' then coalesce(pod.totalRebate,0) else 0 end ) * -1 as potentialRecoveryExcluded from tbl_TPM_ProofofDelivery pod with (nolock) where pod.ownerId = and pod.ownerType = and pod.flagged = 'Y' and nullif( pod.exceptionSuppressList, '' ) is null and ( left( pod.flagComment, 17 ) = 'Excluded Operator' or left( pod.flagComment, 15 ) = 'Already Claimed' ) group by pod.claimId ), claimStats as ( select 1 as statId, sum( case when claimStatus = 'PENDING' and acknowledgedDate is null then 1 else 0 end ) as pendingCount, sum( case when claimStatus = 'PENDING' and acknowledgedDate is null and datediff( day, pendingDate, getdate() ) > 5 then 1 else 0 end ) as agingCount, cast( sum( case when acknowledgedDate is not null or claimStatus = 'APPROVED' then 1 else 0 end ) * 1.0 / nullif( sum( case when claimStatus = 'PENDING' or claimStatus = 'APPROVED' or acknowledgedDate is not null then 1 else 0 end ), 0 ) as decimal( 9,4 ) ) as completionPct, sum( case when cb.claimStatus = 'PENDING' and cb.pendingDate is not null and cb.acknowledgedDate is null then coalesce( pa.potentialRecoveryIncluded, 0 ) + coalesce( pa.potentialRecoveryExcluded, 0 ) else 0 end ) as potentialRecovery, sum( case when cb.acknowledgedDate is not null then coalesce( pa.potentialRecoveryIncluded, 0 ) else 0 end) as realizedOffsets, sum( case when cb.acknowledgedDate >= datefromparts( year( getdate() ), month( getdate() ), 1 ) and cb.acknowledgedDate < dateadd( month, 1, datefromparts( year( getdate() ), month( getdate() ), 1) ) then coalesce( pa.potentialRecoveryIncluded, 0 ) else 0 end ) as realizedOffsetsMTD, sum( case when cb.acknowledgedDate >= datefromparts( year( getdate() ), 1, 1 ) and cb.acknowledgedDate < dateadd( year, 1, datefromparts( year( getdate() ), 1, 1 ) ) then coalesce( pa.potentialRecoveryIncluded, 0 ) else 0 end ) as realizedOffsetsYTD from claimBase cb left join podAgg pa on pa.claimId = cb.claimId ) #forJSON( 'claimStats', attributes.datastore, 'statId' )#