Finding differences between (date) rows in a single column

Dear experts,

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.

Sunil
LVL 1
SunilDVRAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
let me give this a try myself:
SELECT l.new_appstatus
, l.datechanged
, p.datechanged previous_date
, datediff(l.datechanged, p.datechanged) change_date_diff
FROM log l
LEFT JOIN log p
  ON l.UID = p.UID
 AND p.datechanged = ( SELECT MAX(i.datechanged)
                         FROM log i
                        WHERE i.UID = l.UID
                          AND i.datechanged < l.datechanged
                      )
WHERE l.UID = 1;

Open in new window

0
 
adrpoCommented:

You could go like this....

Cheers,
za-k/

SELECT
l.new_appstatus, l.datechanged, 
(SELECT DATE_DIFF(l.datechanged, l2.datechanged) 
 FROM log l2 
 WHERE l2.new_appstatus = l.new_appstatus - 1 AND l2.UID=l.UID)
FROM log l
WHERE l.UID = filter;

Open in new window

0
 
adrpoCommented:

Aaa, you want to also give a name to that new field...
And also you want to have a 0 for the first row in there...
Here we go again...

Cheers,
za-k/
SELECT
l.new_appstatus, l.datechanged, 
(SELECT DATE_DIFF(l.datechanged, l2.datechanged) 
 FROM log l2 
 WHERE 
((l2.new_appstatus = l.new_appstatus - 1) OR
 ((l2.new_appstatus = l.new_appstatus) and l.new_appstatus = 1)
  AND l2.UID=l.UID)
 as days_from_previous
FROM log l
WHERE l.UID = filter;

Open in new window

0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
SunilDVRAuthor Commented:
Hi za-k/

I tried your second example. There appears to be a missing closing-bracket ")" from the end of line 8, so I added this; also DATE_DIFF() should be DATEDIFF(). Before changing these, the query halted at these errors.

Having changed these, the query returns the error

#1242 - Subquery returns more than 1 row

Presumably referring to the subquery beginning "SELECT DATEDIFF( "

I'm afraid my SQL debugging capabilities aren't good enough to see what's wrong in this case... any ideas?

Thanks

Sunil
0
 
adrpoCommented:

Strange.... if you restrict over l2.UID = l.UID it should restrict actually on
l2.UID = filter from the outer where clause, but it might not be the case
and that's why you get multiple results in the subquery.
You might try pushing the filter inside the subquery like this....

SELECT
l.new_appstatus, l.datechanged, 
(SELECT DATE_DIFF(l.datechanged, l2.datechanged) 
 FROM log l2 
 WHERE 
((l2.new_appstatus = l.new_appstatus - 1) OR
 ((l2.new_appstatus = l.new_appstatus) and l.new_appstatus = 1)
  AND l2.UID=filter))
 as days_from_previous
FROM log l
WHERE l.UID = filter;

Open in new window

0
 
adrpoCommented:

Ok, after some tests here it is....
This one will give you NULL for the first row for field days_from_previous, but it will work..
It seems there was some problem with OR and so on, I'll test some more...
SELECT
l.new_appstatus, l.datechanged,
(SELECT DATEDIFF(l.datechanged, l2.datechanged)
 FROM log l2
 WHERE
((l2.new_appstatus = l.new_appstatus - 1)
  AND l2.UID=l.UID))
 as days_from_previous
FROM log l
WHERE l.UID = 1;

Open in new window

0
 
adrpoCommented:

Here is the last version. It will return 0 now at the first row for field days_from_previous.
Seems I had problems at first with the parantheses. They are important as you either
restrict for l2.new_appstatus = l.new_appstatus - 1 OR you make an AND between
l2.new_appstatus = l.new_appstatus  and l_new_appstatus = 1; to handle the
case where new_appstatus = 1.

Cheers,
za-k/


SELECT
l.new_appstatus, l.datechanged,
(SELECT DATEDIFF(l.datechanged, l2.datechanged)
 FROM log l2
 WHERE
  ((l2.new_appstatus = l.new_appstatus - 1) OR
   (l2.new_appstatus = l.new_appstatus AND l.new_appstatus=1))
  AND l2.UID=l.UID)
 as days_from_previous
FROM log l
WHERE l.UID = 1;

Open in new window

0
 
SunilDVRAuthor Commented:
Hi za-k,

Not working properly yet - however, I think I may be able to shed some light on why it is failing.

If I use "444444444" as the ID (where there is only one entry which refers to this ID), the query runs successfully and generates:

new_appstatus | datechanged | days_from_previous
3 | 2008-01-31 15:33:28 | NULL

Which is correct, for an ID which has only one record in this table.

If I use "999999999", there are 2 entries with this ID, and I get:

new_appstatus | datechanged | days_from_previous
2 | 2008-01-31 15:33:17 | NULL
6 | 2008-02-05 11:35:23 | NULL

Which is... kind of correct (correct order, but NULL in the second row when it should be "6" days)

If I use ID = 030007357 (there are 8 entries which correspond to this) I'm afraid I'm still getting the same result with latest attempts:

"#1242 - Subquery returns more than 1 row"

The 8 records which reference 030007357 have the new_appstatus values (in date order) 1,2,3,4,5,6,3,4. In other words, they represent someone going through the status', but then backtracking to an earlier status.

Perhaps it's a hunch, but: I deleted the last 2 entries, so that there were only 6 records referencing 030007357, in date order with the new_appstatus values 1,2,3,4,5,6 - this query runs successfully, though the output is not quite correct: again, it displays NULL for every row in the calculated column.

So... a bit of trial and error later:

1) The "finding the difference between the date" code seems to return NULL all the time.

2)The query appears to fail where there are 2 entries with the same app_status. I'm trying to have the application work in such a way that it doesn't matter how "illogical" something might be (there are only 6 status', but someone might go 1,2,3,4,5,6,2,5,4,6 ... ), it will still generate a list by date order, with the time between each change, and the status that it changed to each time.

Any ideas?

Thanks for your time - I've increased this to 500 points, as it seems more difficult than I had anticipated.

Sunil
0
 
SunilDVRAuthor Commented:
- Sorry -

Where I wrote "Which is... kind of correct (correct order, but NULL in the second row when it should be "6" days)" should read "Which is... kind of correct (correct order, but NULL in the second row when it should be "5" days)
0
 
adrpoCommented:

Ok. Why don't you say so?
I assumed that new_appstatus was ALWAYS starting from 1
and was increasing by 1 for each increasing date, all for the same UID.

Of course, if you try to apply my query to data that has your layout
it would not work. You should have given this latest example instead of the
one you gave in the question:
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 completly misled me that new_appstatus is increasing by 1, starting at 1.

Well, if you want to do this for the latest set of data, then things are a bit more tricky:
new_appstatus | datechanged | days_from_previous
2 | 2008-01-31 15:33:17 | NULL
6 | 2008-02-05 11:35:23 | NULL

This is 1,2,3,4,5,6,3,4 the most problematic, ast there is no ORDER between
new_appstatus.

I have to think a bit about this new challenge. One idea would be to just ignore
the new_appstatus, order everything with the same UID by date, then go from maxdate to mindate and just cascade the date_diff. I'll give it a try this way and
let you know how it goes.

Cheers,
za-k/




 
0
 
adrpoCommented:

1,2,3,4,5,6,3,4
Ok, let's try again. I'm not sure min(date) will work, but it should!

This way you don't care about any new_appstatus, you always
take the min date with the same UID and make the diff with
the current record.

Cheers,
za-k/

SELECT
l.new_appstatus, l.datechanged,
(SELECT DATEDIFF(l.datechanged, min(l2.datechanged))
 FROM log l2
 WHERE l2.UID=l.UID)
 as days_from_previous
FROM log l
WHERE l.UID = 1;

Open in new window

0
 
adrpoCommented:

Hi again,

Ok, i tested the stuff and it should work!

Cheers,
za-k/
0
 
SunilDVRAuthor Commented:
Hi za-k/

Firstly, my apologies for not getting back sooner - I've been ill, and not doing any programming at all.

Secondly: the latest code you posted again sort of worked in some situations, but not all:

I attach  the output from record id 999999999 (I added "ORDER BY datechanged", as a couple were in the wrong order).

From first to the second row works great - it's a five day gap, and days_from_previous displays 5.

However, the remaining rows have days_from_previous displaying the wrong value. What I have noticed, though, is that the number shown *is* relevant - it's the number of days from the FIRST entry in the log. Hence the 4 entries on 7th February all show "7" and are 7 days from the first entry; the two I've just entered today (23rd Feb) are show "23" and are 23 days from the first entry.

So... I think it's almost there.

Thanks for your time, and apologies again for the delay.

Sunil
new_appstatus 	datechanged 	days_from_previous
2 	2008-01-31 15:33:17 	0
6 	2008-02-05 11:35:23 	5
1 	2008-02-07 13:20:14 	7
3 	2008-02-07 13:20:18 	7
5 	2008-02-07 13:20:21 	7
2 	2008-02-07 13:20:25 	7
5 	2008-02-23 16:37:40 	23
6 	2008-02-23 16:37:44 	23

Open in new window

0
 
SunilDVRAuthor Commented:
I am hesitant to "bump" this up, but this question is still outstanding and it has been over 5 weeks since my previous post and there have been no other answers.

Can anyone help to finish this off, please?

Thank you,

Sunil
0
 
SunilDVRAuthor Commented:
Hi angelll,

I would still love to have an answer for this query, but I was very hesitant to "bump" it.

If anyone can help me out, I'd very much appreciate it.

Thanks

Sunil
0
 
SunilDVRAuthor Commented:
Dear angelll,

Your solution works excellently - thank you very much. I juse added an

ORDER BY datechanged ASC;

to the end to display by date.

I hope others find this useful as well.

Cheers,

Sunil
0
All Courses

From novice to tech pro — start learning today.