select a.total, b.felony_sentenced, c.felony_unsentenced, 
       d.misdemeanor_sentenced, e.misdemeanor_Unsentenced, 
       f.warrant_felony_sentenced, g.warrant_felony_unsentenced, 
       h.warrant_misdemeanor_sentenced, i.warrant_misd_unsentenced, 
       j.total_sentenced
from ( 
select 1 as dummy, count ( distinct person_id ) as total
from bookings a, placements b
where   '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a,
(select  1 as dummy, count ( distinct person_id ) as felony_sentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank <=  18
and a.person_id in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) b,
( select  1 as dummy, count ( distinct person_id ) as felony_unsentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank <=  18
and a.person_id not in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) c, 
( select  1 as dummy, count ( distinct person_id ) as misdemeanor_sentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank between 19 and 99
and a.person_id in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) d, 
(select  1 as dummy, count ( distinct person_id ) as misdemeanor_Unsentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank between 19 and 99
and a.person_id not in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) e, 
( select  1 as dummy, count ( distinct person_id ) as warrant_felony_sentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank = 18.5
and a.person_id  in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id  in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) f, 
( select  1 as dummy, count ( distinct person_id ) as warrant_felony_unsentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.booking_number = b.booking_number 
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank = 18.5
and a.person_id  not in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id  in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) g, 
(select  1 as dummy, count ( distinct person_id ) as warrant_misdemeanor_sentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank = 100
and a.person_id  in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id  in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) h, 
( select  1 as dummy, count ( distinct person_id ) as warrant_misd_unsentenced
from bookings a, 
( select a.booking_number, min( a.rank ) as rank
from ( select a.booking_number, f.rank as rank
from bookings a, placements b, arrests c, arrest_charges d, 
     charge_codes e, charge_category_codes f
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and d.charge_code = e.code
and e.charge_category_code = f.charge_category_code
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
union all
select a.booking_number, d.rank as rank
from bookings a, placements b, arrests c, 
     ( select arrest_id, 18.5 as rank
       from warrants
       where confirm_comment like '%DIST%'
       UNION all
       select arrest_id, 100 as rank
       from warrants
       where confirm_comment like '%METRO%'
 ) d
where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
and a.arrival_date >= '1-Jan-2000'
and a.booking_number = b.booking_number 
and a.booking_number = c.booking_number
and c.arrest_id = d.arrest_id
and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
			     9020, 9408, 9040, 9130, 9388, 
			     9150, 9280 )
 ) a 
group by a.booking_number ) b 
where a.booking_number = b.booking_number 
and b.rank = 100
and a.person_id not in
( select   b.person_id 
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.person_id  in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
))) i, 
( select  1 as dummy, count ( distinct b.person_id ) as total_sentenced
from case_sentences a, bookings b
where (  '1-Jan-2004' between a.sentence_start_date and nvl( a.sentence_end_date,  a.sentence_start_date + nvl( a.SENTENCE_DURATION, sysdate - a.sentence_start_date )   )  )
and a.booking_number = b.booking_number
and b.arrival_date >= '1-Jan-2000'
and b.person_id in 
(    
     select  distinct a.person_id  
     from bookings a, placements b
     where  '1-Jan-2004' between a.arrival_date and nvl( release_date, sysdate )
     and '1-Jan-2004' between b.start_date and nvl( b.end_date, sysdate )
     and a.booking_number = b.booking_number 
     and b.cell_block_id not in ( 9030, 9347, 9306, 9190, 9327,
				  9020, 9408, 9040, 9130, 9388, 
				  9150, 9280 )
) ) j
where a.dummy = b.dummy
and  b.dummy = c.dummy
and  c.dummy = d.dummy
and  d.dummy = e.dummy
and  e.dummy = f.dummy
and  f.dummy = g.dummy
and  g.dummy = h.dummy
and  h.dummy = i.dummy
/