I'm a bit stuck with a query - I wish to find the differences between datetime values in different rows of a given column (further limited by the fact I wish to restrict output by another value as well). Please excuse the length of the explanation, but I want to be as clear as possible.
I've got a log table, with the structure:
UID | new_appstatus | datechanged
UID is a record ID from another table; new_appstatus is a numeric code, and datechanged is a datetime field.
Whenever the status of a given record in the main table is changed, a new line is inserted into my log table, which includes the UID of the record which has changed, the new status, and the date/time it changed. Where there are several changes to a given record in the main table over a period of time, you end up with multiple records in the log table.
I can very easily use "SELECT new_appstatus, datechanged FROM log_table WHERE uid='001' ORDER BY datechanged ASC" to generate a list of all the changes for a given UID (in this case, 001).
That results in an output table as follows:
new_appstatus | datechanged
... with all the entries for UID=001. All well and good... but:
I wish to calculate the number of days between each entry of this output table, and display it in another column - the output might be as follows:
new_appstatus | datechanged | days_from_previous
1 | 2008-01-01 10:00:00 | -
2 | 2008-01-05 10:00:00 | 4
3 | 2008-01-12 10:00:00 | 7
4 | 2008-01-13 10:00:00 | 1
This is easily done if I used DATEDIFF(datechanged, previous_record_datechanged), but there is no column "previous_record_datechanged" - I'm trying to compare a value from another row, rather than a column. Generating a 2nd column seems redundant, given that the data is then duplicated...
In other words, for every log table record where UID=001 (in this example), the query needs to compare the current "datechanged" value with the previous log table record's "datechanged" value, and calculate the difference to put into "days_from_previous".
I assume this can be done with a self-join... but I am a bit stuck in terms of how! Also, I need to be able to restrict the query to a single UID - it cannot spit out the values for every UID in the table. This kind of multi-clause self-join is a bit beyond me at the moment.
Any help is gratefully appreciated - thank you for your time.