Skip to content

Instantly share code, notes, and snippets.

@NilovAlexander

NilovAlexander/query.sql

Last active May 19, 2016 14:18
Show Gist options
  • Save NilovAlexander/b606dcdc7d52151623891a8379fd825b to your computer and use it in GitHub Desktop.
Save NilovAlexander/b606dcdc7d52151623891a8379fd825b to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query.sql
SELECT
company_name,
(last_name || ' ' || SUBSTRING(first_name, 1, 1) || '.' || SUBSTRING(middle_name, 1, 1) || '.') employee_name,
to_char(inner_date_create, 'YYYY-DD-MM HH24:MI') date_create,
sum(inner_assigned) assigned,
sum(inner_complete) complete,
sum(inner_fail) fail
FROM (
SELECT
company."NAME" company_name,
employee."LAST_NAME" last_name,
employee."FIRST_NAME" first_name,
employee."MIDDLE_NAME" middle_name,
mission."DATE_CREATE" inner_date_create,
(CASE WHEN mission."STATUS" = 'assigned'
THEN count(mission."ID")
ELSE 0 END) inner_assigned,
(CASE WHEN mission."STATUS" = 'complete'
THEN count(mission."ID")
ELSE 0 END) inner_complete,
(CASE WHEN mission."STATUS" = 'fail'
THEN count(mission."ID")
ELSE 0 END) inner_fail
FROM
ets."MISSION" mission
INNER JOIN vts."FDC_LEGAL_PERSON" company ON company."ASUODS_ID" = mission."COMPANY_ID"
INNER JOIN ets."WAYBILL" waybill ON waybill."ID" = mission."WAYBILL_ID"
INNER JOIN ets."EMPLOYEE" employee ON waybill."RESPONSIBLE_PERSON_ID" = employee."ID"
GROUP BY company_name, last_name, first_name, middle_name, inner_date_create, mission."STATUS") AS inner_table
GROUP BY company_name, last_name, first_name, middle_name, inner_date_create
ORDER BY company_name, inner_date_create, employee_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment