Editing Mental health expansion (section)

====People waiting >5 days for first contact====

<syntaxhighlight lang="sql" line>
select
  ContractID,
  ContractName,
  ClientID,
  EpisodeContractID,
  NoteDate,
  ContactTypeID,
  ContactTypeActive,
  case
    when ContactTypeID in (
      2, 3, 4, 13, 15, 19, 20, 24, 27, 34, 35, 37, 38, 39,
      41, 63, 68, 69, 72, 75, 76, 77, 83, 85, 86, 88, 89,
      90, 93, 94, 97, 98
    ) then 1
    else 0
  end as [Direct contact]
into #mhbase
from vClientNotesDocuments
where 
  ContractID in (13,11)
  and datepart(mm, NoteDate) = 6
  and datepart(yy, NoteDate) = 2025
  and UserID <> 1111;

select 
  eco.EpisodeContractID,
  ec.ContractName,
  ec.EpisodeContractStartDate
into #mhnewec
from vEpisodeContracts ec
join vClientEpisodeContractOrder eco
  on ec.EpisodeContractID = eco.EpisodeContractID
where ec.ContractID in (13,11)
  and datepart(mm, ec.EpisodeContractStartDate) = 6
  and datepart(yy, ec.EpisodeContractStartDate) = 2025
  and ec.EpisodeContractID in (select distinct EpisodeContractID from #mhbase);


-- Get earliest direct contact for each EpisodeContract
with ctea as (
    select
        cnd.EpisodeContractID,
        min(cnd.NoteDate) as firstnote
    from vClientNotesDocuments cnd
    where 
        cnd.EpisodeContractID in (select EpisodeContractID from #mhnewec)
        and ContactTypeID in (
            2, 3, 4, 13, 15, 19, 20, 24, 27, 34,
            35, 37, 38, 39, 41, 63, 68, 69, 72,
            75, 76, 77, 83, 85, 86, 88, 89, 90,
            93, 94, 97, 98
        )
    group by cnd.EpisodeContractID
)

-- Count people who waited >5 days for first contact, only if there's enough time in the month
select
    ec.ContractName,
    count(*) as ClientsWaitingOver5Days
from #mhnewec ec
left join ctea c on ec.EpisodeContractID = c.EpisodeContractID
where 
    datediff(day, ec.EpisodeContractStartDate, c.firstnote) > 5
    and datediff(day, ec.EpisodeContractStartDate, eomonth(ec.EpisodeContractStartDate)) > 5
group by ec.ContractName
;
</syntaxhighlight>