How do I caculate months between dates which have null values 1

[barnard90]

Hi

I have an employee table (EMPLOYEE) which has employee start date(ST_DT) and employee end date(END_DT)

I need to calculate the employee duration for each employee which is
End Date - Start date

There are some records which have blank start dates
There are some records which have blank end dates

Again , there are some records where the start date is more than the End date ( This is due to a typographical mistake)

In all these cases I need to report the employee duration as NULL

How can have a single SELECT statement which can give me the employee_duration

 

[Dagon]

I think NULL is exactly what you will get if you try to do:

end_date - null
or
null - start_date

Oracle evaluates any expression that contains a null to null, even if some of the other items involved have values. For the start_date > end_date, you can use a decode or case e.g.

decode(sign(end_date-start_date), -1, null, end_date-start_date)

 

[BJCooperIT]

Did you test it?

CREATE TABLE test_it (ename VARCHAR2(20), start_date DATE, end_date DATE);
INSERT INTO test_it VALUES('John','','');
INSERT INTO test_it VALUES('Amal','01-mar-1995','');
INSERT INTO test_it VALUES('Kirsten','','25-jun-2006');
INSERT INTO test_it VALUES('Diane','18-sep-1983','07-nov-2005');
INSERT INTO test_it VALUES('Bud','22-oct-1997','05-feb-1995');
COMMIT;

SELECT ename, start_date, end_date, 
       round(months_between(start_date,end_date),1) months
FROM test_it

[COLOR=blue]RESULTS:[/color]
[b]ENAME   START_DATE   END_DATE    MONTHS[/b]
John	
Amal    3/1/1995		
Kirsten              6/25/2006	
Diane   9/18/1983    11/7/2005   265.6
Bud     10/22/1997   2/5/1995    -32.5

Now all you need is to handle the invalid date range rows:

SELECT ename, start_date, end_date,
       decode(sign(months),-1,NULL,months) months
FROM   (SELECT emp_name, start_date, end_date, 
               round(months_between(end_date,start_date),1) months
        FROM test_it)

[COLOR=blue]RESULTS:[/color]
[b]ENAME   START_DATE   END_DATE    MONTHS[/b]
John			
Amal    3/1/1995		
Kirsten 6/25/2006	
Diane   9/18/1983    11/7/2005   265.6
Bud     10/22/1997   2/5/1995

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com