CSDS_UDAL_QuartoDoc

Author

Ashley Dickson

This notebook displays some of the data that are available on UDAL regarding CSDS. It is not a complete description, for there are many tables and millions of records. Hopefully, however, it will help point us in the right direction for further investigation.

# packages needed
library(odbc)
library(DBI)
library(rstudioapi)
library(rmarkdown)
library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.5.1     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(scales)


Attaching package: 'scales'

The following object is masked from 'package:purrr':

    discard

The following object is masked from 'package:readr':

    col_factor

# open connection
con <- dbConnect(odbc::odbc(), dsn="udal_prod", timeout = 10)

Let’s start by viewing the CSDS tables available

SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'CYP[0-9][0-9][0-9]%'
    AND TABLE_NAME NOT LIKE '%Publish%'
    AND TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME ASC

Displaying records 1 - 10
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE
UDAL_Warehouse	MESH_CSDS	CYP000Header	VIEW
UDAL_Warehouse	MESH_CSDS	CYP001MPI	VIEW
UDAL_Warehouse	MESH_CSDS	CYP002GP	VIEW
UDAL_Warehouse	MESH_CSDS	CYP003AccommType	VIEW
UDAL_Warehouse	MESH_CSDS	CYP004CarePlanType	VIEW
UDAL_Warehouse	MESH_CSDS	CYP005CarePlanAgreement	VIEW
UDAL_Warehouse	MESH_CSDS	CYP006SocPerCircumstances	VIEW
UDAL_Warehouse	MESH_CSDS	CYP007EmpStatus	VIEW
UDAL_Warehouse	MESH_CSDS	CYP008OverseasVisitorChargCat	VIEW
UDAL_Warehouse	MESH_CSDS	CYP101Referral	VIEW

View the fields available in a particular table: CSDS201, Care Contacts

-- view column names in Care Contacts tables
SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CYP201CareContact'

Displaying records 1 - 10
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	AuditId
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	CareContactID
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	ServiceRequestID
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	TeamID_Local
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	Contact_Date
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	Contact_Time
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	OrgID_Commissioner
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	AdminCategory
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	CareContact_Duration
UDAL_Warehouse	MESH_CSDS	CYP201CareContact	Consultation_Type

Count of all records in table:

SELECT COUNT(*) as 'Count of Records'
FROM [Reporting_MESH_CSDS].[CYP201CareContact]

1 records
Count of Records
708196276

Count records into single-year age bins to visualise distribution.

SELECT AgeYr_Contact_Date, COUNT(*) as 'Records'
FROM [Reporting_MESH_CSDS].[CYP201CareContact]
GROUP BY AgeYr_Contact_Date

a <- ggplot(df_age, aes(x = AgeYr_Contact_Date, y = Records))
a + 
  geom_bar(stat = "identity") +
  ggtitle("Distribution of Care Activity Records") +
  xlab("Patient Age") +
  ylab("Count of Records") +
  scale_y_continuous(labels = comma)

Warning: Removed 1 row containing missing values or values outside the scale range
(`geom_bar()`).

SELECT Der_Financial_Year, Der_Financial_Month, COUNT(*) as 'Records'
FROM [Reporting_MESH_CSDS].[CYP201CareContact]
GROUP BY Der_Financial_Year, Der_Financial_Month

b <- ggplot(df_month, aes(x = Der_Financial_Year, y = Records, fill = Der_Financial_Month))
b + 
  geom_bar(stat = "identity") +
  ggtitle("Time Coverage of Care Contact Table") +
  xlab("Financial Year") +
  ylab("Count of Records") +
  scale_y_continuous(labels = comma)

Attendance Status information is poorly completed, but since the number of records is so large there may be a decent sample for analytical purposes.

SELECT AttendanceStatus, COUNT(*) as 'Records'
FROM [Reporting_MESH_CSDS].[CYP201CareContact]
GROUP BY AttendanceStatus

c <- ggplot(df_att, aes(x = "", y = Records, fill = AttendanceStatus))
c + 
  geom_col() + coord_polar(theta = "y") +
  ggtitle("Pie Chart of Attendance Status") +
  scale_y_continuous(labels = comma)

View Provider organisations sorted biggest to smallest.

SELECT a.OrgID_Provider, b.Org_Name, COUNT(*) AS 'Records'
FROM [Reporting_MESH_CSDS].[CYP201CareContact] a
LEFT OUTER JOIN Internal_ESRReference.REF_ORGANISATION b
  ON a.OrgID_Provider = b.Org_Code_For_Join
GROUP BY a.OrgID_Provider, b.Org_Name
ORDER BY 'Records' DESC

Displaying records 1 - 10
OrgID_Provider	Org_Name	Records
RYX	CENTRAL LONDON COMMUNITY HEALTHCARE NHS TRUST	25729078
RDR	SUSSEX COMMUNITY NHS FOUNDATION TRUST	21820099
RW4	MERSEY CARE NHS FOUNDATION TRUST	17767734
NNF	CITY HEALTH CARE PARTNERSHIP CIC	15603963
RV3	CENTRAL AND NORTH WEST LONDON NHS FOUNDATION TRUST	15577458
RXP	COUNTY DURHAM AND DARLINGTON NHS FOUNDATION TRUST	15523151
RY4	HERTFORDSHIRE COMMUNITY NHS TRUST	14101490
RAT	NORTH EAST LONDON NHS FOUNDATION TRUST	13759572
RRE	MIDLANDS PARTNERSHIP NHS FOUNDATION TRUST	13175395
RJC	SOUTH WARWICKSHIRE UNIVERSITY NHS FOUNDATION TRUST	12864493