7 Replies Latest reply: Feb 8, 2013 12:37 PM by pollywog RSS

    Date Diff Issue

    990049
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Date Diff Issue
            IvanBlanarik
            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
              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
                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
                  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
                    Thanks I'll try the forum, too bad I have 2008 otherwise the second link would have worked =)
                    • 7. Re: Date Diff Issue
                      pollywog
                      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