Behind the scenes - Show SQL queries

library(nottshcOpenData)
#> 
#> ── This is nottshcOpenData 0.2.1 ───────────────────────────────────────────────
#> ℹ nottshcOpenData is currently in development, please report any bugs or ideas at:
#> ℹ https://github.com/CDU-data-science-team/nottshcOpenData/issues
#> 
#> ── Connecting to NOTTSHC SUCE Server: ──────────────────────────────────────────
#> conn_mysql_suce <- connect_mysql(database = "SUCE")

# Create MySQL connection
# Note, the driver name, here "MySQL ODBC 8.0 ANSI Driver", may differ on your computer
conn_mysql_suce <- connect_mysql(database = "SUCE",
                                 driver = "MySQL ODBC 8.0 ANSI Driver")
#> ℹ No host name set, using default server: 109.74.194.173
#> ℹ No UID set, using default UID: opendata
#> ℹ No PWD set, using default PWD: letmein
#> 
#> ── Connecting to MySQL ODBC 8.0 ANSI Driver ────────────────────────────────────
#> ✓ Connecting to server: 109.74.194.173
#> ✓ Connecting to database: SUCE

1. SQL code used to query data

# Get database (db_) connection for specified date range 
get_px_exp(from = "2020-01-01",
           to = "2020-12-31") %>% 
  dplyr::show_query()
#> <SQL>
#> SELECT *
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')

2. SQL code used to tidy data

get_px_exp(from = "2020-01-01",
           to = "2020-12-31") %>% 
  tidy_px_exp() %>% 
  dplyr::show_query()
#> <SQL>
#> SELECT `key`, CONCAT_WS('', `key`, '_', `type_category`) AS `comment_key`, `date`, `location`, `team_c`, `team_n`, `directorate`, `division`, `su`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `category`, `subcategory`, `comment_type`, `comment_txt`, `type_category`, `type_num`, `code`, `crit`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, `type_category`, `type_num`, `LHS`.`code` AS `code`, `crit`, `category`, `subcategory`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, `type_category`, `type_num`, `code`, CASE
#> WHEN (`crit` IN (-5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5)) THEN (`crit`)
#> ELSE (NULL)
#> END AS `crit`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, `type_category`, `type_num`, `code`, CASE
#> WHEN (`comment_type` = 'imp') THEN (`imp_crit` * -1.0)
#> WHEN (`comment_type` = 'best') THEN (`best_crit`)
#> END AS `crit`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `best_crit`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, `type_category`, `type_num`, NULLIF(`code`, 'XX') AS `code`
#> FROM ((((SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `best_crit`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, 'imp' AS `type_category`, 'n1' AS `type_num`, `imp_n1` AS `code`
#> FROM ((SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'imp' AS `comment_type`, `imp` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'best' AS `comment_type`, `best` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)) `q06`)
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `best_crit`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, 'imp' AS `type_category`, 'n2' AS `type_num`, `imp_n2` AS `code`
#> FROM ((SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'imp' AS `comment_type`, `imp` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'best' AS `comment_type`, `best` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)) `q06`))
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `best_crit`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, 'best' AS `type_category`, 'n1' AS `type_num`, `best_n1` AS `code`
#> FROM ((SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'imp' AS `comment_type`, `imp` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'best' AS `comment_type`, `best` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)) `q06`))
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `best_crit`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, `comment_type`, `comment_txt`, 'best' AS `type_category`, 'n2' AS `type_num`, `best_n2` AS `code`
#> FROM ((SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'imp' AS `comment_type`, `imp` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)
#> UNION ALL
#> (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `imp_crit`, `imp_n1`, `imp_n2`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`, 'best' AS `comment_type`, `best` AS `comment_txt`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, CASE
#> WHEN (`service` IN (0, 1, 2, 3, 4, 5)) THEN (`service`)
#> ELSE (NULL)
#> END AS `service`, CASE
#> WHEN (`listening` IN (0, 1, 2, 3, 4, 5)) THEN (`listening`)
#> ELSE (NULL)
#> END AS `listening`, CASE
#> WHEN (`communication` IN (0, 1, 2, 3, 4, 5)) THEN (`communication`)
#> ELSE (NULL)
#> END AS `communication`, CASE
#> WHEN (`respect` IN (0, 1, 2, 3, 4, 5)) THEN (`respect`)
#> ELSE (NULL)
#> END AS `respect`, CASE
#> WHEN (`inv_care` IN (0, 1, 2, 3, 4, 5)) THEN (`inv_care`)
#> ELSE (NULL)
#> END AS `inv_care`, CASE
#> WHEN (`positive` IN (0, 1, 2, 3, 4, 5)) THEN (`positive`)
#> ELSE (NULL)
#> END AS `positive`, `imp`, `imp_crit`, `imp_n1`, `imp_n2`, `best`, `best_crit`, `best_n1`, `best_n2`, `inpatient`, `fft_category`, `team_n`, `directorate`, `division`
#> FROM (SELECT `key`, `team_c`, `date`, `location`, `time`, `formtype`, `su`, `carertype`, `promoter`, `service`, `listening`, `communication`, `respect`, `inv_care`, `positive`, `improve` AS `imp`, `imp_crit`, UPPER(`imp_n1`) AS `imp_n1`, UPPER(`imp_n2`) AS `imp_n2`, `best`, `best_crit`, UPPER(`best_n1`) AS `best_n1`, UPPER(`best_n2`) AS `best_n2`, `inpatient`, `fft_category`, `team_n`, `dir_t` AS `directorate`, `division2` AS `division`
#> FROM (SELECT `key`, `TeamC` AS `team_c`, `Date` AS `date`, `Location` AS `location`, `Time` AS `time`, `formtype`, `SU` AS `su`, `carertype`, `Promoter` AS `promoter`, `Service` AS `service`, `Listening` AS `listening`, `Communication` AS `communication`, `Respect` AS `respect`, `InvCare` AS `inv_care`, `Positive` AS `positive`, `Improve` AS `improve`, `ImpCrit` AS `imp_crit`, `Imp_N1` AS `imp_n1`, `Imp_N2` AS `imp_n2`, `Best` AS `best`, `BestCrit` AS `best_crit`, `Best_N1` AS `best_n1`, `Best_N2` AS `best_n2`, `Inpatient` AS `inpatient`, `fftCategory` AS `fft_category`, `TeamN` AS `team_n`, `DirT` AS `dir_t`, `Division2` AS `division2`
#> FROM (SELECT *
#> FROM (SELECT OpenLocal.*, Teams.Inpatient, Teams.fftCategory, Teams.TeamN,
#>       Directorates.DirT, Directorates.Division2
#>       FROM OpenLocal INNER JOIN Teams
#>       INNER JOIN Directorates ON Directorates.DirC = Teams.Directorate
#>       WHERE OpenLocal.TeamC = Teams.TeamC
#>       AND OpenLocal.Date >= Teams.date_from
#>       AND OpenLocal.Date <= Teams.date_to
#>       AND OpenLocal.Date >= Directorates.date_from
#>       AND OpenLocal.Date <= Directorates.date_to) `q01`
#> WHERE (`Date` >= '2020-01-01')) `q02`
#> WHERE (`Date` <= '2020-12-31')) `q03`) `q04`) `q05`)) `q06`)) `q07`) `q08`
#> WHERE ((`comment_type` = 'imp' AND `type_category` = 'imp') OR (`comment_type` = 'best' AND `type_category` = 'best'))) `q09`) `LHS`
#> LEFT JOIN (SELECT `Category` AS `category`, `Code` AS `code`, `Subcategory` AS `subcategory`
#> FROM `SUCE`.`NewCodes`) `RHS`
#> ON (`LHS`.`code` = `RHS`.`code`)
#> ) `q01`