–Taking this fact into account, perform ABC analysis of passengers attractiveness (an airline company does not matter).
–ABC analysis is based on Pareto principle – 80% of your sales come from 20% of your clients.
–After the analysis, passengers should be grouped into 3 categories: A, B, and C. The algorithm:
–1. Calculate ratio of each passenger’s profit contribution with running totals (S) to total profit from all passengers (TS).
–Contribution with running totals for each passenger is calculated by summation of his profit with profits from all those
–passengers that give larger or equal profits.
–In the case of equal profits, the smaller running totals will be for the passenger, name of which goes earlier in alphabetic order.
–2. Group the passengers over ABC categories. Category "A" – passengers with S/TS between 0.00% and 80.00% (inclusive).
–Category "B" – S/TS from 80.01% up to 95.00%. Category "C" – S/TS from 95.01% up to 100%.
–Result set: Passenger’s name, sum of profit from this passenger ($), S/TS of the passenger in percent (accuracy within 0.01),
–ABC category of the passenger.
–name dohod nakop_itog ABC
–1Bruce Willis 593.40 71.52% A
–2George Clooney 390.00 94.16% B
–3Kevin Costner 472.80 89.36% B
–4Jennifer Lopez 199.20 99.15% C
–5Ray Liotta 473.40 83.54% B
–6Nikole Kidman 206.40 96.70% C
–7Alan Rickman 69.00 100.00% C
–8Kurt Russell 1078.20 42.06% A
–9Harrison Ford 1080.00 28.80% A
–10Russell Crowe 504.00 77.72% A
–11Steve Martin 864.00 64.21% A
–12Michael Caine 1260.00 15.51% A
–13Mullah Omar 936.00 53.58% A
–Consider calculation of running totals for a situation when namesakes have brought the identical profits.
–As passengers are added to results one by one, having identical profits they will have different percent of running totals.
Using WITH
–http://support.microsoft.com/kb/290136
–http://www.sqlteam.com/article/calculating-running-totals
–http://www.1keydata.com/sql/sql-running-totals.html
WITH
PersonalData(ID,
name, income) AS
(select p.ID_psg, name, sum(case
when
datediff(SS, time_out, time_in) > 0 then datediff(SS, time_out, time_in) * 0.01
else
datediff(SS, time_out, dateadd( DAY, 1, time_in)) * 0.01
end)
from Passenger
p
inner join Pass_in_trip pt on
pt.ID_psg = p.ID_psg
inner join Trip t on pt.trip_no = t.trip_no
group by p.ID_psg, name)
select ID, name, income, cast(res as varchar(6))+ ‘%’,
case
when res > 0 and res <= 80 then ‘A’
when res >= 80.01 and res <= 95 then ‘B’
else ‘C’
end from
(
select s0.ID, s0.name, s0.income, round(cast
(
( SUM(s1.income) * 100 / (select SUM(income) from PersonalData)
) as
float), 2) res from
PersonalData s0
inner join PersonalData s1
on (s0.income < s1.income) or (s0.income = s1.income and s0.ID <= s1.ID) or (s0.income = s1.income and s0.ID = s1.ID)
group by s0.name, s0.ID, s0.income
) x
order by name;