This discussion is archived
7 Replies Latest reply: Feb 8, 2013 10:34 AM by 990049 RSS

Date Diff Issue

990049 Newbie
Currently Being Moderated
Hello,

I am building out a report where I am looking at a date for one field and a date for a second field and then also hold dates. I need to figure out the days inbetween from date 1 to hold start date, hold end date to 2nd hold start date and so fourth. Data looks something like this:


Account Created Date Closed Date Hold Type Hold Start Date Hold End Date
11111 01/01/2010 02/01/12 apples 01/05/2010 06/05/2010
11111 01/01/2010 02/01/12 apples 07/05/2011 08/05/2011
11111 01/01/2010 02/01/12 apples 09/05/2011 01/01/2012


Need to get the days in between from created date to hold start date 01/05/10, then from hold end date 06/05/10 to hold start date 07/05/11, and so on.


How would I go about looking at date difference for dates that are in the same column?
  • 1. Re: Date Diff Issue
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Date Diff Issue
    IvanBlanarik Journeyer
    Currently Being Moderated
    Hi,
    if I understand you correctly, you need to use analytics function LAG. This function enables you to take the value from previous row(hold_end). When there is no previous row it uses default value(created).
    select t.*,
      hold_start - lag(hold_end, 1, created) over(order by hold_start) as days_between
    from table t 
    Here is a short description how it works:
    http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php
  • 3. Re: Date Diff Issue
    990049 Newbie
    Currently Being Moderated
    Normally I would use lead/lag, however I am now working out of MS SQL 2008 and I have found out there is no lead/lag. Would you know a way to go about it in MS SQL?

    Thank you
  • 4. Re: Date Diff Issue
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!

    Whenever you have a question, please post CREATE TABLE and INSERT statements for your sample data.
    Also post the results you want from that data, formatted, between \
     tags, as explained in the forum FAQ {message:id=9360002}
    
    It sounds like you want this:
    SELECT     account
    ,     created_date
    ,     hold_start_date
    ,     hold_end_date
    ,     hold_start_date - created_date     AS days_from_created
    ,     LEAD (hold_start_date)
         OVER ( PARTITION BY account
              ORDER BY      hold_start_date
              ) - hold_start_date          AS days_to_next_hold
    FROM     table_x
    ORDER BY account
    ,      hold_start_date
    ;
    In Oracle, to get the number of days between two DATEs, just subtract one from the other.  
    To get a value from the next row in the result set, use the analytic LEAD function.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 5. Re: Date Diff Issue
    bencol Pro
    Currently Being Moderated
    987046 wrote:
    Normally I would use lead/lag, however I am now working out of MS SQL 2008 and I have found out there is no lead/lag. Would you know a way to go about it in MS SQL?

    Thank you
    https://forums.mssql.com ?

    or google:

    http://msdn.microsoft.com/en-us/library/hh231256.aspx
  • 6. Re: Date Diff Issue
    990049 Newbie
    Currently Being Moderated
    Thanks I'll try the forum, too bad I have 2008 otherwise the second link would have worked =)
  • 7. Re: Date Diff Issue
    pollywog Expert
    Currently Being Moderated
    I've never used SqlServer however I have a book that shows how to convert different function to and from oracle.
    for your problem it says.
    "Use a scalar subqueure to find the next date relative to your date then use datediff function to find difference"

    so maybe something like
    SELECT datediff (day, x.hold_end, x.next_hold_end) diff
      FROM (SELECT e.hold_end,
                   (SELECT MIN (d.hold_end)
                      FROM mytable d
                     WHERE d.hold_end > e.hold_end)
                      next_hold_end
              FROM mytable e) x
    not sure if that is helpful or not

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points