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