Этот запрос предоставляет нам время различных операций. Прямо сейчас он разбивает время каждого события по названию, дате и модели лопаты. Тем не менее, я хочу как-то объединить среднее значение всех имен в одной команде в новый столбец. Будучи новичком в игре SQL, было бы здорово иметь стратегию, которая могла бы предоставлять различные определения средних значений и возвращать их в одну и ту же таблицу. Изображение вывода следует по этой ссылке.
https://i.stack.imgur.com/6V5XB.png
DECLARE @start AS DATETIME = '2017-08-15'
DECLARE @end AS DATETIME = '2017-08-20'
SELECT *
FROM (
SELECT
[Name],
[Date],
MAX([Crew]) as [Crew],
MAX([Shovel_ID]) as [Shovel Model],
CONVERT(DECIMAL(10,1) ,AVG([empty])) as [Empty],
CONVERT(DECIMAL(10,1) ,AVG([HAUL])) AS [Hauling],
CONVERT(DECIMAL(10,0) ,AVG([Wait At Dump]*60)) AS [Wait at Dump],
CONVERT(DECIMAL(10,0) ,AVG([Dumping]*60)) as [Dumping],
CONVERT(DECIMAL(10,0) ,AVG([Spot at LU]*60)) as [Spot at LU],
CONVERT(DECIMAL(10,0) ,AVG([Load Time]*60)) AS [Truck Loading],
CONVERT(DECIMAL(10,0) ,AVG([QUEUE]*60)) as [Wait at Shovel],
CONVERT(DECIMAL(10,0) ,AVG([Hot Seat]*60)) as [Hot Seat],
CONVERT(DECIMAL(10,0) ,AVG([Empty Hauling]*60)) as [Empty Stopped],
CONVERT(DECIMAL(10,0) ,AVG([HAULING STOPPED]*60)) AS [Hauling Stopped]
FROM (
Select
haul_cycle,
max([name]) as [Name],
max([shovel_id]) as [Shovel_ID],
max([date]) as [Date],
max([crew]) as [Crew],
sum(dumping) as [Dumping],
sum([spot at lu]) as [Spot at LU],
sum([wait at dump]) as [Wait at Dump],
sum([empty]) as [Empty],
sum([hauling]) as [Haul],
SUM([Truck Loading]) as [Load Time],
SUM([Queue at LU]) as [QUEUE],
SUM([Hot Change]) as [Hot Seat],
SUM([EMPTY sTOPPED]) AS [Empty Hauling],
SUM([HAULing STOPPED]) AS [Hauling Stopped],
SUM([HAULing STOPPED]+[EMPTY STOPPED]+[Queue at LU]+[Truck Loading]+
[Hauling]+[Empty]+[Spot at LU]+[wait at dump]+[dumping]) as [Cycle Time],
COUNT([HAUL_CYCLE]) AS [Number of Cycles]
FROM (
SELECT
HAUL_CYCLE_TRANS.HAUL_CYCLE_REC_IDENT as [HAUL_CYCLE],
CREW_IDENT as [CREW],
badge.LAST_NAME + ' , ' + badge.FIRST_NAME as [Name],
DUMP_END_SHIFT_DATE as date ,
DUMP_END_SHIFT_IDENT,
CAST(EQUIPMENT_STATUS_TRANS.END_TIMESTAMP-
EQUIPMENT_STATUS_TRANS.START_TIMESTAMP AS FLOAT)*24*60 AS DURATION,
COALESCE(SUB_STATUS_DESC, STATUS_DESC) AS [Status],
CASE
WHEN HAUL_CYCLE_TRANS.LOADING_UNIT_IDENT IN (4001, 4002, 4005, 4006)
THEN '6060'
ELSE '7495'
END AS SHOVEL_ID
FROM HAUL_CYCLE_TRANS
LEFT JOIN HAUL_UNIT_STATUS_TRANS_COL on
HAUL_UNIT_STATUS_TRANS_COL.HAUL_CYCLE_REC_IDENT =
HAUL_CYCLE_TRANS.HAUL_CYCLE_REC_IDENT
LEFT JOIN EQUIPMENT_STATUS_TRANS on
EQUIPMENT_STATUS_TRANS.EQUIP_STATUS_REC_IDENT =
HAUL_UNIT_STATUS_TRANS_COL.EQUIP_STATUS_REC_IDENT
LEFT JOIN badge on badge.BADGE_IDENT =
haul_cycle_trans.HAULING_UNIT_BADGE_IDENT
LEFT JOIN EQUIP_STATUS_CODE on EQUIP_STATUS_CODE.STATUS_CODE =
EQUIPMENT_STATUS_TRANS.STATUS_CODE
LEFT JOIN EQUIP_SUB_STATUS_CODE on EQUIP_SUB_STATUS_CODE.SUB_STATUS_CODE =
EQUIPMENT_STATUS_TRANS.SUB_STATUS_CODE
WHERE dump_end_shift_date >= @start AND First_Name <> '') raw_data
PIVOT(SUM(duration) FOR [Status] IN ([Dumping], [Spot at LU],[Wait at Dump],
[Empty], [Empty Stopped], [Hauling Stopped], [Hauling], [Queue at LU],
[Truck Loading], [Hot Change])) DATAFIELD
GROUP BY haul_cycle) SUM_DATA_FOR_EACH_REC
GROUP BY [name], [date], Shovel_ID
) AVG_OPERATOR_STATUS
ORDER BY [NAME],[DATE]
