CODEWITHSUNDEEP
sqloracle10g
user8512043
user8512043

Reputation: 1157

In The Same Column, Get The Date Difference As Days

I've written a simple query that returns the last two attendance records of a person and here is a sample how it works:

Id      Date
305351  1/24/2018
305351  1/23/2018
E5671   10/12/2014
E5671   10/11/2014

The Query:

----Retrieve days from date difference - Starts
SELECT T1.EMPNO,
         T1.ATT_DATE
FROM (
     SELECT T2.EMPNO, T2.ATT_DATE,
            ROW_NUMBER() OVER(PARTITION BY T2.EMPNO ORDER BY T2.ATT_DATE DESC) as rn
            FROM TableName T2 
     )  T1
WHERE T1.rn <= 2
----Retrieve days from date difference - Ends

The above works well and now I am trying to get days from the date difference. Tried the following but returns incorrect days between two dates from the rows with a JOIN to the employee id:

TO_DATE(T1.ATT_DATE) - TO_DATE(T3.ATT_DATE) "DAYS"

Expected Output:

 Id      Date      Days
305351  1/24/2018  1
305351  1/23/2018  1
E5671   10/12/2014 1
E5671   10/11/2014 1

I am guessing, made a silly mistake here and would expect an idea to make it work - Thanks.

Update 1 - The output could be the following:

Expected Output:

Id      Date      Days
305351  1/24/2018  1 //(24-01-2018) - (23-01-2018) = Day 1 
E5671   10/12/2014 1 //(12-01-2018) - (11-01-2018) = Day 1

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use lag() and lead(). For instance:

SELECT T1.EMPNO, T1.ATT_DATE,
       (T1.ATT_DATE - T1.prev_att_date) as Days
FROM (SELECT T2.*,
             LAG(T2.ATT_DATE) OVER (PARTITION BY t2.EMPNO ORDER BY t2.ATT_DATE) as prev_att_date
             ROW_NUMBER() OVER (PARTITION BY T2.EMPNO ORDER BY T2.ATT_DATE DESC) as rn
      FROM TableName T2 
     )  T1
WHERE T1.rn <= 2;

This puts the days to the previous record on each row, so the values are not necessarily the same. Your question is unclear on whether the values should be the same on each row or whether the number should be to the previous value.

Upvotes: 1

Related Questions