SELECT t.ВЛАДЕЛЕЦ_ВАГОНА, t.ВСЕГО, t.НА_ПРОСТОЕ, t.БЕЗ_ПРОСТОЯ, t.ПВ, t.СПЦ, t.ПЛ, t.ЦС,t.КР, t.ПРОЧИЕ FROM
(
SELECT
(
SELECT ro.ORGSHORTNAME FROM DK.RM_ORG ro WHERE ro.ID = rsd.CAROWNER
)
AS ВЛАДЕЛЕЦ_ВАГОНА,
(
SELECT count(*) FROM SALES.RM_SHEET_DETAIL rsd1 WHERE rsd1.CAROWNER = rsd.CAROWNER
)
AS ВСЕГО,
(
SELECT count(*) FROM SALES.RM_SHEET_DETAIL rsd1 WHERE rsd1.CAROWNER = rsd.CAROWNER AND rsd1.CARACCEPTED = 1
)
AS НА_ПРОСТОЕ,
(
SELECT count(*) FROM SALES.RM_SHEET_DETAIL rsd1 WHERE rsd1.CAROWNER = rsd.CAROWNER AND rsd1.CARACCEPTED = 0
)
AS БЕЗ_ПРОСТОЯ,
CASE rsd.CARTYPE
WHEN 263 THEN '1'
WHEN 60 THEN '1'
WHEN 265 THEN '1'
WHEN 206 THEN '2'
WHEN 205 THEN '2'
WHEN 50 THEN '50'
WHEN 70 THEN '70'
WHEN 40 THEN '40'
ELSE 'OTHERS'
END AS CARTYPE
FROM
SALES.RM_SHEET_DETAIL rsd
LEFT JOIN
SALES.E_NSI_WAG_TYPE e_nwt ON (e_nwt.WAG_TYPE_ID = rsd.CARTYPE)
)
PIVOT
(
count(CARTYPE) FOR CARTYPE IN( 1 AS ПВ, 2 AS СПЦ, 50 AS ПЛ, 70 AS ЦС, 40 AS КР, 'OTHERS' AS ПРОЧИЕ)
) t
СУБД Oracle 11g
Средний
