STS001

All 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’ field made from a concatenation of these fields to help identify distinct Request and Service events for each client

Requests – concatenation of LA Code, Client ID, Event Start Date and Route Of Access

Services – 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, the last submission within the LA submission window) to ensure process uses most up-to-date version of each event

Select LA Code, Client ID, Age, Event Start Date, Event End Date, Route Of Access and Event Outcome from the historical ‘All Events’ dataset into ‘Request Build’ table where Event Type is ‘Request’

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 steps to work correctly

Cluster together separate Request Events that occur for the same Client, within a short timespan, into one combined Request Event to avoid over-counting Request activity

A new Request Event starting within 4 days of a previous Request Event ending is considered to be the same Request for purposes of clustering

Move these into a ‘Requests’ table

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

Now clustered chains of Request Events have been fully formed, the Start and End date of each Request cluster can be added.

Filter for Request 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 Request cluster as the Request Start Date

Unique Requests from here on in are identified as distinct unique combinations of LA Code, Client ID and Request Count

Select all Service Events from ‘All Events’ table where Service Type is Long Term Nursing, Residential, Community or Prison into a ‘Services’ table

Disregard any Request clusters where Client has an open/ongoing Service event in ‘Services’ table

Request Cluster starts between a Service Start and End date for the same Client

or 

Request Cluster starts on/after Service Start Date for same Client and Service event has no End Date

Begin by joining all Request clusters from ‘Requests’ table to all of the Client’s subsequent Event activity from the ‘All Events’ table into a new ‘Request Sequels’ table

This allows us to see what Events were associated with the Client from the day that their Request for support ended, in order to determine the sequel to each event

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

Now delete these Request Clusters from the ‘Requests’ table

Create an ‘STS001’ table with columns LA Code, ID, Age, Route Of Access, Final Outcome and Final Outcome Mapped

Insert Requests from ‘No Chronology’ into this new STS001 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 28 days of the Request Event ending in the ‘Request Sequels’ table is considered ‘in scope’ and related to the Request

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 Request Event is encountered in the chain of onward Events, the chain ends here. These Requests should be identified with a Flag and dealt with in Group 4 at end of process

For those sequel chains/clusters where a Service Event was encountered in the Clients onward activity following the Request, these Requests along with their sequel outcome can be inserted into the previously created ‘STS001’ 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 STS001 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

Filter the ‘Service found in chronology’ Requests above for those where the Service found was ‘Short Term Support: St-Max’

Write these into a ‘STS002a Cohort’ table. The STS002a script will require this as a starting point for the ST-Max SALT table replication

In those cases where onward activity was found for the Client following the Request, but this activity only included Assessments or Reviews, 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 or ‘Progress to End of Life Care’ outcome then these can be inserted into ‘STS001’ table

‘Final Outcome’ column should be the NFA/End of Life Event Outcome, ‘Final Outcome Mapped’ should be NFA/End of Life Outcome mapped to SALT categories

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

Step 2:

Requests where none of the Event Outcomes in the non-Service chronology following a Request contains any useable NFA/EOL information

Insert these remaining Part 3 Requests into ‘STS001’ table where ‘Final Outcome’ is Event Outcome associated with original Request Event and ‘Final Outcome Mapped’ is Event Outcome associated with original Request Event mapped to SALT categories wherever possible

A Request containing 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 Request ended (> 28 days) to be considered as related to the Request
2. Another Request Event was encountered in the sequel activity, which supplants and supersedes the original Request (pin-pointed with the Flag created earlier in process)

Insert these Requests into ‘STS001’ table where ‘Final Outcome’ is Event Outcome associated with original Request Event and ‘Final Outcome Mapped’ is Event Outcome associated with original Request Event mapped to SALT categories wherever possible

Every Request present in the initial clustered Requests table should now be accounted along with a sequel, in table ‘STS001’

Using this Final table, count number of requests, broken down by sequel type (Final Outcome Mapped) and Route of Access as per SALT STS001