CODEWITHSUNDEEP
sqlreporting-servicesssrs-2012reportbuilder3.0
Mahmoud Salah
Mahmoud Salah

Reputation: 45

SSRS count working days only

I need some help in this my case is

1-two parameters date from , date to

2-number of team parameter that manually enter by user for later on use in some calculation

rquirement

count only working days (6days per week ) without Friday based on filtered period (date from and date to)

Code

=(COUNT(IIF(Fields!Job_Status.Value="Closed",1,Nothing))) / 
((DateDiff(DateInterval.day,Parameters!DateFrom.Value,Parameters!ToDate.Value
)) * (Parameters!Number_of_teams.Value))

Note

this code is working fine but it calculate all days

thanks in advance

Upvotes: 4

Views: 2656

Answers (3)

Muhammad Yaseen
Muhammad Yaseen

Reputation: 741

Try below expression. I have tested for monthly basis to calculate working days in a month, which excludes Saturday and Sunday from calendar days:

DateDiff("d",Parameters!StartDate.Value,Parameters!EndDate.Value)) - Round( (((DateDiff (DateInterval.Day, DateAdd(DateInterval.Day,7-WeekDay(Parameters!StartDate.Value),Parameters!StartDate.Value), DateAdd(DateInterval.Day,7-WeekDay(Parameters!EndDate.Value),DateAdd("d",-1,Parameters!EndDate.Value).AddDays(1)) + 1)/ 7)*2 ) + iif(weekday(Parameters!EndDate.Value)=7,1,0) + iif(weekday(Parameters!StartDate.Value)=1,1,0) -1 , 0 )

Upvotes: 0

DatumPoint
DatumPoint

Reputation: 429

The most sustainable solution for this kind of question, in the long term, is to create a "date dimension" aka "calendar table". That way any quirks in the classification of dates that don't conform to some neat mathematical pattern can be accommodated. If your government decides to declare date X a public holiday starting from next year, just add it to your public holidays column (attribute). If you want to group by say "work days, weekends, and public holidays" no need to reinvent the wheel, just add that classification to the calendar table and everyone has the benefit of it and you don't need to worry about inconsistency in calculation/classification. You might want the first or last working day of the month. Easy, filter by that column in the calendar table.

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

Try this:

=(DATEDIFF(DateInterval.Day, CDATE("2016-02-14"), CDATE("2016-02-17")) + 1)
-(DATEDIFF(DateInterval.WeekOfYear, CDATE("2016-02-14"), CDATE("2016-02-17")) * 2)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,CDATE("2016-02-14"),FirstDayOfWeek.System))="sunday",1,0)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,CDATE("2016-02-17"),FirstDayOfWeek.System))="saturday",1,0)
))

It will ruturn count of monday to friday between the given range in the above case it returns 3. For StartDate = 2016-02-14 and EndDate = 2016-02-21 it returns 5.

UPDATE: Expression to exclude friday from the count.

=(DATEDIFF(DateInterval.Day, Parameters!DateFrom.Value, Parameters!ToDate.Value) + 1)
-(DATEDIFF(DateInterval.WeekOfYear, Parameters!DateFrom.Value, Parameters!ToDate.Value) * 1)
-(IIF(WeekdayName(DatePart(DateInterval.Weekday,Parameters!ToDate.Value,FirstDayOfWeek.System))="friday",1,0))

Tested with:

 DateFrom     ToDate     Result
2016-02-12  2016-02-19     6
2016-02-12  2016-02-18     6
2016-02-12  2016-02-15     3

It is very strange to me see a saturday and sunday as working days instead of friday.

Let me know if this helps you.

Upvotes: 2

Related Questions