Below is the Snowflake query. I know it is ugly but it is what I have to work with here. The problem is my PIVOT isn't working. Everything up to that point works but when I try the Pivot I get an error: invalid identifier 'DRIVERTYPE'. I need to transpose my results to turn rows into columns based on the 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
Below is how the data looks from everything up to the Pivot:
I am guessing this is an Alias issue. I tried doing the top level select with an Alias and using that but no luck. Also tried A.VarNames, up to H.VarNames but still get the error when trying to use Pivot. I am open to other ways to get this done. Would like to see some examples in the answers please.
CodePudding user response：
Since I don't have your base tables and you indicated the majority of the query works, I wrote a simple test using WITH which provided a pivoted result set.
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;
So, applied it to your query - please see if this works for you:
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;
CodePudding user response：
Compressing your query to this:
SELECT CSC, DRIVERTYPE, TEN_MONTHS_TURNOVER FROM
(
⋯
) H
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN ('Company Driver', 'Independent Contractor', 'Total')
) AS PivotTable
implies a disconnect between the result set columns and the pivot. You may find something like this more workable:
SELECT *
(
⋯
) H
PIVOT (
AVG(TEN_MONTHS_TURNOVER) FOR DRIVERTYPE IN ('Company Driver', 'Independent Contractor', 'Total')
) AS PivotTable