give me who closed the SR

 avatar
user_0223141589
mysql
2 years ago
800 B
0
Indexable
SELECT
  vs.NAME sr_ref,
  qc.WRITE_DATE,
  rp.NAME
FROM
  BIZ.DBT_STG.STG_ODOO_PROD__QUALITY_CHECK qc
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__FLEET_VEHICLE_COST fvc on fvc.id = qc.VEHICLE_COST_ID
  JOIN (
    SELECT
      qc.REPAIR_ID,
      MAX(qc.WRITE_DATE) as max_date
    FROM
      BIZ.DBT_STG.STG_ODOO_PROD__QUALITY_CHECK qc
    WHERE
      qc.QUALITY_STATE != 'none'
    GROUP BY
      1
  ) max_date_task ON max_date_task.repair_id = qc.REPAIR_ID and max_date_task.max_date = qc.WRITE_DATE
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_USERS ru ON ru.id = qc.USER_ID
  JOIN BIZ.DBT_STG.STG_ODOO_PROD__RES_PARTNER rp ON rp.id = ru.PARTNER_ID
  join BIZ.DBT_STG.STG_ODOO_PROD__VEHICLE_SERVICES vs on vs.id = fvc.VEHICLE_SERVICES_ID
WHERE
  qc.QUALITY_STATE != 'none'
  and vs.STAGE_ID = 4
ORDER BY
  1 DESC