with 
tblPerjalanan as (
select distinct a.KodeAgen, h.NamaAgen, a.KodePerjalanan, c.KodeRute, b.WaktuKeberangkatan,
d.NamaKota as Origin, e.NamaKota as Destination, f.NamaTipeBus, isnull(g.KodeBus + ' - ' + g.NoPlatKendaraan, '') as KodeBus
from Process_Tiket as a 
inner join View_Jadwal as b on a.KodePerjalanan = b.KodePerjalanan
inner join Administration_RuteLayanan as c on b.KodeRute = c.KodeRute
left join Administration_City as d on c.Origin = d.KodeKota
left join Administration_City as e on c.Destination = e.KodeKota
left join Administration_BusType as f on c.KodeTipeBus = f.KodeTipeBus
left join Administration_Bus as g on b.KodeBus = g.KodeBus
inner join Administration_Agent as h on a.KodeAgen = h.KodeAgen
where a.KodeAgen = '{0}' and
convert(varchar(20), b.WaktuKeberangkatan, 112) >= '{1}' and 
convert(varchar(20), b.WaktuKeberangkatan, 112) <= '{2}'
)
select a.*, 
(select count(KodeTiket) from Process_Tiket where KodePerjalanan = a.KodePerjalanan and KodeAgen = a.KodeAgen) as TotalTiketTerjual
from tblPerjalanan as a order by WaktuKeberangkatan;