Select name, cast(x/100.00 as decimal(18, 2)),
CAST(CAST(y * 100.00 / sum(x) over() AS DECIMAL(18, 2)) AS VARCHAR(50)) + ‘%’,
case when cast(y * 100.00/(sum(x) over()) as decimal(18, 2)) between 0 and 80 then ‘A’
when cast(y * 100.00/(sum(x) over()) as decimal(18, 2)) between 80 and 95 then ‘B’
else ‘C’ end
from (
Select a1.name, a1.x, sum(a2.x) y
from
(Select
name, p.ID_psg, SUM(datediff(ss, time_out, case when time_out < time_in then 0 else 1 end + time_in)) x
from Pass_in_trip p, Trip t, passenger pa where p.trip_no = t.trip_no and pa.ID_psg = p.ID_psg
group by name, p.id_psg) a1,
(Select
name, p.ID_psg, SUM(datediff(ss, time_out, case when time_out < time_in then 0 else 1 end + time_in)) x
from Pass_in_trip p, Trip t, passenger pa where p.trip_no = t.trip_no and pa.ID_psg = p.ID_psg
group by name, p.id_psg) a2
where a1.x < a2.x or a1.x = a2.x and (a1.name > a2.name or a1.name = a2.name and a1.ID_psg <= a2.id_psg)
group by a1.name, a1.x, a1.id_psg
) mT
My solution without using WITH is:
select 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
(select p.ID_psg ID, 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
) income
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
) PersonalData
) as numeric(10, 2)), 2
) res from
(select p.ID_psg ID, 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
) income
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
) s0
inner join (select p.ID_psg ID, 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
) income
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
) 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;