-1

The question I am dealing with:

Wwhat is the longest period of time where there was no change in employment at the company. A change in employment is someone new starting or an existing employee leaving the company.

The table contains employee id, start_date, end_date, employee name.

I'm finding a tough time trying to start a query. I've looked up some gap and island information but am still stuck. Anyone with a solution I can parse through to better understand the concept?

Question

Dale K
  • 25,246
  • 15
  • 42
  • 71
Jeffrey Zhu
  • 1
  • 1

1 Answers1

1

This addresses the original version of the question.

If you want the longest period of time with no change, then you want to unpivot the dates and use lead() or lag(). Something like this:

with dates as (
      select start_date as dte
      from t
      union   -- on purpose to remove duplicates
      select end_date
      from t
     )
select (next_dte - dte)
from (select d.*, lead(dte) over (order by dte) as next_dte
      from dates d
     ) d
order by (next_dte - dte) desc
fetch first one row only;

Of course, the exact syntax for the logic might depend on your database, but this gives you an idea of what to do.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi Gordon, Thanks for the answer. That helps me be delve deeper into this problem. Admittedly, my SQL skills are rather new and I'm lacking an understanding of fundamental concepts. I've edited the post to include the exact question. The database is not specified and I'm more concerned with learning the concepts. Are you able to provide me with a complete answer so I can start searching up the functions you're using and piece it together by working backwards? Thanks again for you help. – Jeffrey Zhu Apr 11 '21 at 00:53