This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Course Of The Month
5 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Date Differences SSRS
Posted on 2016-10-17
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?
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?
[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
Active today
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
Active today
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| rolling count by date, hour query | 7 | 30 | |
| SQLCMD question to have fields created as fixed length | 2 | 36 | |
| SQL Query | 9 | 27 | |
| Need split for SQL data | 7 | 45 |
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month5 days, 20 hours left to enroll
734 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.