By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE dept_vw (id, dept, name, from_Date, to_Date, mi, avg, dept_id) AS
SELECT 12, 'CSE', 'BRADLEY', DATE '2015-04-01', DATE '2015-06-25', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 22, 'CSE', 'ASHLEY', DATE '2015-02-01', DATE '2015-06-15', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 30, 'CSE', 'GARCES', DATE '2014-04-01', DATE '2015-02-16', 'TR', 3.77, 70 FROM DUAL UNION ALL
SELECT 25, 'CSE', 'LYNCH', DATE '2014-10-04', DATE '2015-02-15', 'CH', 3.08, 70 FROM DUAL UNION ALL
SELECT 35, 'CSE', 'STRONG', DATE '2014-11-27', DATE '2015-02-15', 'CH', 4.08, 70 FROM DUAL UNION ALL
SELECT 37, 'CSE', 'KEFFLER', DATE '2015-01-01', DATE '2015-01-02', 'CH', NULL, 70 FROM DUAL UNION ALL
SELECT 34, 'CSE', 'STRONG', DATE '2014-04-01', DATE '2014-10-26', 'TD', 4, 70 FROM DUAL UNION ALL
SELECT 24, 'CSE', 'LYNCH', DATE '2014-04-01', DATE '2014-08-23', 'TD', 3, 70 FROM DUAL UNION ALL
SELECT 33, 'CSE', 'STRONG', DATE '2014-02-11', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 29, 'CSE', 'GARCES', DATE '2013-12-11', DATE '2014-03-31', 'AN', 3.54, 70 FROM DUAL UNION ALL
SELECT 23, 'CSE', 'LYNCH', DATE '2014-02-08', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 21, 'CSE', 'SHI', DATE '2013-04-01', DATE '2014-01-13', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 28, 'CSE', 'DENNEY', DATE '2013-09-01', DATE '2014-01-13', 'CH', 3.92, 70 FROM DUAL UNION ALL
SELECT 18, 'CSE', 'SAVOY', DATE '2013-04-01', DATE '2013-11-14', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 27, 'CSE', 'WHITE', DATE '2013-04-01', DATE '2013-06-28', 'GC', 2, 70 FROM DUAL UNION ALL
SELECT 26, 'CSE', 'WHITE', DATE '2013-01-01', DATE '2013-03-31', 'AN', 2.77, 70 FROM DUAL UNION ALL
SELECT 17, 'CSE', 'SAVOY', DATE '2013-02-01', DATE '2013-03-31', 'AN', 2.54, 70 FROM DUAL UNION ALL
SELECT 20, 'CSE', 'SHI', DATE '2013-02-01', DATE '2013-03-31', 'AN', 3.15, 70 FROM DUAL;
18 rows affected
CREATE TABLE avg_vw (dept, avg, dept_id) AS
SELECT 'CSE', 3.31, 70 FROM DUAL;
1 rows affected
SELECT id, dept, name, from_date, to_date, mi, avg, dept_id
FROM (
SELECT id , dept, name, from_date, to_date, mi, avg, dept_id, 1 AS priority
FROM dept_vw vw
UNION ALL
SELECT NULL, dept, NULL, NULL, NULL, NULL, round(avg,2), dept_id, 2
FROM avg_vw
)
ORDER BY dept_id DESC, priority, to_Date DESC;
| ID | DEPT | NAME | FROM_DATE | TO_DATE | MI | AVG | DEPT_ID |
|---|---|---|---|---|---|---|---|
| 12 | CSE | BRADLEY | 2015-04-01 00:00:00 | 2015-06-25 00:00:00 | CH | 3.69 | 70 |
| 22 | CSE | ASHLEY | 2015-02-01 00:00:00 | 2015-06-15 00:00:00 | EN | null | 70 |
| 30 | CSE | GARCES | 2014-04-01 00:00:00 | 2015-02-16 00:00:00 | TR | 3.77 | 70 |
| 25 | CSE | LYNCH | 2014-10-04 00:00:00 | 2015-02-15 00:00:00 | CH | 3.08 | 70 |
| 35 | CSE | STRONG | 2014-11-27 00:00:00 | 2015-02-15 00:00:00 | CH | 4.08 | 70 |
| 37 | CSE | KEFFLER | 2015-01-01 00:00:00 | 2015-01-02 00:00:00 | CH | null | 70 |
| 34 | CSE | STRONG | 2014-04-01 00:00:00 | 2014-10-26 00:00:00 | TD | 4 | 70 |
| 24 | CSE | LYNCH | 2014-04-01 00:00:00 | 2014-08-23 00:00:00 | TD | 3 | 70 |
| 33 | CSE | STRONG | 2014-02-11 00:00:00 | 2014-03-31 00:00:00 | AN | null | 70 |
| 29 | CSE | GARCES | 2013-12-11 00:00:00 | 2014-03-31 00:00:00 | AN | 3.54 | 70 |
| 23 | CSE | LYNCH | 2014-02-08 00:00:00 | 2014-03-31 00:00:00 | AN | null | 70 |
| 21 | CSE | SHI | 2013-04-01 00:00:00 | 2014-01-13 00:00:00 | CH | 3.69 | 70 |
| 28 | CSE | DENNEY | 2013-09-01 00:00:00 | 2014-01-13 00:00:00 | CH | 3.92 | 70 |
| 18 | CSE | SAVOY | 2013-04-01 00:00:00 | 2013-11-14 00:00:00 | EN | null | 70 |
| 27 | CSE | WHITE | 2013-04-01 00:00:00 | 2013-06-28 00:00:00 | GC | 2 | 70 |
| 26 | CSE | WHITE | 2013-01-01 00:00:00 | 2013-03-31 00:00:00 | AN | 2.77 | 70 |
| 17 | CSE | SAVOY | 2013-02-01 00:00:00 | 2013-03-31 00:00:00 | AN | 2.54 | 70 |
| 20 | CSE | SHI | 2013-02-01 00:00:00 | 2013-03-31 00:00:00 | AN | 3.15 | 70 |
| null | CSE | null | null | null | null | 3.31 | 70 |