Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Doesn't that work in Access already?
    George
    Home | Blog

  3. #3
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Should have noticed the plus signs, but I could have sworn convert and coalesce were supported. Apologies!
    George
    Home | Blog

  5. #5
    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?

  6. #6
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    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

  8. #8
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    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; 09-06-07 at 10:10.

  10. #10
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    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...

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens if you leave them as a right?
    George
    Home | Blog

  13. #13
    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

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you sure you have Right(<val>,2) & ":" ?
    George
    Home | Blog

  15. #15
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •