Date Diff calculation - Code Snippet

[sa0309]

I'm on oracle 10g. Looking for code samples that will caluclate days_diff between 2 dates on different rows. Below is some sample data.

Group by Field1

Row 1 = Days_diff between Row 1 e_dt and Row 2 b_dt
Row 2 = Days_diff between Row 2 e_dt and Row 3 b_dt
Row 3 = no calc because of change of Field 1 group
Row 4 = Days_diff between Row 4 e_dt and Row 5 b_dt
Row 5 = no calc because of change of Field 1 group


Row field1 b_dt e_dt days_diff
1 1000038 08-JAN-2013 10:41:00 PM 11-JAN-2013 10:57:00 PM 5
2 1000038 16-JAN-2013 07:18:00 PM 18-JAN-2013 08:29:00 PM 13
3 1000038 01-FEB-2013 07:18:00 PM 08-FEB-2013 08:29:00 PM 0
4 1002739 23-JAN-2013 11:35:00 AM 26-JAN-2013 07:56:00 PM 1
5 1002739 27-JAN-2013 06:00:00 PM 30-JAN-2013 04:26:00 PM 0


Any suggestions much appreciated.

Thank you.

 

[SantaMufasa]

sa,

In Oracle, a typical method for obtaining data from a fixed number of rows away from the current row is to use the LAG/LEAD Analytic Functions. Here is an example, below, which uses the LEAD function (i.e., obtain data from a specific row following the current row). The "PARTITION BY" clause specifies how to "group" the data rows:

First, to confirm the data that you specified:

select row#
      ,field1
      ,to_char(b_dt,'dd-MON-yyyy hh:mi:ss PM') b_dt
      ,to_char(e_dt,'dd-MON-yyyy hh:mi:ss PM') e_dt
      ,days_diff
  from sample;

ROW#     FIELD1 B_DT                      E_DT                       DAYS_DIFF
---- ---------- ------------------------- ------------------------- ----------
   1    1000038 08-JAN-2013 10:41:00 PM   11-JAN-2013 10:57:00 PM            5
   2    1000038 16-JAN-2013 07:18:00 PM   18-JAN-2013 08:29:00 PM           13
   3    1000038 01-FEB-2013 07:18:00 PM   08-FEB-2013 08:29:00 PM            0
   4    1002739 23-JAN-2013 11:35:00 AM   26-JAN-2013 07:56:00 PM            1
   5    1002739 27-JAN-2013 06:00:00 PM   30-JAN-2013 04:26:00 PM            0

5 rows selected.

Now the "magic" code:

select field1
      ,to_char(b_dt,'dd-MON-yyyy hh:mi:ss PM') b_dt
      ,to_char(e_dt,'dd-MON-yyyy hh:mi:ss PM') e_dt
      ,days_diff
      ,Nvl(lead(b_dt,1) over (partition by field1
                               order by b_dt)
                    -e_dt,0)
                               calc_diff
      ,Nvl(Round(lead(b_dt,1) over (partition by field1
                               order by b_dt)
                    -e_dt),0)
                               round_diff
  from sample;

    FIELD1 B_DT                      E_DT                       DAYS_DIFF  CALC_DIFF ROUND_DIFF
---------- ------------------------- ------------------------- ---------- ---------- ----------
   1000038 08-JAN-2013 10:41:00 PM   11-JAN-2013 10:57:00 PM            5 4.84791667          5
   1000038 16-JAN-2013 07:18:00 PM   18-JAN-2013 08:29:00 PM           13 13.9506944         14
   1000038 01-FEB-2013 07:18:00 PM   08-FEB-2013 08:29:00 PM            0          0          0
   1002739 23-JAN-2013 11:35:00 AM   26-JAN-2013 07:56:00 PM            1 .919444444          1
   1002739 27-JAN-2013 06:00:00 PM   30-JAN-2013 04:26:00 PM            0          0          0

5 rows selected.

Notice that I displayed both the "CALC_DIFF" (Calculated difference between the dates, based upon time-of-day) and the ROUND_DIFF (which rounds the difference to the nearest full day). In both instances, the data differ from your original day differeces (but you can see why).

Let us know if you have follow-up questions about any of the code, above.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sa0309]

Mufasa - very cool stuff. I need to research LAG/LEAD Analytic Functions further but I assume you can have multiple fields in the "PARTITION BY" clause.

I believe this will work for me.

Thank you!! Much appreciated.

 

[SantaMufasa]

Yes, you can have multiple expressions in the "PARTITION BY" clause.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.