This repository contains the code used to produce the data and metrics underpinning the DHSC Client Level Data Dashboard which is available for Local Authorities.
Local Authorities submit Client Level Data to NHS England each quarter. NHS England pseudonymise the data to remove identifying personal information, and provide DHSC access to the pseudonymised national dataset, including submission meta data and a number of additional, derived fields, via a secure database. The scripts in this repository analyse this national dataset and therefore they are not directly transferable to local CLD.
Note, this git repository does not allow contributors and is solely for the purpose of sharing the code used to produce the CLD dashboard with Local Authority analysts.
This directory contains code to create deduplicated main tables that serve as the starting point for producing the dashboard tables and ASCOF measures. There are two versions, using:
- single submissions (create_main_table_for_12mo_period.sql), and
- joined submissions, to enable analysis of data covering more than 12 month periods (create_main_table_for_period.sql)
The code is designed to create new main tables on a quarterly basis and name these according to the start and end date of the reporting period (which for joined submissions may increase in length by 3 months each quarter as more data is received). Processing steps are contained within dedicated stored procedures, also available in this directory. The main processing steps are:
- selecting submissions covering the period,
- filtering the data to events in the period,
- creating cleaned and derived fields, and
- deduplicating records.
Release 2 specification
These scripts process the data as per the Release 2 CLD specification. For some priority fields, and fields impacted by the change from the release 1 to release 2 CLD specifications, data cleaning and/or mapping is carried out. For data submitted from October 2025 onwards, where local authorities have continued to use the previous Release 1 specification, the data is mapped to Release 2 where possible. This approach is necessary as all methods and scripts have been updated to support the Release 2 specification. No additional data rows are created, instead individual fields are amended as needed. Reference tables contain release 1 to release 2 mapping and are manually updated to map invalid entries to valid entries from the release 2 defined lists, where it is apparent what the entry should be.
All pages on the dashboard except for ASCOF use the single submissions table covering the latest 12 month reporting period. The codes used to produce the data underpinning the main dashboard pages are numbered and must be ran sequentially. More information on the main table methodology and processing of the data for the dashboard is available in the dashboard methodology document alongside the dashboard on Athena and on Agem’s website.
ASCOF measures tend to require more than 12 months of data (e.g. for identifying new/existing clients and determining sequels) and therefore they all use the joined submisisons table.
The codes for creating the ASCOF measures are set up as stored procedures. This allows figures to be generated for multiple statistical reporting years by altering the reporting period start and end dates and the starting main table. The Latest_Person_Details.sql script creates a table of latest demographic information such as date of birth, gender and accommodation status for all clients. This script is used by all the ASCOF procedures and therefore must be ran in advance. The main script (ASCOF_main_script.sql) calls each of ASCOF stored procedures for multiple reporting periods and joins them together for the purpose of the dashboard.
The full ASCOF methodolody document is available alongside the dashboard on Athena and on Agem’s website.
Unless stated otherwise, the codebase is released under the MIT License. This covers both the codebase and any sample code in the documentation. The documentation is © Crown copyright and available under the terms of the Open Government 3.0 licence.