Joseph Bland Joseph Bland - 7 months ago 8
SQL Question

Why does this SQL Query return calculate time wrong?

Basically, this query is suppose to calculate the time in minutes a user is logged onto our application. Its probably something simple, but I just can't grasp it right now. The issue I'm having is that sometimes one or two of our users will have times that make no sense. Such is one user would have 23 minutes one day, and the next much less, when it should be about the same.

Example data:

+----------------+----------+
| ava_modifiedby | Time |
+----------------+----------+
| RSCHIFFELBEIN | 20:22:08 |
| TMORRIS | 13:41:57 |
| TATKINS | 13:23:01 |
| MATTECH1 | 07:59:49 |
| R5 | 07:02:23 |
+----------------+----------+





SELECT DISTINCT
A.ava_modifiedby,
CONVERT(VARCHAR, DATEADD(ss, ((SUM(DATEPART(hh, A.ava_changed) * 3600 + DATEPART(mi, A.ava_changed) * 60 + DATEPART(ss, A.ava_changed)) - SUM(DATEPART(hh, B.ava_changed) * 3600 + DATEPART(mi, B.ava_changed) * 60 + DATEPART(ss, B.ava_changed))) % 86400), 0), 108) AS TIME
FROM R5AUDVALUES A
LEFT OUTER JOIN R5AUDVALUES B ON A.ava_primaryid = B.ava_primaryid
WHERE A.ava_table = 'R5SESSIONS'
AND A.ava_deleted = '+'
AND A.ava_to IS NULL
AND B.ava_table = 'R5SESSIONS'
AND B.ava_inserted = '+'
AND B.ava_from IS NULL
AND A.ava_modifiedby <> '*'
AND B.ava_modifiedby <> '*'
GROUP BY A.ava_modifiedby

Answer

Without seeing your an actual row example from your table, it's hard to say, but I think that something like this would work

SELECT DISTINCT 
A.ava_modifiedby, CONVERT(varchar, DATEADD(ss,
DateDiff(ss, a.ava_changed, b.ava_changed), 0), 108) AS TIME
FROM R5AUDVALUES A
LEFT OUTER JOIN R5AUDVALUES B ON A.ava_primaryid = B.ava_primaryid
WHERE A.ava_table = 'R5SESSIONS'
    AND A.ava_deleted = '+'
    AND A.ava_to IS NULL
    AND B.ava_table = 'R5SESSIONS'
    AND B.ava_inserted = '+'
    AND B.ava_from IS NULL
    AND A.ava_modifiedby <> '*'
    AND B.ava_modifiedby <> '*'
GROUP BY A.ava_modifiedby