×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*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.

# 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
order by b_dt)
-e_dt,0)
calc_diff
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."

### 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.

Mufasa
(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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!