DATE DIFF only working days

0

Hi,
you can subtract the "number of days between two dates" calculated using DATE DIFF, from the number of days off calculated with the query below:

SELECT count(WODC_ID) as numberOfDays FROM WorkingDaysConfigurations
WHERE WODC_Date >= start_data
AND WODC_Date <= end_data

Of course, you have to insert your dates for start_data and end_data

Make a business rule with SQL query and you will get the number of days minus days off

I need that it doesn't count neither weekend days (Saturday and Sunday) and dates will be entered when filling in the form.

This solution is not enough for me.

Thanks
J

Hi,
here you have something that might help you: https://stackoverflow.com/questions/34794713/sql-datediff-without-weekends-and-public-holidays

You need to make a form rule for changing the value (connect to both dates) and in it use a business rule that calculates the number of days.

You can use dbo.Calendars table. There is [CAL_IsWorkingDay] column which shows workin/nonworking days. Then add two fields on your form to input date range, and 'sql row' or 'sql grid' attribute to calculate number of dates. Date attributes should be put ias string value in ' '. Use SQLs below. Other solution is to put the SQL command in a 'SET' javascript action, to change value of a certain field (text or numeric) triggered by a change in both date fileds, so that you can save the result.
.
SELECT [CAL_ID]
,[CAL_WorkingDate]
,[CAL_IsWorkingDay]
FROM [dbo].[Calendars]
where CAL_WorkingDate between '2019-01-01' and '2019-01-08'

SELECT count(*) as number
FROM [dbo].[Calendars]
where CAL_WorkingDate between '2019-01-01' and '2019-01-08'
and CAL_IsWorkingDay=1