Question: Given an employees table with the columns EmpID,FirstName,LastName,StartDate, and EndDate.
I want to use a query on Oracle to calculate the longest period in days that a company has gone without headcount change. 

Here is my query:

```
select MAX(endDate-startDate)

from
(select endDate 
from employees
where endDate is not null)

union all 

(select startDate 
from employees)

```

But I got an error:
ORA-00904:"STARTDATE":invalid identifier

***How can I fix this error? 
Is my query the correct answer to this question?***

Thanks

Answer: 
In a simplistic view, you would want to put together all the start-dates (when the headcount increases) and all the end-dates (when it decreases), combine them all, arrange them in increasing order, measure the differences between consecutive dates, and take the max.

"Put together" is a `UNION ALL`, and measure differences between "consecutive" dates can be done with the analytic function `lag()`.

One complication: one employee may start exactly on the same date another is terminated, so the headcount doesn't change. More generally, on any given date there may be starts and ends, and you need to exclude the dates when there are an equal number of starts and ends. So the first part of the solution is more complicated: you need to group by date and compare the start and end counts.

Something like this may work (not tested!):

```
with d ( dt, flag ) as (
       select start_date, 's' from employees union all
       select end_date  , 'e' from employees
     ),
     prep ( int ) as 
       select   dt - lag(dt) over (order by dt)
       from     d
       group by dt
       having   count(case flag when 's' then 1 end) != 
                                              count(case flag when 'e' then 1 end)
     )
select max(int) as max_interval
from   prep
;

```

**Edit** - Gordon has a good point in his solution: perhaps the longest period without a change in headcount is the current period (ending "now"). For this reason, one needs to add SYSDATE to the UNION ALL, like he did. It can be added with either flag (for example 's' to be specific).
I think the answer to your question is something like this:

```
select max(span)
from (select (lead(dte) over (order by dte) - dte) as span
      from (select startDate as dte from employees union all
            select endDate as dte from employees union all
            select trunc(sysdate) from dual
           ) d
     ) d;

```

A head-count change (presumably) occurs when an employee starts or stops. Hence, you want the largest interval between two such adjacent dates.