Results 1 to 15 of 21
Thread: accumulated time query

090307, 17:19 #1Registered User
 Join Date
 Mar 2004
 Posts
 162
Unanswered: accumulated time query
is this query convertible from SQL to ms access? it gives me accumulated time from int values
CONVERT(varchar, SUM(COALESCE (s2.DykTid, 0)) / 60) + ':' + RIGHT('00' + CAST(SUM(COALESCE (s2.DykTid, 0)) % 60 AS varchar(2)), 2) AS AccumulatedTime
i was given help make thι query in this thread (www.dbforums.com/showthread.php?t=1621731) and now i need it to work in access.
thx in advance
//Mr

090407, 04:53 #2www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,434
Provided Answers: 10

090407, 05:28 #3SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
CONVERT and CAST both translate to STR, COALESCE converts to ISNULL, the plus sign for concatenation converts to ampersand, and the modulo operator % converts to MOD
so, george, no it doesn't

090407, 06:05 #4www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,434
Provided Answers: 10

090407, 06:32 #5Registered User
 Join Date
 Mar 2004
 Posts
 162
now i can't make that MOD to work im trying this:
SELECT s1.LogNr, s1.DykTid, STR(CHAR, SUM(ISNULL(s2.DykTid, 0)) / 60) & ':' & RIGHT('00' & STR(SUM(ISNULL(s2.DykTid, 0)) MOD 60 AS CHAR(2)), 2) AS AccumulatedTime, s1.LoggID
I changed the VARCHAR to CHAR since that dosen't seems to be supported in access or am i wrong?

090407, 09:04 #6SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
mrpcguy, you cannot just substitute the access function name for the sql server function name, you need also to accommodate the differences in the syntax
so for example CONVERT(datetype,expression) does not automatically become STR(datetype,expression)

090507, 04:26 #7Registered User
 Join Date
 Mar 2004
 Posts
 162
Everything works fine except that MOD part of the query, i just can't figure that one out.
got this so far... and its not working at all, just gives me wrong number
Str(Sum(Str(s2.DykTid)) /60) & ":" & Right('00' & Str(Sum(Str(s2.DykTid))),2) MOD 60 AS AccumulatedTime

090507, 07:11 #8SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
let's examine this: Str(Sum(Str(s2.DykTid)) /60)
functions, when nested, are executed from the inside out
so, first thing, we have Str(s2.DykTid), which converts a column value to a string
the next thing that happens is Sum(Str(s2.DykTid))  you're summing up all these strings, and that's not quite right, because SUM is supposed to operate on numbers, not strings
luckily for you, access is smart enough to convert your carefully crafted string back to numbers first
finally after dividing the sum by 60, you are converting it back to a string
the same type of problem occurs in the other part of the formula
try this and see if it works:
Str(Sum(s2.DykTid) /60) & ":" & Right('00' & Str(Sum(s2.DykTid) MOD 60),2) AS AccumulatedTime

090607, 10:07 #9Registered User
 Join Date
 Mar 2004
 Posts
 162
thx alot for help
this Str(Sum(s2.DykTid) /60) & ":" & Right('00' & Str(Sum(s2.DykTid) MOD 60),2) AS AccumulatedTime
gives me this:
9.01666666666667: 1
9.86666666666667:52
10.7833333333333:47
11.5333333333333:32
so now i use this:
left(Sum(s2.DykTid)/60,2) & ":" & Sum(s2.DykTid) Mod 60 AS AccumulatedTime, s1.LogNr, s1.DykTid, s1.LoggID
and it kinda gives me what i want except for a few things , the output looks like this:
9,:1
9,:52
10:47
11:32
because i use left i have the "," in shorter ´than 2 digits, the solution would be to calculate where "," is and then cut in correct place.
The other thing that i don't have any idea how to solve is the 9:1 output, it should be a zero in front of numbers lower then 10 so it should say 9:01 (9hours and 1min)Last edited by mrpcguy; 090607 at 10:10.

090607, 10:11 #10SQL Consultant
 Join Date
 Apr 2002
 Location
 Toronto, Canada
 Posts
 20,002
right('0'&Sum(s2.DykTid)/60,2) & ":" &
right('0'&Sum(s2.DykTid) Mod 60,2) AS AccumulatedTime

090607, 10:20 #11Registered User
 Join Date
 Mar 2004
 Posts
 162
i change your right to a left and now everything looks much better but now hour 10 become 01 and hour 20 equals 02 and so on...

090607, 10:25 #12www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,434
Provided Answers: 10

090607, 10:28 #13Registered User
 Join Date
 Mar 2004
 Posts
 162
sry im only change the first right to left
with both right i get this
0:0
333:26
667:46
667:16
1,6:36
667:01
667:16
333:35
333:26
,25:15
333:35
,25:15
333:53
6,5:30
667:10
667:55
667:40
667:01
667:52

090607, 11:05 #14www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,434
Provided Answers: 10

090607, 11:23 #15Registered User
 Join Date
 Mar 2004
 Posts
 162
This is what i use and what i get
right('0'&Sum(s2.DykTid)/60,2) & ":" & right('0'&Sum(s2.DykTid) Mod 60,2) AS Accu1,
left('0' & Sum(s2.DykTid)/60,2) & ":" & right('0'&Sum(s2.DykTid) Mod 60,2) AS Accu2,
(Sum(s2.DykTid)/60) & ":" & (Sum(s2.DykTid) Mod 60) AS Accu3
Accu1 Accu2 Accu3
67:01 09:01 9,01666666666667:1
67:52 09:52 9,86666666666667:52
33:47 01:47 10,7833333333333:47
33:32 01:32 11,5333333333333:32
i used 3 instead of 2 in in earlier post
this is what i want
09:01
09:52
10:47
11:32
and it should also work on 3 digits hour like 100:30