We want to determine which rows represent a range of consecutive projects.
For example, consider the below data. Excluding the first row, each row’s PROJ_START should equal the PROJ_END of the row before it. We want to find the range of dates for consecutive projects, we would like to return all rows where the current PROJ_END equals the next row’s PROJ_START
select proj_id, proj_start, proj_end
from (
select proj_id, proj_start, proj_end,
lead(proj_start)over(order by proj_id) next_proj_start
from V
) alias
where next_proj_start = proj_end
Finding Differences Between Rows in the Same Group or Partition
We want to return the DEPTNO, ENAME, and SAL of each employee along with the difference in SAL between employees in the same department. The difference should be between each current employee and the employee hired immediately afterward. For each employee hired last in his department, return “N/A” for the difference.
with next_sal_tab (deptno,ename,sal,hiredate,next_sal)
as
(
select
deptno,
ename,
sal,
hiredate,
lead(sal)over(partition by deptno order by hiredate) as next_sal
from emp
)
select deptno, ename, sal, hiredate, coalesce(cast(sal-next_sal as char), 'N/A') as diff
from next_sal_tab
Locating the Beginning and End of a Range of Consecutive Values
We have located the ranges of consecutive values and want to find just their start and end points.
Sample data and output
SELECT
proj_grp,
MIN(proj_start),
MAX(proj_end)
FROM
(
SELECT
proj_id,
proj_start,
proj_end,
SUM(flag) OVER( ORDER BY proj_id ) proj_grp
FROM
(
SELECT
proj_id,
proj_start,
proj_end,
CASE
WHEN LAG(proj_end) OVER( ORDER BY proj_id ) = proj_start THEN
0
ELSE
1
END flag
FROM
v
) alias1
) alias2
GROUP BY
proj_grp
Filling in Missing Values in a Range of Values
We want to return the number of employees hired each year for the entire decade of the 2005s (2000-2009), but there are some years in which no employees were hired.
Sample Output
SELECT
x.yr,
coalesce(cnt, 0) cnt
FROM
(
SELECT
EXTRACT(YEAR FROM MIN(hiredate) OVER()) - mod(EXTRACT(YEAR FROM MIN(hiredate) OVER()), 10) + ROWNUM - 1 yr
FROM
emp
WHERE
ROWNUM <= 10
) x
LEFT JOIN (
SELECT
TO_NUMBER(to_char(hiredate, 'YYYY')) yr,
COUNT(*) cnt
FROM
emp
GROUP BY
TO_NUMBER(to_char(hiredate, 'YYYY'))
) y ON ( x.yr = y.yr )
Generating Consecutive Numeric Values
We need to have row source generator like below.
SELECT
array id
FROM
dual
MODEL
DIMENSION BY ( 0 idx )
MEASURES ( 1 array )
RULES ITERATE(10) ( array[iteration_number]= iteration_number + 1 )
