1

I'm trying to convert the below Excel formula into SSRS but having looked around I cannot seem to find a solution. I can calculate the number of working days between two dates but what I'm trying to do is add on a number of working days to a date. Essentially I don't have the 2nd date. I guess it would be something along the lines of the DATEADD function?

=WORKDAY($A1,B$1)

Hope someone can help Many thanks

Baseline9
  • 101
  • 1
  • 3
  • 12

3 Answers3

2

Here is a tsql solution to add X Business Days to a date.

declare @calendar as table (theDate date, dayOfWeek varchar (10));
declare @startDate as date = '20170704';
declare @businessDaysToAdd as integer = 10;

insert into @calendar
select theDate
, datename(dw, theDate) dow
from 
dbo.dateTable('20170701', '20170720') ;

with temp as (
select theDate
, dayOfWeek
, rank() over (order by theDate) theRank

from @calendar
where theDate > @startDate
and dayOfWeek not in ('Saturday', 'Sunday')
)
select * from temp
where theRank = @businessDaysToAdd;

Notes

dbo.DateTable is a table valued function that just happens to exist in the database I was using. In real life, you might have an actual calendar table of some sort.

This example does not include holidays.

This is only the start of the answer to the posted question. It only solves the problem of Essentially I don't have the 2nd date.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • 1
    Brill thanks. We do have a calendar table and the field "theDate" is exactly what I require. I now need to work out how to pass in start date which will be application date from my data and businessdaystoadd which I have in my data table as well. Do you know of a way to invoke this code in SSRS and pass in those values, I have tried a new dataset and function but could not get that to work. Please note our environment does not allow us to create functions and tables via SQL management studio hence I'm trying to build in SSRS. – Baseline9 Jul 31 '17 at 11:27
  • 1
    Ok doing some more digging I don't think I can so this via the SQL / Data. I'm trying to create a piece of analysis that monitors customer contact rate success. This measures the number of working days between application and when we speak to the customer. I have done it in Excel so Application date down the left and number of working days across the top. What I'm trying to do now is create a leading edge so app date + days >=Today is null. I think the only way to do this in SSRS is adding working days to application date as ReportItems! Hope that makes sense it is difficult to explain! :) – Baseline9 Aug 02 '17 at 16:46
  • 1
    I have no expertise in SSRS. What might work though is to make the sql code a function or stored procedure which you call from SSRS. – Dan Bracuk Aug 02 '17 at 17:28
  • 1
    Thanks Dan. Same thought I had. I'm going to look if I can re create the maths behind the workday function in Excel and then convert it to an expression in SSRS. I will post any solution I find :) – Baseline9 Aug 03 '17 at 10:09
1

Type this into the expression for the textbox. (From SSRS 2008 Datediff for Working Days)

=(DateDiff(DateInterval.day,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)+1)
-(DateDiff(DateInterval.WeekOfYear,Parameters!STARTDATE.Value,Parameters!ENDDATE.Value)*2)
-(iif(Weekday(Parameters!STARTDATE.Value) = 7,1,0)
-(iif(Weekday(Parameters!ENDDATE.Value) = 6,1,0))-1)
Tarzan
  • 4,270
  • 8
  • 50
  • 70
  • 1
    Unfortunately this is not quite what I'm after. This calculates number of working days between two dates. I'm trying to add business days onto a date to get a 2nd date. Hope that makes sense – Baseline9 Aug 02 '17 at 17:03
1

Ok after much perseverance I managed to get what I wanted in both TSQL and SSRS. My objective was to measure Agent productivity so I didn’t want to count the weekend and this would be unfair. If a date fell on a weekend then I wanted it to jump to a Monday. Likewise if adding number of days onto a date went over a weekend in the future then I needed the incremented date to reflect this. For the end user (In SSRS) I wanted a leading edge (Like an Upside down triangle) so that if the date + number working days was in the future then set to NULL, showing a zero would look like no productivity which is incorrect.

First TSQL - My base query started with the following SO thread but after trying many of the options I was finding when the date fell on a Saturday or Sunday the solution did not work for me (I was unable to create functions due to permissions). However tweaking the below got me there and I dealt with Sunday specifically

Add business days to date in SQL without loops

SELECT  
,DATEADD(WEEKDAY, (/*Your Working Days*//5)*7+(/*Your Working Days*/ % 5) + 
(CASE WHEN DATEPART(WEEKDAY,/*Your Date*/) <>7  AND DATEPART(WEEKDAY,/*Your Date*/) + (/*Your Working Days*/ % 5) >5 THEN 2 
      WHEN DATEPART(WEEKDAY,/*Your Date*/) = 7  AND DATEPART(WEEKDAY,/*Your Date*/) + (/*Your Working Days*/ % 5) >5 THEN 1 ELSE 0 END), /*Your Date*/) AS [IncrementedDate]
FROM /*YourTable*/

Then for SSRS - The 2 key points here is that TSQL will divide as an integer if the source number is an integer so this needs to be handled in SSRS and secondly you need to set the first week day to Monday as part of the expression. I put this expression into a Matrix with Date Created being my Row Group and Contact Working Days being my Column Group.

=DATEADD("W",(INT(ReportItems!ContactWorkingDays.Value/5))*7+(ReportItems!ContactWorkingDays.Value MOD 5) + IIF(DATEPART("W",ReportItems!DateCreated.Value,FirstDayOfWeek.Monday) <> 7 AND (DATEPART("W",ReportItems!DateCreated.Value,FirstDayOfWeek.Monday) + (ReportItems!ContactWorkingDays.Value MOD 5) >5),2,IIF(DATEPART("W",ReportItems!DateCreated.Value,FirstDayOfWeek.Monday) =  7 AND (DATEPART("W",ReportItems!DateCreated.Value,FirstDayOfWeek.Monday) + (ReportItems!ContactWorkingDays.Value MOD 5) >5),1,0)),ReportItems!DateCreated.Value)

This does not include holidays - I'm not too bothered at this stage and that is for a rainy day! :)

Baseline9
  • 101
  • 1
  • 3
  • 12