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: 
You aren't returning the startDate in the sub-query. Add startDate to the inner query.

```
select MAX(endDate-startDate) from 
(select startDate, endDate from employees where endDate is not null) 
union all
(select startDate from employees)

```

EDIT:

You can also try this:

```
select MAX(endDate-startDate) from employees where endDate is not null

```

However, I don't think your query is what you're looking for as it only lists the longest term employee that no longer works at the company.
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.