Solved

Date Differences SSRS

Posted on 2016-10-17
3
67 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
3 Comments
 
LVL 48

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Select - find rows created in last 6 months 4 38
SQL Replication question 9 43
TSQL - How to declare table name 26 31
optimize stored proc 14 22
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

831 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