I am trying to find how many days that the company from EmployeeActivity Table using Postgres did not have any activity of an joining an employee or cutting employees. Null refer to they who still do activity inside the company meanwhile DateLeave refer to them leaving the company or not working anymore.
DateJoined DateLeave Name
................................
2012-06-20 NULL Terrence
2012-06-21 2013-06-23 Mady
2010-06-20 2012-06-24 Greg
2013-06-20 NULL Matt
my trials for this was
select EXTRACT(DAY FROM MAX(EmployeeActivity.DateJoined) - MIN(EmployeeActivity.DateLeave)
From EmployeeActivity
WHERE EmployeeActivity.DateLeave IS 'NULL'
However it shows wrong value, especially for longer table
Output Expectation: My expectation for this output is to query the longest period of days that the company have no activity in assigning or firing Employee.
If I've understood correctly, the following should meet your needs:
SELECT
ActivityDate,
lag(ActivityDate) over (ORDER BY ActivityDate) as PreviousActivityDate,
Date_Part('day',ActivityDate - lag(ActivityDate) over (ORDER BY ActivityDate)) as Difference
FROM
(
select DateJoined as ActivityDate from EmployeeActivity
union
select coalesce(DateLeave,now()) from EmployeeActivity
) AllActivityDates
ORDER BY Difference DESC
LIMIT 1 OFFSET 1
The reason for the OFFSET 1 is because the earliest DateJoined doesn't have a previous row, and that one comes to the top, we're just skipping it.