Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: Sum by date range

    I am working on a report for staff productivity, and have to get a summary figure for how much productivity was expected for a date range. The problem is that the amount expected from an employee can change if they move from full time to part time etc.

    So I have a view that has the begin date, end date, expected daily production # by employee, and have to figure out how to get the multiplication to work correctly.

    Example:

    Employee 1 had a daily production # of 10 from 1/1/07-3/31/07 and daily production of 5 from 4/1/07 - now

    If I run the production report for 1/1/07 - 6/30/07 what I want is one summary figure of for the entire range which would be 10*Datediff(d,1/1/07,3/31/07)+5*Datediff(4/1/07,6/30/07) or 890+450=1340.

    Of course the actual date range for the report will be a variable, and the dates for the begin and end of a production date range will be all over the place.

    Any quick and easy way to do this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why not just replace the dates you have there with the coumn names from your table? You would probably end up with rows of values to SUM() rather than columns to ADD (+). Also - what about holidays\ weekends?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    in the example posted replacing the dates with column names would only work for some spots:

    if I use the variables @start and @end for the report dates then I would have to use:

    set @start = 1/1/07
    set @end = 6/30/07

    10*DateDiff(d,Begin_Date,End_Date)+5*DateDiff(d,Be gin_Date,@end)

    If the date of the reports was

    set @start = 2/1/07
    set @end = 6/30/07

    then the query would have to be:

    10*DateDiff(d,@start,End_Date)+5*DateDiff(d,Begin_ Date,@end)

    I'm trying to find something that can deal with all of the different possibilities.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    then use columns not variables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Not understanding how that could work as all of my columns fall between the dates selected for the report, and those dates are half of what drives the calculation.

Posting Permissions

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