[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1083
  • Last Modified:

Difference between dates stored in Same column

Hi Experts
I am actually stuck with a logic problem on a SQL query in Oracle.  
I need the difference between two dates (that are in date datatypes) that are in same column.
I tried using the ROW_NUMBER () OVER (PARTITION BY) concept but its yielding incorrect values at some places. The "Days in each status" output column is what I am looking for.  The status id is the column which has few set of statuses which get repeated for each unique person.

STATUS_CHANGE_DATE      status_id      Days in each status
4/9/2009 6:51      220      0 (there is no value above it so it should be 0)
4/6/2009 7:57      114      4 (diff between the 4/9 and 4/6 incl both the days)
4/6/2009 7:56      6      1 (diff between 4/6 =0 but should consider it as1)
4/6/2009 7:56      5      1
3/30/2009 14:30      111      8 (diff between 4/6 and t3/30)
3/26/2009 9:04      208      5 (diff between 3/30 and 3/26)
3/25/2009 16:17      49      2 (diff between 3/26 and 3/25)

  • 4
  • 3
1 Solution
Hi gvsbnarayana,

The LAG() function would be better than the ROW_NUMBER() for this I think.  What is your PARTITION BY column(s)?  The LAG() function allows you to look back 'n' rows (the other one is LEAD() which allows you to look forward 'n' rows).  You could do something like

SELECT status_change_date, LAG(status_change_date,1,null) OVER(partition by some_column ORDER BY status_change_date) as prev_date
FROM ...

gvsbnarayanaAuthor Commented:
My partition column is the status_id as per the above example I provided. I tried using the lag but no luck.

please show me your SQL.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

gvsbnarayanaAuthor Commented:
SELECT   (SELECT user_status
            FROM apps.per_assignment_status_types
           WHERE assignment_status_type_id =
         (a.status_change_date - b.status_change_date) AS days,        
    FROM (SELECT assignment_status_id, status_change_date, assignment_id,
                 ROW_NUMBER () OVER (PARTITION BY assignment_status_type_id ORDER BY status_change_date)
            FROM apps.irc_assignment_statuses) a,
         (SELECT assignment_status_id, status_change_date, assignment_id,
                 ROW_NUMBER () OVER (PARTITION BY assignment_status_type_id ORDER BY status_change_date)
            FROM apps.irc_assignment_statuses) b
   WHERE a.assignment_status_type_id = b.assignment_status_type_id
     AND a.arowno - 1 = b.browno
     AND b.assignment_id = 649650
     order by a.status_change_date

Try this simple SQL first ...

BTW.  Are you sure the partitioning key is assignment_status_type_id?  These values must be the same across the multiple rows to form a partion.  Your examples in the original question had the values as different for each row.


SELECT assignment_status_id, 
       LAG (status_change_date,1,null) OVER (PARTITION BY assignment_id ORDER BY status_change_date) prev_date
  FROM apps.irc_assignment_statuses
 WHERE assignment_id = 649650
 ORDER BY assignment_status_id, 

Open in new window

gvsbnarayanaAuthor Commented:
The Assignment Status Type id gets repeated to each Assignment id. The Assignment Status table holds the lookup values for the statuses table. So, per defintion, to form a partition I considered the  assignment status type_id as the partition key. I think that was the mistake I did.

But as per the above query I got the values for the prev value as you considered the assignment id itself and I made my modifications accordingly.

Thanks for the idea.  
gvsbnarayanaAuthor Commented:
Thanks for showing me the right direction

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now