Task 16 (aero)

–One second of a flight of each passenger gives 1 cent ($0.01) profit to airline company.

–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;