STS002a

The cohort of Requests for Support where sequel to Request was ‘Short Term Support to Maximise Independence’ is created in this STS001 script and forms the base of the STS002a script here (a few steps into the process)

All Service Events (that is, Requests, Assessments, Reviews and Services) beginning on or before 31/03/2024. No End Date is applied yet

Client Type is a Service User

Date of Death on or after 01/04/2023 or no Date of Death

Give all clients a new Identifier using a combination of NHS Number (where available) and Local Authority ID to allocate a unique ID to each Client in the cohort (see Summary for more information)

Using a prescribed list of unique fields, create a new Event ID made from a concatenation of these fields to help identify distinct Service events for each client

Concatenation of LA Code, Client ID, Event-Start Date, Service Type, Service Component and Delivery Mechanism

Where multiple instances of Events with the same Event ID have been submitted over time, select only the last version submitted before dd/mm/yy (that is, last submission within the LA submission window) to ensure process uses most up-to-date version of each event

Select LA Code, Client ID, Event Start Date, Event End Date and Event Outcome from all rows in the ‘Subset’ table where:

1. Event Type is ‘Service’ and Service Type is ‘Short Term Support: ST-Max’
2. LA Code, Client ID and Event Start corresponds to same instance of LA Code, Client ID and Sequel Event Start Date in the ‘STS002a Cohort’ table created in the STS001 (Requests) code

For Events with no Event End Date (End Date is NULL) populate End Date with today’s date as a placeholder to allow the next step to work correctly

Cluster together separate ST-Max Events that occur for the same Client, within a short time span, into one combined Reablement period, to avoid over-counting ST-Max Events

A new ST-Max Event starting within 1 day of a previous ST-Max Event ending is considered to be the same ST-Max Event for purposes of clustering

Move these into an ‘ST-MAX’ table

As part of this table build, create an additional ‘ST-Max Count’ column, to record instances of multiple ST-Max clusters for same Client at same LA within the period of interest. ST-MAX ‘1’ should be first chronologically with sequence following from there

Now clustered chains of ST-Max have been fully formed, the Start and End date of each ST- Max cluster can be added

Filter for Event End Date between 01/04/2023 and 31/03/2024 (any clusters that run beyond this date are out of scope)

Take earliest Start Date associated with each ST-Max cluster as the ST-Max Start Date

Unique instances of ST-Max from here on in are identified as distinct unique combinations of LA Code, Client ID and ST-Max Count

Begin by joining all ST-Max clusters from ‘ST-Max’ table to all of the Client’s subsequent Event activity from the ‘Subset’ table into a new ‘ST-Max Sequels’ table

This allows us to see what Events were associated with the Client from the day that their period of Reablement support ended, in order to determine the outcome of each ST-Max event

Some ST-Max Clusters in ‘ST-Max Sequels’ will be followed by no further Event activity for that Client. Stage these ST-Max clusters into a ‘No Chronology’ table, using the Event Outcome associated with the ST-Max Cluster End Date as the Final Outcome – no further information can be inferred

Now delete these Clusters from the ‘ST-Max’ table

Create an ‘STS002a’ Table with columns LA Code, Client ID, Final Outcome and Final Outcome Mapped

Insert ST-Max from ‘No Chronology’ into this new STS002a table

‘Final Outcome’ column used to hold the raw Event Outcome from CLD. ‘Final Outcome Mapped column’ is the Event Outcome mapped to SALT wherever possible

Any activity within 3 days of the ST-Max Event ending in the ‘ST-Max Sequels’ table is considered ‘in scope’ and related to the ST-Max period

First Event encountered within this timeframe creates the start of a Sequel cluster/chain. Any subsequent Events after this are added to the chain until chain of continuous sequel Events ends or is broken

An Event beginning more than 3 days after the Event End Date of previous Event is considered too far apart and constitutes a break in sequel chain at this point

If another ST-Max Event is encountered in the chain of onward Events, the chain ends here. These ST-Max clusters should be identified with a Flag and dealt with at end of process

For those sequel chains/clusters where a Service Event was encountered in the Clients onward activity following the ST-Max period ending, these ST-Max clusters along with their sequel outcome can be inserted into the previously created ‘STS002a’ table

‘Final Outcome’ column should be Service Type of the sequel Service. ‘Final Outcome Mapped’ column should be Service Type of the sequel Service, mapped to the SALT STS002a table categories

In cases where more than one Service Event is returned, the SALT hierarchy as per LTS001 should be applied to select the correct Service type

In those cases where onward activity was found for the Client following the ST-Max period ending, but this activity only included Assessments, Reviews or Requests, search the Event Outcomes of these non-Service rows for any useable sequel information

Step 1:

If the Event Outcome on one or more of these non-Service Events in client chronology contains any ‘NFA’ outcome then these can be inserted into ‘STS002a’ table. ‘Final Outcome’ column should be the NFA Event Outcome as per CLD spec, ‘Final Outcome Mapped’ should be NFA Event Outcome mapped to SALT categories

In cases where multiple conflicting NFA outcomes are encountered in sequel chronology, over- write to ‘NFA – Other’ as insert into ‘STS002a’ table

Step 2:

Process those ST-Max clusters where none of the Event Outcomes in the non-Service chronology contains any useable NFA information

Insert these remaining ST-Max clusters into ‘STS002a’ table where ‘Final Outcome’ is Event Outcome associated with original ST-Max cluster End Date and ‘Final Outcome Mapped’ is Event Outcome associated with original ST-Max cluster End Date mapped to SALT categories wherever possible

An ST-Max cluster containing only onward Event activity considered to be ‘out of scope’ for determining a sequel can arise via 2 scenarios:

1. The first sequel event occurred too long after the ST-Max period ended (> 3 days) to be considered as definitely related to the Reablement period
2. Another ST-Max Event was encountered in the sequel activity, which supplants and supersedes the original ST-Max period (pin-pointed with the Flag created earlier in process)

Insert these ST-Max clusters into ‘STS002a’ table where ‘Final Outcome’ is Event Outcome associated with original ST-Max Cluster End Date and ‘Final Outcome Mapped’ is Event Outcome associated with original ST-Max Cluster End Date mapped to SALT categories wherever possible

Every ST-Max/Reablement period present in the initial clustered ST-Max table should now be accounted for, along with a sequel, in table ‘STS002a’.

Using this Final table, count number of ST-Max, broken down by sequel type (Final Outcome Mapped)