×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Date Diff calculation - Code Snippet

Date Diff calculation - Code Snippet

Date Diff calculation - Code Snippet

(OP)
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.

RE: Date Diff calculation - Code Snippet

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:

CODE

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:

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.

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

RE: Date Diff calculation - Code Snippet

(OP)
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.

RE: Date Diff calculation - Code Snippet

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close