Task 7 (aero)

–For each company, find the number of passengers (if any) that have been transported in April 2003 by ten-day periods.

 

select c.name, ISNULL(first, 0) first, ISNULL(second, 0) second, ISNULL(third, 0) third from Company c

left

join

(

select name, COUNT(pt.ID_psg) first from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-01 00:00:00.000’ AND ‘2003-04-10 00:00:00.000’

group

by name

)

x on x.name = c.name

left

join

(

select name, COUNT(pt.ID_psg) second from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-11 00:00:00.000’ AND ‘2003-04-20 00:00:00.000’

group

by name) y on y.name = c.name

left

join

(

select name, COUNT(pt.ID_psg) third from Trip t

inner

join Pass_in_trip pt on pt.trip_no = t.trip_no

inner

join Company c on c.ID_comp = t.ID_comp

where

pt.date between ‘2003-04-21 00:00:00.000’ AND ‘2003-04-30 00:00:00.000’

group

by name) z on z.name = c.name

where

NOT (first IS NULL and second IS NULL and third IS NULL)

order

by c.name