Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 129
  • Last Modified:

Date Differences SSRS

Good afternoon. I am just starting to play around with SSRS. I have a report I created from a SharePoint list (if that matters). I have two date fields once called created and the other called completion_date. With in the report I went into the text box properties for both and formatted the date to read m/d/yyyy on both fields. Basically I removed the time.

I then created a text box called NumberofBusinessDays. It is in this field that I want to return a value not counting weekends on the difference between the field created and the field completion_date. So for example:

Created Date = 10/17/2016 Completion Date = 10/20/2016 then NumberofBusinessDays = 3.

How do I accomplish this for a newbie?
0
DJ P
Asked:
DJ P
1 Solution
 
PortletPaulCommented:
Not counting weekends sounds simple enough, but it does complicate the calculation. Here is a way using a case expression:

SELECT
   ID
,     (DATEDIFF(dd, [Created Date], [Completion Date]) + 1)
    - (DATEDIFF(wk, [Created Date], [Completion Date]) * 2)
    - (CASE WHEN DATENAME(dw, [Created Date]) = 'Sunday' THEN 1 ELSE 0 END)
    - (CASE WHEN DATENAME(dw, [Completion Date]) = 'Saturday' THEN 1 ELSE 0 END)
  as NumberofBusinessDays 

  , <<other fields >>
from your_table

Open in new window

It is also possible to avoid using strings for Saturday/Sunday if that is important
0
 
DJ PAuthor Commented:
Thanks. Keep in mind I am new to this and what you provided does not work. I'm not sure if this matters but I am looking at this data via a data set from sharepoint. I am reading that the way you set things out of report writer via a sharepoint list is different than traditional sql but again I'm not sure on that since my experience is lacking. I can't even get a simple date parameter to work but that's another story. Would I be correct on this or should what you provided work? If this is the case than I am missing something.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now