Differences

This shows you the differences between two versions of the page.

--- kurs:employees_per_year [2010/05/18 11:15]
127.0.0.1 external edit
+++ kurs:employees_per_year [2016/12/01 13:53] (current)
admin
@@ Line 8: / Line 8: @@
   select min(EXTRACT(year from hire_date)), max(EXTRACT(year from hire_date)) into
      sy, ey from employees;
+/*  select min(y), max(y) into sy, ey from
+    (select extract(year from hire_date) y from employees);     */
   for i in sy..ey loop
@@ Line 17: / Line 20: @@
 <code>
-with list_years as (
+with list_year as (
-select level-1 + 1985 as ya from dual connect by level<20
+select level-1 +
+(select extract(year from min(hire_date)) from emp)
+as yl from dual connect by level <=
+(select extract(year from max(hire_date)) -
+        extract(year from min(hire_date)) + 1
+from emp)
 )
-select ya, count(hire_date) from employees right join list_years
+select yl, count(employee_id) from emp right join
-on ya = (extract(year from hire_date))
+  list_year on yl = extract(year from hire_date)
-group by ya
+  group by yl
-order by ya
+  order by yl
 </code>

dokuwiki