Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    8

    Question Unanswered: DateTime Grand Total Calculation

    Okay, I've looked around on various areas on ways to do this, and am still stumped. So, I'll just pose the question outright here.

    I'm building a database that tracks the the volunteers hours for people. I need to be able to show the current totals of hours worked for those people, by person.

    I've got to the point of being able to calculate the total of the specific entry by "h" and "m" using the DateDiff. Also, I've been able to setup a UNION query to show a total at the bottom of hours or minutes, whichever I choose.

    The problem is this, how would I calculate total hours and minutes worked specific by person, say, on a report? Also, show the grand total hours worked by all persons? I don't need to see the shifts worked necessarily, just the total hours calculated for all shifts. If there is a current shift working, with no end date (possibly using the nz function) calculate based on current time.

    So, to make a long story longer, attempting to show calculated totals with a grand total on a report. Oh, and to sort by most hours / minutes worked with most being at the top.

    Any takers?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    nz() should handle currently running tasks as you suggest.

    you make your life more difficult by formatting into hh:nn too early
    consider:
    SELECT person, sum(nz(outDateTime,now())-inDateTime) AS worked
    FROM tblWhatever
    GROUP BY person
    ORDER BY sum(nz(outDateTime,now())-inDateTime) DESC;

    at some later stage (? report) you can format the decimal-days sum into hh:mm or whatever pleases you.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by izyrider
    nz() should handle currently running tasks as you suggest.

    you make your life more difficult by formatting into hh:nn too early
    consider:
    SELECT person, sum(nz(outDateTime,now())-inDateTime) AS worked
    FROM tblWhatever
    GROUP BY person
    ORDER BY sum(nz(outDateTime,now())-inDateTime) DESC;

    at some later stage (? report) you can format the decimal-days sum into hh:mm or whatever pleases you.

    izy
    Thanks for the info, I'll give it a try. I've had pretty good luck so far with figuring out total time worked. I'm running into problems now with formatting the data to hh:nn. It gives an error, unless I use just "n". I can get total minutes then, and wonder if at that point it would be best to just divide by 60 to get hours. At any rate, thanks for the asistance.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what data type is the variable that you are havign the problem with?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it gives an error ??
    or it gives an unanticipated result?

    the sum i proposed should end up with e.g. 10.255 decimal-days
    = ten days and a little bit more than a quarter day
    = 246hours 7minutes
    so hh:nn will extract 06:07 and ignore the 240 hours from 10 complete days ...not helpful

    have a go with something like
    int(YourSum * 24) & ":" & format$(((YourSum *24) - int(YourSum * 24))*60, "00")
    which should deliver 246:07

    izy
    currently using SS 2008R2

Posting Permissions

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