We help IT Professionals succeed at work.

SSRS Syntax: sum if today() - hire_date >= 90 days

dplowman
dplowman asked
on
I am trying to create an avereage OPH excluding associates that have not been hired for less than 90 days.  The code below looks at current date minus hired date < 90 days then 0, else sum(Fields!PDL_Originated.Value+Fields!ILP_Originated.Value+Fields!PDL_Renewals.Value+Fields!ILP_Renewals.Value)/sum(Fields!hoursworked.Value). On the 90th day the associates originated values/hours worked should be included in the average, but only for the days > 90 days since hire date. Any ideas?
=iif(datediff("dd", Fields!date.Value, Fields!hire_date.Value) < 90, 0, sum(Fields!PDL_Originated.Value+Fields!ILP_Originated.Value+Fields!PDL_Renewals.Value+Fields!ILP_Renewals.Value)/sum(Fields!hoursworked.Value)

Open in new window

Comment
Watch Question

Author

Commented:
So I figured out part of the problem. To count originations I can use:

=sum(iif(datediff("d",Fields!hire_date.Value,Fields!date.Value) >= 90, (Fields!PDL_Originated.Value+Fields!ILP_Originated.Value+Fields!PDL_Renewals.Value+Fields!ILP_Renewals.Value),0))

but for hours worked this is not working????

=sum(iif(datediff("d",Fields!hire_date.Value,Fields!date.Value) >= 90, Fields!hoursworked.Value,0))

Author

Commented:
This works!


=(sum(iif(datediff("d",Fields!hire_date.Value,Fields!date.Value) >= 90,
(Fields!PDL_Originated.Value+Fields!ILP_Originated.Value+Fields!PDL_Renewals.Value+Fields!ILP_Renewals.Value),0)))/
(sum(iif(datediff("d",Fields!hire_date.Value,Fields!date.Value) >= 90, cdbl(Fields!hoursworked.Value), 0)))
BI Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Doesn't that cause a "division by zero" error in certain cases?

Try the one in the code snippet.
(you may need to add sum() around the whole expression, bit difficult to tell without more info)
=iif(
	datediff("d", Fields!hire_date.Value, Fields!date.Value) >= 90,
	(Fields!PDL_Originated.Value + Fields!ILP_Originated.Value + Fields!PDL_Renewals.Value + Fields!ILP_Renewals.Value) / cdbl(Fields!hoursworked.Value)
	, 0
)

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.