# Thread: Sum by date range

1. Registered User
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. King of Understatement
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?

3. Registered User
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. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
then use columns not variables.

5. Registered User
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
•