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)
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.