Home > Forum > Forms > DATE DIFF only working days

DATE DIFF only working days
0

Hello,

I am new here and I am studying Webcon yet :)
i would like to ask one question and I need help.

I created the field where I count number of days between two dates. It was easy (with help from this forum :) )
This was very helpful for me.
https://community.webcon.com/forum/thread/469?messageid=469

But I need counting only working days and I am really desperate.

In System settings => Global parameters => Working days calendar I defined non-working days for 2024 and 2025.
I have version Webcon Designer 2022

Thank you.
Janka

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

In reply to: Jana K.

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.

In reply to: Jana K.

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

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