Hej!
Jag har en lösning som får lite decimalfel och tyvärr så åker jag på semester om någon dag och hinner därför inte fixa mer med lösningen. Så jag tänkte att jag skickar in den ändå så kan jag läsa sen vad jag gjorde/tänkte fel. Hoppas det är ok.
SELECT
VehicleName
,CAST(SUM(ConsumptionPerMin*Duration)AS FLOAT)/CAST(SUM(Duration)AS FLOAT) AS Top20PctAveragePerMinute
FROM(
SELECT
VehicleName
,Duration
,ConsumptionPerMin
FROM(
SELECT
VehicleName
,Duration
,ConsumptionPerMin
,ROW_NUMBER() over (Partition BY VehicleName
order by ConsumptionPerMin DESC) as Rank
,COUNT(*) over (Partition BY VehicleName) as TotalRank
FROM(
SELECT
A.[VehicleName]
,CAST(DATEDIFF(ms,A.[StartTime],B.[StartTime])AS FLOAT) AS Duration
,CAST((B.[MeterValue]-A.[MeterValue])/(CAST(DATEDIFF(ms,A.[StartTime],B.[StartTime])AS FLOAT)/1000/60)AS FLOAT) AS ConsumptionPerMin
FROM(
SELECT
[VehicleName]
,[StartTime]
,[MeterValue]
,ROW_NUMBER() OVER(PARTITION BY [VehicleName] ORDER BY [StartTime]ASC) AS 'RowNumber'
FROM [Min].[dbo].[MeterReadings]
) A
INNER JOIN (
SELECT
[VehicleName]
,[StartTime]
,[MeterValue]
,ROW_NUMBER() OVER(PARTITION BY [VehicleName] ORDER BY [StartTime] ASC) -1 AS 'RowNumber'
FROM [Min].[dbo].[MeterReadings]
) B ON A.[VehicleName] = B.[VehicleName] AND A.[RowNumber] = B.[RowNumber]
)RES
)RES
WHERE RANK <= (TotalRank*.2)
)RES
GROUP BY VehicleName
ORDER BY 1
Mvh
Panos