雪花SQL语句透视将无法使用我正在使用的查询。
雪花SQL语句透视将无法使用我正在使用的查询。
提问于 2022-08-04 18:42:30
下面是雪花查询。我知道这很难看，但这正是我必须在这里工作的地方。问题是我的支点不起作用。这一点之前的一切都可以工作，但是当我尝试数据透视时，我会得到一个错误:无效标识符'DRIVERTYPE‘。我需要将我的结果转换成基于DriverType的列。
SELECT CSC, DRIVERTYPE, TEN_MONTHS_TURNOVER FROM
(
-- Beginning of Year (Driver Count):
SELECT
CASE
WHEN A.CSC = 'ABI' THEN 'Abilene'
WHEN A.CSC = 'ALB' THEN 'Albuquerque'
WHEN A.CSC = 'AMA' THEN 'Amarillo'
WHEN A.CSC = 'BOV' THEN 'Bovina'
WHEN A.CSC = 'DFW' THEN 'Dallas / Fort Worth'
WHEN A.CSC = 'ELP' THEN 'El Paso'
WHEN A.CSC = 'HOU' THEN 'Houston'
WHEN A.CSC = 'ODE' THEN 'Odessa'
WHEN A.CSC = 'OKC' THEN 'Oklahoma City'
WHEN A.CSC = 'PHO' THEN 'Phoenix'
WHEN A.CSC = 'SAN' THEN 'San Antonio'
WHEN A.CSC = 'TOP' THEN 'Topeka'
WHEN A.CSC = 'TUL' THEN 'Tulsa'
WHEN A.CSC = 'WIC' THEN 'Wichita'
END AS CSC,
CASE
WHEN A.DRIVERTYPE = 'CD' THEN 'Company Driver'
WHEN A.DRIVERTYPE = 'LO' THEN 'Independent Contractor'
END AS DRIVERTYPE,
A.DRIVER_COUNT_BEG_YR, B.YTD_HIRED_DRIVERS, C.YTD_TERMED_DRIVERS, D.DRIVER_COUNT_END_MO,
ROUND((A.DRIVER_COUNT_BEG_YR + D.DRIVER_COUNT_END_MO) / 2) AS AVG_DRIVER_COUNT, ROUND(C.YTD_TERMED_DRIVERS / AVG_DRIVER_COUNT, 4) AS TEN_MONTHS_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER / 10, 4) AS MONTHLY_TURNOVER, ROUND(TEN_MONTHS_TURNOVER * 12 / 10, 4) AS ANNUALIZED_TURNOVER,
ROUND(C.YTD_TERMED_DRIVERS / (A.DRIVER_COUNT_BEG_YR + B.YTD_HIRED_DRIVERS) * 1, 4) AS TURNOVER,
G.COUNTS AS EOY_FORECAST_COUNTS, +EOY_FORECAST_COUNTS - D.DRIVER_COUNT_END_MO AS NEED_TO_HIRE, E.RANGE_HIRED_DRIVERS, F.RANGE_TERMED_DRIVERS
FROM
(
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS DRIVER_COUNT_BEG_YR
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) < SUBSTRING(CURRENT_DATE(),1,4)
AND SUBSTRING(MPP_TERMINATIONDT,1,4) >= SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) A
LEFT JOIN
(
-- Year to Date (Hired Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS YTD_HIRED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) = SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) B ON A.CSC = B.CSC AND A.DRIVERTYPE = B.DRIVERTYPE
LEFT JOIN
(
-- Year to Date (Termed Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS YTD_TERMED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_TERMINATIONDT,1,4) = SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) C ON A.CSC = C.CSC AND A.DRIVERTYPE = C.DRIVERTYPE
LEFT JOIN
(
-- End of Month (Driver Count):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS DRIVER_COUNT_END_MO--, Forecast_Counts
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) <= SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MPP_HIREDATE,6,2) <= SUBSTRING(CURRENT_DATE(),6,2)
AND SUBSTRING(MPP_TERMINATIONDT,1,4) >= SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MPP_TERMINATIONDT,6,2) > SUBSTRING(CURRENT_DATE(),6,2)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) D ON A.CSC = D.CSC AND A.DRIVERTYPE = D.DRIVERTYPE
LEFT JOIN
(
-- Montly/Date Range (Hired Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS RANGE_HIRED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE MPP_HIREDATE BETWEEN '2022-07-01' AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) E ON A.CSC = E.CSC AND A.DRIVERTYPE = E.DRIVERTYPE
LEFT JOIN
(
-- Montly/Date Range (Termed Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS RANGE_TERMED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE MPP_TERMINATIONDT BETWEEN '2022-07-01' AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) F ON A.CSC = F.CSC AND A.DRIVERTYPE = F.DRIVERTYPE
INNER JOIN "DEV"."PUBLIC"."TMW_DRIVERFORECAST" G ON A.CSC = G.CSC -- AND A.DRIVERTYPE = E.DRIVERTYPE
WHERE SUBSTRING(MONTH,1,4) = SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MONTH,6,2) = 12 /* End of Current Year Forecast */
----------------------------------------------------------------------------------------------------------------
-- (Works 3rd Graph) Sub-Total Line Grouping and % Formulas (to get correct Percentages):
----------------------------------------------------------------------------------------------------------------
UNION
-- Beginning of Year (Driver Count):
SELECT
CASE
WHEN A.CSC = 'ABI' THEN 'Abilene'
WHEN A.CSC = 'ALB' THEN 'Albuquerque'
WHEN A.CSC = 'AMA' THEN 'Amarillo'
WHEN A.CSC = 'BOV' THEN 'Bovina'
WHEN A.CSC = 'DFW' THEN 'Dallas / Fort Worth'
WHEN A.CSC = 'ELP' THEN 'El Paso'
WHEN A.CSC = 'HOU' THEN 'Houston'
WHEN A.CSC = 'ODE' THEN 'Odessa'
WHEN A.CSC = 'OKC' THEN 'Oklahoma City'
WHEN A.CSC = 'PHO' THEN 'Phoenix'
WHEN A.CSC = 'SAN' THEN 'San Antonio'
WHEN A.CSC = 'TOP' THEN 'Topeka'
WHEN A.CSC = 'TUL' THEN 'Tulsa'
WHEN A.CSC = 'WIC' THEN 'Wichita'
END AS CSC,
'Total' AS DRIVERTYPE, --A.DRIVERTYPE,
SUM(A.DRIVER_COUNT_BEG_YR) AS DRIVER_COUNT_BEG_YR, SUM(B.YTD_HIRED_DRIVERS) AS YTD_HIRED_DRIVERS,
SUM(C.YTD_TERMED_DRIVERS) AS YTD_TERMED_DRIVERS, SUM(D.DRIVER_COUNT_END_MO) AS DRIVER_COUNT_END_MO,
SUM(ROUND((A.DRIVER_COUNT_BEG_YR + D.DRIVER_COUNT_END_MO) / 2)) AS AVG_DRIVER_COUNT,
ROUND(SUM(C.YTD_TERMED_DRIVERS) / AVG_DRIVER_COUNT, 4) AS TEN_MONTHS_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER / 10, 4) AS MONTHLY_TURNOVER, ROUND(TEN_MONTHS_TURNOVER * 12 / 10, 4) AS ANNUALIZED_TURNOVER,
ROUND(SUM(C.YTD_TERMED_DRIVERS) / (SUM(A.DRIVER_COUNT_BEG_YR) + SUM(B.YTD_HIRED_DRIVERS)) * 1, 4) AS TURNOVER,
SUM(G.COUNTS) AS EOY_FORECAST_COUNTS, +EOY_FORECAST_COUNTS - SUM(D.DRIVER_COUNT_END_MO) AS NEED_TO_HIRE,
SUM(E.RANGE_HIRED_DRIVERS) AS RANGE_HIRED_DRIVERS, SUM(F.RANGE_TERMED_DRIVERS) AS RANGE_TERMED_DRIVERS
FROM
(
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS DRIVER_COUNT_BEG_YR
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) < SUBSTRING(CURRENT_DATE(),1,4)
AND SUBSTRING(MPP_TERMINATIONDT,1,4) >= SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) A
LEFT JOIN
(
-- Year to Date (Hired Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS YTD_HIRED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) = SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) B ON A.CSC = B.CSC AND A.DRIVERTYPE = B.DRIVERTYPE
LEFT JOIN
(
-- Year to Date (Termed Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS YTD_TERMED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_TERMINATIONDT,1,4) = SUBSTRING(CURRENT_DATE(),1,4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) C ON A.CSC = C.CSC AND A.DRIVERTYPE = C.DRIVERTYPE
LEFT JOIN
(
-- End of Month (Driver Count):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS DRIVER_COUNT_END_MO--, Forecast_Counts
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE SUBSTRING(MPP_HIREDATE,1,4) <= SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MPP_HIREDATE,6,2) <= SUBSTRING(CURRENT_DATE(),6,2)
AND SUBSTRING(MPP_TERMINATIONDT,1,4) >= SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MPP_TERMINATIONDT,6,2) > SUBSTRING(CURRENT_DATE(),6,2)
AND MPP_TYPE1 IN ('CD', 'LO') /* CD is Company Driver?, LO is Independent Contractor? */
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) D ON A.CSC = D.CSC AND A.DRIVERTYPE = D.DRIVERTYPE
LEFT JOIN
(
-- Monthly/Date Range (Hired Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS RANGE_HIRED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE MPP_HIREDATE BETWEEN '2022-07-01' AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) E ON A.CSC = E.CSC AND A.DRIVERTYPE = E.DRIVERTYPE
LEFT JOIN
(
-- Monthly/Date Range (Termed Drivers):
SELECT MPP_Terminal AS CSC, MPP_Type1 AS DRIVERTYPE, COUNT(MPP_TERMINAL) AS RANGE_TERMED_DRIVERS
FROM (
SELECT MPP_Terminal, /* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM "DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE MPP_TERMINATIONDT BETWEEN '2022-07-01' AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY MPP_TERMINAL, MPP_TYPE1
)
GROUP BY MPP_TERMINAL, MPP_TYPE1
ORDER BY MPP_TERMINAL, MPP_TYPE1
) F ON A.CSC = F.CSC AND A.DRIVERTYPE = F.DRIVERTYPE
INNER JOIN "DEV"."PUBLIC"."TMW_DRIVERFORECAST" G ON A.CSC = G.CSC -- AND A.DRIVERTYPE = G.DRIVERTYPE
WHERE SUBSTRING(MONTH,1,4) = SUBSTRING(CURRENT_DATE(),1,4) AND SUBSTRING(MONTH,6,2) = 12 /* End of Current Year Forecast */
GROUP BY A.CSC --,A.DRIVERTYPE
ORDER BY CSC, DRIVERTYPE
) H
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN ('Company Driver', 'Independent Contractor', 'Total')
) AS PivotTable下面是数据从所有数据到数据透视的方式：
我猜这是一个别名的问题。我试着用别名做顶级的选择，但是没有运气。也尝试过A.VarNames，直到H.VarNames，但是在尝试使用数据透视时仍然会出现错误。我愿意用其他方法来完成这件事。我想看看答案中的一些例子。
回答 2
Stack Overflow用户
回答已采纳
发布于 2022-08-05 03:44:13
由于我没有您的基表，并且您指示了大多数查询工作，所以我编写了一个简单的测试，其中提供了一个旋转结果集。
WITH PT (CSC, DRIVERTYPE, TEN_MONTHS_TURNOVER) AS (
select
$1 as CSC,
$2 as DRIVERTYPE,
$3 as TEN_MONTHS_TURNOVER
from
(
values
('Abilene', 'Company Driver', 0.125),('Abilene', 'Independent Contractor', 0.125),('Abilene', 'Total', 0.125)
)
)
SELECT
*
FROM
PT
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN (
'Company Driver',
'Independent Contractor',
'Total'
)
) as P;因此，将其应用于您的查询-请查看此方法是否适用于您：
WITH PT (CSC, DRIVERTYPE, TEN_MONTHS_TURNOVER) AS (
SELECT
CSC,
DRIVERTYPE,
TEN_MONTHS_TURNOVER
FROM
(
-- Beginning of Year (Driver Count):
SELECT
CASE
WHEN A.CSC = 'ABI' THEN 'Abilene'
WHEN A.CSC = 'ALB' THEN 'Albuquerque'
WHEN A.CSC = 'AMA' THEN 'Amarillo'
WHEN A.CSC = 'BOV' THEN 'Bovina'
WHEN A.CSC = 'DFW' THEN 'Dallas / Fort Worth'
WHEN A.CSC = 'ELP' THEN 'El Paso'
WHEN A.CSC = 'HOU' THEN 'Houston'
WHEN A.CSC = 'ODE' THEN 'Odessa'
WHEN A.CSC = 'OKC' THEN 'Oklahoma City'
WHEN A.CSC = 'PHO' THEN 'Phoenix'
WHEN A.CSC = 'SAN' THEN 'San Antonio'
WHEN A.CSC = 'TOP' THEN 'Topeka'
WHEN A.CSC = 'TUL' THEN 'Tulsa'
WHEN A.CSC = 'WIC' THEN 'Wichita'
END AS CSC,
CASE
WHEN A.DRIVERTYPE = 'CD' THEN 'Company Driver'
WHEN A.DRIVERTYPE = 'LO' THEN 'Independent Contractor'
END AS DRIVERTYPE,
A.DRIVER_COUNT_BEG_YR,
B.YTD_HIRED_DRIVERS,
C.YTD_TERMED_DRIVERS,
D.DRIVER_COUNT_END_MO,
ROUND(
(A.DRIVER_COUNT_BEG_YR + D.DRIVER_COUNT_END_MO) / 2
) AS AVG_DRIVER_COUNT,
ROUND(C.YTD_TERMED_DRIVERS / AVG_DRIVER_COUNT, 4) AS TEN_MONTHS_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER / 10, 4) AS MONTHLY_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER * 12 / 10, 4) AS ANNUALIZED_TURNOVER,
ROUND(
C.YTD_TERMED_DRIVERS / (A.DRIVER_COUNT_BEG_YR + B.YTD_HIRED_DRIVERS) * 1,
4
) AS TURNOVER,
G.COUNTS AS EOY_FORECAST_COUNTS,
+ EOY_FORECAST_COUNTS - D.DRIVER_COUNT_END_MO AS NEED_TO_HIRE,
E.RANGE_HIRED_DRIVERS,
F.RANGE_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS DRIVER_COUNT_BEG_YR
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) < SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_TERMINATIONDT, 1, 4) >= SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) A
LEFT JOIN (
-- Year to Date (Hired Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS YTD_HIRED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) B ON A.CSC = B.CSC
AND A.DRIVERTYPE = B.DRIVERTYPE
LEFT JOIN (
-- Year to Date (Termed Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS YTD_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_TERMINATIONDT, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) C ON A.CSC = C.CSC
AND A.DRIVERTYPE = C.DRIVERTYPE
LEFT JOIN (
-- End of Month (Driver Count):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS DRIVER_COUNT_END_MO --, Forecast_Counts
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) <= SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_HIREDATE, 6, 2) <= SUBSTRING(CURRENT_DATE(), 6, 2)
AND SUBSTRING(MPP_TERMINATIONDT, 1, 4) >= SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_TERMINATIONDT, 6, 2) > SUBSTRING(CURRENT_DATE(), 6, 2)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) D ON A.CSC = D.CSC
AND A.DRIVERTYPE = D.DRIVERTYPE
LEFT JOIN (
-- Montly/Date Range (Hired Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS RANGE_HIRED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
MPP_HIREDATE BETWEEN '2022-07-01'
AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) E ON A.CSC = E.CSC
AND A.DRIVERTYPE = E.DRIVERTYPE
LEFT JOIN (
-- Montly/Date Range (Termed Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS RANGE_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
MPP_TERMINATIONDT BETWEEN '2022-07-01'
AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) F ON A.CSC = F.CSC
AND A.DRIVERTYPE = F.DRIVERTYPE
INNER JOIN "DEV"."PUBLIC"."TMW_DRIVERFORECAST" G ON A.CSC = G.CSC -- AND A.DRIVERTYPE = E.DRIVERTYPE
WHERE
SUBSTRING(MONTH, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MONTH, 6, 2) = 12
/* End of Current Year Forecast */
----------------------------------------------------------------------------------------------------------------
-- (Works 3rd Graph) Sub-Total Line Grouping and % Formulas (to get correct Percentages):
----------------------------------------------------------------------------------------------------------------
UNION
-- Beginning of Year (Driver Count):
SELECT
CASE
WHEN A.CSC = 'ABI' THEN 'Abilene'
WHEN A.CSC = 'ALB' THEN 'Albuquerque'
WHEN A.CSC = 'AMA' THEN 'Amarillo'
WHEN A.CSC = 'BOV' THEN 'Bovina'
WHEN A.CSC = 'DFW' THEN 'Dallas / Fort Worth'
WHEN A.CSC = 'ELP' THEN 'El Paso'
WHEN A.CSC = 'HOU' THEN 'Houston'
WHEN A.CSC = 'ODE' THEN 'Odessa'
WHEN A.CSC = 'OKC' THEN 'Oklahoma City'
WHEN A.CSC = 'PHO' THEN 'Phoenix'
WHEN A.CSC = 'SAN' THEN 'San Antonio'
WHEN A.CSC = 'TOP' THEN 'Topeka'
WHEN A.CSC = 'TUL' THEN 'Tulsa'
WHEN A.CSC = 'WIC' THEN 'Wichita'
END AS CSC,
'Total' AS DRIVERTYPE,
--A.DRIVERTYPE,
SUM(A.DRIVER_COUNT_BEG_YR) AS DRIVER_COUNT_BEG_YR,
SUM(B.YTD_HIRED_DRIVERS) AS YTD_HIRED_DRIVERS,
SUM(C.YTD_TERMED_DRIVERS) AS YTD_TERMED_DRIVERS,
SUM(D.DRIVER_COUNT_END_MO) AS DRIVER_COUNT_END_MO,
SUM(
ROUND(
(A.DRIVER_COUNT_BEG_YR + D.DRIVER_COUNT_END_MO) / 2
)
) AS AVG_DRIVER_COUNT,
ROUND(SUM(C.YTD_TERMED_DRIVERS) / AVG_DRIVER_COUNT, 4) AS TEN_MONTHS_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER / 10, 4) AS MONTHLY_TURNOVER,
ROUND(TEN_MONTHS_TURNOVER * 12 / 10, 4) AS ANNUALIZED_TURNOVER,
ROUND(
SUM(C.YTD_TERMED_DRIVERS) / (
SUM(A.DRIVER_COUNT_BEG_YR) + SUM(B.YTD_HIRED_DRIVERS)
) * 1,
4
) AS TURNOVER,
SUM(G.COUNTS) AS EOY_FORECAST_COUNTS,
+ EOY_FORECAST_COUNTS - SUM(D.DRIVER_COUNT_END_MO) AS NEED_TO_HIRE,
SUM(E.RANGE_HIRED_DRIVERS) AS RANGE_HIRED_DRIVERS,
SUM(F.RANGE_TERMED_DRIVERS) AS RANGE_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS DRIVER_COUNT_BEG_YR
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) < SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_TERMINATIONDT, 1, 4) >= SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) A
LEFT JOIN (
-- Year to Date (Hired Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS YTD_HIRED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) B ON A.CSC = B.CSC
AND A.DRIVERTYPE = B.DRIVERTYPE
LEFT JOIN (
-- Year to Date (Termed Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS YTD_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_TERMINATIONDT, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) C ON A.CSC = C.CSC
AND A.DRIVERTYPE = C.DRIVERTYPE
LEFT JOIN (
-- End of Month (Driver Count):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS DRIVER_COUNT_END_MO --, Forecast_Counts
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
SUBSTRING(MPP_HIREDATE, 1, 4) <= SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_HIREDATE, 6, 2) <= SUBSTRING(CURRENT_DATE(), 6, 2)
AND SUBSTRING(MPP_TERMINATIONDT, 1, 4) >= SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MPP_TERMINATIONDT, 6, 2) > SUBSTRING(CURRENT_DATE(), 6, 2)
AND MPP_TYPE1 IN ('CD', 'LO')
/* CD is Company Driver?, LO is Independent Contractor? */
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) D ON A.CSC = D.CSC
AND A.DRIVERTYPE = D.DRIVERTYPE
LEFT JOIN (
-- Monthly/Date Range (Hired Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS RANGE_HIRED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
MPP_HIREDATE BETWEEN '2022-07-01'
AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) E ON A.CSC = E.CSC
AND A.DRIVERTYPE = E.DRIVERTYPE
LEFT JOIN (
-- Monthly/Date Range (Termed Drivers):
SELECT
MPP_Terminal AS CSC,
MPP_Type1 AS DRIVERTYPE,
COUNT(MPP_TERMINAL) AS RANGE_TERMED_DRIVERS
FROM
(
SELECT
MPP_Terminal,
/* <-- is Join to CSC */
MPP_Status,
MPP_Type1,
MPP_HireDate,
MPP_TerminationDT
FROM
"DEV"."PUBLIC"."TMW_MANPOWERPROFILE"
WHERE
MPP_TERMINATIONDT BETWEEN '2022-07-01'
AND '2022-07-31'
AND MPP_TYPE1 IN ('CD', 'LO')
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
)
GROUP BY
MPP_TERMINAL,
MPP_TYPE1
ORDER BY
MPP_TERMINAL,
MPP_TYPE1
) F ON A.CSC = F.CSC
AND A.DRIVERTYPE = F.DRIVERTYPE
INNER JOIN "DEV"."PUBLIC"."TMW_DRIVERFORECAST" G ON A.CSC = G.CSC -- AND A.DRIVERTYPE = G.DRIVERTYPE
WHERE
SUBSTRING(MONTH, 1, 4) = SUBSTRING(CURRENT_DATE(), 1, 4)
AND SUBSTRING(MONTH, 6, 2) = 12
/* End of Current Year Forecast */
GROUP BY
A.CSC --,A.DRIVERTYPE
ORDER BY
CSC,
DRIVERTYPE
)
)
SELECT
*
FROM
PT PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN (
'Company Driver',
'Independent Contractor',
'Total'
)
) AS PivotTable;Stack Overflow用户
发布于 2022-08-05 13:13:22
将查询压缩到以下内容：
SELECT CSC, DRIVERTYPE, TEN_MONTHS_TURNOVER FROM
(
⋯
) H
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN ('Company Driver', 'Independent Contractor', 'Total')
) AS PivotTable表示结果集列与枢轴之间的断开。你可能会发现这样的事情更可行：
SELECT *
(
⋯
) H
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN ('Company Driver', 'Independent Contractor', 'Total')
) AS PivotTable页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73240874
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号