with chart_query as
( select deptno    label
  ,      max(sal)  hi
  ,      min(sal)  lo
  ,      sum(sal)  summary
  ,      count(sal) rec_count
  ,      avg(sal)  average
  from   emp
  group
  by     deptno
)



with chart_query as
( select job    label
  ,      max(sal)  hi
  ,      min(sal)  lo
  ,      sum(sal)  summary
  ,      count(sal) rec_count
  ,      avg(sal)  average
  from   emp
  group
  by     job
)
, limits as
( select min(lo) overall_lo
  ,      max(hi) overall_hi
  ,      sum(summary)/sum(rec_count) overall_avg
  ,      sum(rec_count) overall_sum
  ,      max(length(label))+1 label_length
  from   chart_query
)
, chart_dimensions as
( select (overall_avg - x_min)/(x_max - x_min) * width axis_pos
  ,      x_min
  ,      x_max
  ,      width
  ,      marker_count
  from   ( select least(1000, 100*trunc(overall_lo/100)) x_min  
           ,      greatest(7000, 100*(1+trunc(overall_hi/100))) x_max  
           ,      80 width
           ,      overall_avg
           ,      6 marker_count
           from   limits
         )
)         
, marker_rows as
( SELECT LEVEL rn
  FROM   chart_dimensions
  CONNECT 
  BY     marker_count+1 > LEVEL
  ORDER 
  BY     LEVEL ASC 
) 
, marker_values as
( SELECT rn
  ,      x_min + (rn-1)* (x_max - x_min)/(marker_count-1)  marker_value
  FROM   chart_dimensions 
  ,      marker_rows
  ORDER 
  BY     rn
) 
, lo_hi as
( select   lpad(label, label_length)||'|' label
  ,        (lo - x_min)/(x_max - x_min) * width from_pos
  ,        (hi - x_min)/(x_max - x_min) * width to_pos
  ,        (average - x_min)/(x_max- x_min) * width avg_pos
  ,        (overall_avg - x_min)/(x_max - x_min) * width axis_pos
  ,        rownum seq
  ,        width
  ,        label_length
  from     chart_query
  ,        limits
  ,        chart_dimensions
)
, linespacers as
( select level rn
  from   dual
  connect by 3 > level
)
, lo_hi_bars as
( select label
  ,      lpad
          ( 'I', from_pos)
        ||rpad('-', avg_pos - from_pos-1, '-')
        ||'*'
        ||rpad('-', to_pos - avg_pos-1, '-')
        ||rpad('I', width -to_pos,' ')
         hilo_bar
  ,       seq        
  ,       label_length
, axis_pos
  from    lo_hi
  order
  by      seq
)
, lo_hi_chart as
( select  case rn
          when 2
          then lpad(' ',label_length)
               ||'|'
               ||lpad('|',axis_pos)
          else label
               ||substr( hilo_bar , 1, axis_pos - 1)
               ||'|'
               ||substr( hilo_bar , axis_pos +1)
          end hilo
from    lo_hi_bars
,       linespacers
order
by      seq
,       rn
)
, chart_legend as
( select label
  from   (  select null label -- empty line
            from   limits
            union all
            select 'Legend:'||chr(13)||chr(10)||'-------'
            from   limits
            union all
            select label||' : '
                   ||' Low='||lo
                   ||' Avg='||round(average,1)
                   ||' High='||hi
                   ||' count='||rec_count                   
            from   chart_query
            union all
            select null -- empty line
            from   dual
            union all
            select 'Overall : '
                   ||' Low='||overall_lo
                   ||' Avg='||round(overall_avg,1)
                   ||' High='||overall_hi
                   ||' count='||overall_sum
            from   limits            
          )
)
select hilo
from   lo_hi_chart
union all
select lpad('_',label_length,'_')
       ||'|'
       ||substr( lpad('|',axis_pos,'_')
               ||lpad('_',width,'_')
               , 1, width+2
               )
from   limits
,      chart_dimensions
union all
select lpad('|',label_length+1)
       ||markers
from   limits
,      chart_dimensions
,      ( select string_table_to_string (cast (collect(rpad(marker_value, width /(marker_count-1) )) as string_table_collection)) markers
         from   marker_values
         ,      chart_dimensions
       )
union all
select label 
from   chart_legend
/


-- DATE ORIENTED QUERY


with chart_query as
( select deptno    label
  ,      max(hiredate-sysdate)  hi
  ,      min(hiredate-sysdate)  lo
  ,      sum(hiredate-sysdate)  summary
  ,      count(hiredate) rec_count
  ,      avg(hiredate-sysdate)  average
  from   emp
  group
  by     deptno
)
, limits as
( select min(lo) overall_lo
  ,      max(hi) overall_hi
  ,      sum(summary)/sum(rec_count) overall_avg
  ,      sum(rec_count) overall_sum
  ,      max(length(label))+1 label_length
  from   chart_query
)
, chart_dimensions as
( select (overall_avg - x_min)/(x_max - x_min) * width axis_pos
  ,      x_min
  ,      x_max
  ,      width
  ,      marker_count
  from   ( select overall_lo-50 x_min  
           ,      overall_hi+100 x_max  
           ,      80 width
           ,      overall_avg
           ,      6 marker_count
           from   limits
         )
)         
, marker_rows as
( SELECT LEVEL rn
  FROM   chart_dimensions
  CONNECT 
  BY     marker_count+1 > LEVEL
  ORDER 
  BY     LEVEL ASC 
) 
, marker_values as
( SELECT rn
  ,      sysdate + (x_min + (rn-1)* (x_max - x_min)/(marker_count-1))  marker_value
  FROM   chart_dimensions 
  ,      marker_rows
  ORDER 
  BY     rn
) 
, lo_hi as
( select   lpad(label, label_length)||'|' label
  ,        (lo - x_min)/(x_max - x_min) * width from_pos
  ,        (hi - x_min)/(x_max - x_min) * width to_pos
  ,        (average - x_min)/(x_max- x_min) * width avg_pos
  ,        (overall_avg - x_min)/(x_max - x_min) * width axis_pos
  ,        rownum seq
  ,        width
  ,        label_length
  from     chart_query
  ,        limits
  ,        chart_dimensions
)
, linespacers as
( select level rn
  from   dual
  connect by 3 > level
)
, lo_hi_bars as
( select label
  ,      lpad
          ( 'I', from_pos)
        ||rpad('-', avg_pos - from_pos-1, '-')
        ||'*'
        ||rpad('-', to_pos - avg_pos-1, '-')
        ||rpad('I', width -to_pos,' ')
         hilo_bar
  ,       seq        
  ,       label_length
, axis_pos
  from    lo_hi
  order
  by      seq
)
, lo_hi_chart as
( select  case rn
          when 2
          then lpad(' ',label_length)
               ||'|'
               ||lpad('|',axis_pos)
          else label
               ||substr( hilo_bar , 1, axis_pos - 1)
               ||'|'
               ||substr( hilo_bar , axis_pos +1)
          end hilo
from    lo_hi_bars
,       linespacers
order
by      seq
,       rn
)
, chart_legend as
( select label
  from   (  select null label -- empty line
            from   limits
            union all
            select 'Legend:'||chr(13)||chr(10)||'-------'
            from   limits
            union all
            select label||' : '
                   ||' Low='||to_char(sysdate + lo)
                   ||' Avg='||to_char(sysdate + round(average,1))
                   ||' High='||to_char(sysdate + hi)
                   ||' count='||rec_count                   
            from   chart_query
            union all
            select null -- empty line
            from   dual
            union all
            select 'Overall : '
                   ||' Low='||to_char(sysdate + overall_lo)
                   ||' Avg='||to_char(sysdate + round(overall_avg,1))
                   ||' High='||to_char(sysdate + overall_hi)
                   ||' count='||overall_sum
            from   limits            
          )
)
select hilo
from   lo_hi_chart
union all
select lpad('_',label_length,'_')
       ||'|'
       ||substr( lpad('|',axis_pos,'_')
               ||lpad('_',width,'_')
               , 1, width+2
               )
from   limits
,      chart_dimensions
union all
select lpad('|',label_length+1)
       ||markers
from   limits
,      chart_dimensions
,      ( select string_table_to_string (cast (collect(rpad(marker_value, width /(marker_count-1) )) as string_table_collection)) markers
         from   marker_values
         ,      chart_dimensions
       )
union all
select label 
from   chart_legend
/