Lesson 16: How to Query the CQC Tables

factsanddimens...
Facts and Dimensions provide 1000s of tables of publicly available stats and reference data, ready to query immediately.
See: anypoint.mules...
See how to query the schemas:
CQC (eg api.cqc.org.uk...)
CQC_Providers (eg: api.cqc.org.uk...)
In this video I show you how to query the CQC tables which contain data from the CQC API.
Some of the code from the video:
--Provider Historic service ratings - key question ratings
select
c.*
from
CQC_Providers.Providers_SCD a left join
CQC_Providers.Providers_historicserviceRatings b on
a.Providers_Register_ID = b.Providers_Register_ID left join
CQC_Providers.Providers_historicserviceRatings_keyQuestionRatings c on
a.Providers_Register_ID = c.Providers_Register_ID and
b.ID = c.Providers_historicserviceRatings_ID
where
a.providerId = 'RW6' and
a.Is_Latest = 1
order by
a.Effective_From
--Get a list of locations with Maternity and midwifery services
select
a.locationId
,a.providerId
,c.name
from
cqc.Locations_SCD a inner join
cqc.Locations_LinkTable_Regulated_Activity b on
a.locationId = b.locationID and a.API_Call_Date = b.API_Call_Date inner join
CQC_Providers.Providers_SCD c on
a.providerId collate Latin1_General_CI_AS = c.providerId
where
b.name collate Latin1_General_CI_AS = 'Maternity and midwifery services' and
a.Is_Latest = 1 and
c.Is_Latest = 1
--Service ratings for a location
select
b.*
from
cqc.Locations_SCD a left join
cqc.Locations_LinkTable_Service_Ratings b on
a.locationId = b.locationID and a.API_Call_Date = b.API_Call_Date
where
a.locationId = 'RW603' and
a.Is_Latest = 1