–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 cleft
join(
select name, COUNT(pt.ID_psg) first from Trip tinner
join Pass_in_trip pt on pt.trip_no = t.trip_noinner
join Company c on c.ID_comp = t.ID_compwhere
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.nameleft
join(
select name, COUNT(pt.ID_psg) second from Trip tinner
join Pass_in_trip pt on pt.trip_no = t.trip_noinner
join Company c on c.ID_comp = t.ID_compwhere
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.nameleft
join(
select name, COUNT(pt.ID_psg) third from Trip tinner
join Pass_in_trip pt on pt.trip_no = t.trip_noinner
join Company c on c.ID_comp = t.ID_compwhere
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.namewhere
NOT (first IS NULL and second IS NULL and third IS NULL)order
by c.name