Dear Dude:
I take it that the value that is in either the In or Out column
identifies some person or activity that has occurred. I'll call this
the "entity".
A better structure for your table might make the problem simpler.
I'll introduce this because I'm hoping this may make the problem
easier to understand and solve. Consider this table:
Id Entity InOrOut Hours
1 15 In 100
2 34 In 50
3 15 Out 112
4 21 In 10
5 34 Out 55
Now from this, if you simply SUM the Hours column over a GROUP BY the
Entity, and within that sum, multiply by -1 when the InOrOut column is
'In' then you will get something close to what you want:
SELECT Entity,
SUM(Hours * IIf(InOrOut = "In", -1, 1)) AS CumulativeTime
FROM YourTable
GROUP BY Entity
This would have a problem, however, if there is one more In row than
Out row, probably indicating the Entity is still "clocked in" at the
time the query is run.
Two routes may be possible at this point. You may wish to ignore the
"current session" for that Entity, or you may want to calculate the
elapsed time for it up to the current time.
In either case, you must be able to determine:
- for each entity, is there an "excess" In row?
- which of the In rows for the Entity is the "excess"?
The first would be determined by a pair of subqueries that COUNT() the
rows for the entity that are In and that are Out. Compare them to see
if there is one more In than Out.
The second might be accomplished by finding the row for that entity
that is the MAX(Hours) assuming this value is constantly increasing
over the lifetime of the Entity. Using the MAX(Id) to do this could
have some drawbacks.
Up to this point, I've done enough speculating about what's happening
that I don't really want to proceed on guesswork past this point.
The data you have can be transformed into the form I suggested using a
Normalizing Union query:
SELECT Id, In AS Entity, 'In' AS InOrOut, Hours
FROM ExistingTable
WHERE In IS NOT NULL
UNION
SELECT Id, Out AS Entity, 'Out' AS InOrOut, Hours
FROM ExistingTable
WHERE Out IS NOT NULL
Have a table as so
Id In Out Hours
1 15 100
2 34 50
3 15 112
4 21 10
5 34 55
What i need the query to do is look at the In column match
it to the Out column with the same value and then work out
the differnce in hours that have elapsed
EG: 15 will have the value 12 as 12hrs have elapsed since
the no 15 has go in then out!
The number 15 will have many In and Out entries through
out the table and each In to Out needs it own value of
hours.
I hope i explained this ok
Thanks for all you help in advance
Dude
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
