You can translate the question and the replies:

Calculating working day difference between two dates.

Hi Everyone, I have a requirement to calculate difference in date (Start vs End) while taking off the weekends and holidays. I have one view for the Start and End date details and another view for the Holiday Calendar. How do I calculate the difference in time? I am having a hard time trying to wrap my head around the requirement. I would appreciate any help. Thank you,
user
15-05-2017 09:23:46 -0400

1 Answer

Hi, I would suggest you to create a another table/CSV/Excel with below sample information | Datevalue | Day | Public holiday | | ----------------- | ----------- | -------------------| | 01/01/2010 | Friday | Y | |  02/01/2010 | Saturday | N | |  03/01/2010 | Sunday | N | | ...                | ...        | ... | After having the data about the not working days, you should perform the following steps: 1. Import this table/CSV/Excel into Virtual DataPort Admin tool as a data source and create a base view. 2. Create a JOIN over your view with the start and end date and the base view created in step1. 3. In the where condition you should add the following conditions: * Datevalue between startdate and enddate * Day not in ('Saturday','Sunday') * Public_holiday <> 'Y' This proposed solution will help you figure out the difference between start datetime and end datetime by excluding weekends and public holidays in Virtual DataPort Admin tool. You can also refer to this links on how to create a date dimension table in Oralce and SQL Server: http://www.dwhworld.com/2010/11/date-dimension-sql-scripts-oracle/ https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ I hope this helps you!
Denodo Team
19-05-2017 08:40:18 -0400
You must sign in to add an answer. If you do not have an account, you can register here