SQL 2016 and CTE Pareto Formula
Declare @start date = '2018-09-05 23:45:52.153',
@end date = '2018-09-06 21:36:12.473';
With Base
as
(
select
a.id as livecount ,
ad.EquipmentName,
ad.[Message]
from
AlarmLive a
join DimAlarmTrigger ad on a.AlarmTriggerId = ad.Id
Join DimAlarmPriority ap on ad.Priority = ap.Id
where
(a.OnTime >= @start and a.ontime < @end ) AND
(a.OffTime >= @start and a.OffTime < @end) AND
ad.Message like '%Fault%'
group by a.id, EquipmentName,ad.[Message]
UNION ALL
select
a.Id as hiscount ,
ad.EquipmentName,
ad.[Message]
from
FactAlarm a
join DimAlarmTrigger ad on a.AlarmTriggerId = ad.Id
join DimAlarmPriority ap on ad.Priority = ap.Id
where
(a.OnTime >= @start and a.ontime < @end ) AND
(a.OffTime >= @start and a.OffTime < @end) AND
ad.Message like '%Fault%'
), TotalRecords
as
(
Select
EquipmentName,
TotalIssues = count(1),
Amount_All = count(1) over ()
From Base
Group by EquipmentName
), Rank_all
as
(
Select
EquipmentName,
TotalIssues,
Amount_All,
Rank_all = rank() over (order by TotalIssues desc)
from TotalRecords
),Pareto_Value
as
(
select
EquipmentName,
TotalIssues,
Amount_All,
Rank_all,
Pareto_Value = round(((TotalIssues*1.0/ Amount_All * 1.0) * 100)-100,-1)
from Rank_all
)
Select * from Pareto_Value