SELECT
  TO_CHAR(fs.est_time_departure_local , 'dd/mm/yy HH24:mi') depdate,
  TO_CHAR(fs.est_time_arrival_local , 'dd/mm/yy HH24:mi') arrivaldate,
  f.flight_number AS flightno,
  sinf.ssr_code AS ssr_code,
  sinf.ssr_desc AS ssr_desc,
  ppss.airport_code AS airport,
  fs.flt_seg_id,
  count(sinf.ssr_code)
FROM t_flight_segment fs ,
  t_flight f ,
  t_pnr_segment ps ,
  t_pnr_pax_fare_segment ppfs,
  t_pnr_pax_fare ppf ,
  t_ssr_info sinf ,
  t_ssr_sub_category scat,
  t_pnr_passenger pax ,
  t_pnr_pax_segment_ssr ppss
WHERE pax.pnr_pax_id  = ppf.pnr_pax_id
AND ppf.ppf_id      = ppfs.ppf_id
AND ppfs.pnr_seg_id = ps.pnr_seg_id
AND ps.flt_seg_id   = fs.flt_seg_id
AND fs.flight_id    = f.flight_id
AND ppss.ppfs_id    = ppfs.ppfs_id
AND ppss.ssr_id     = sinf.ssr_id
AND sinf.ssr_sub_cat_id = scat.ssr_sub_cat_id
AND f.flight_number = 'G90101'
AND fs.est_time_departure_local BETWEEN TO_DATE('14-Oct-2011 08:00:00','dd-mon-yyyy HH24:mi:ss') AND TO_DATE('14-Oct-2011 09:00:00','dd-mon-yyyy HH24:mi:ss')
AND scat.ssr_cat_id = 2
AND ps.status            <> 'CNX'
AND pax.status           <>'CNX'
AND ppss.status          <> 'CNX'
AND ppss.email_status     ='N'
AND (ppss.NO_OF_ATTEMPTS IS NULL
OR ppss.NO_OF_ATTEMPTS    <3)
GROUP BY fs.est_time_departure_local,fs.est_time_arrival_local,f.flight_number , sinf.ssr_code,sinf.ssr_desc,ppss.airport_code,fs.flt_seg_id
ORDER BY fs.est_time_departure_local,fs.est_time_arrival_local,f.flight_number;