Solved

Date Differences SSRS

Posted on 2016-10-17
3
102 Views
Last Modified: 2016-10-19
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
Comment
Question by:DJ P
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 41847768
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
 

Author Comment

by:DJ P
ID: 41848141
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 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
When using a search centre, I'm going to show you how to configure Sharepoint's search to only return results from the current site collection. Very useful when using Office 365 with multiple site collections.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question