# Thread: Check for non working days

1. Member
Join Date
Jul 2008
Posts
72

## Check for non working days

Hello,

It looks simple but I can't get it solved.

My problem is that I have rows with transaction data including a Start and End day. Now I want to check them against a weekend/holiday table to identify how many non-working days are in the period.

Transactional table:

START ; END ; Name
20090821 ; 20090824 ; R1

Non-working day table:
NOWORKDAY
20090822
20090823

Result should look like:
START ; END ; Name ; NonWorking ; Working
20090821 ; 20090824 ; R1 ; 2; 2

I tried it with a cartesian join but failed. Thought this is a common problem but couldn't find anything.

Anyone an idea how to solve this?

Thanks
Mark
Last edited by markbe; 08-26-2009 at 06:55 AM.

2. Member
Join Date
Nov 2008
Posts
52

## Loop via javascript

i was in a simular situation, just with a number range.
i have rows with ranges and need to lookup each number in the range.

i solved it with using a loop in a javascript step which creates new rows,
this could be working for you too:

Javascript-Step:
- create a loop in javascript step for each day with a new column for the day
- then create a new row in the loop
- don't forward the orignal input-row after the loop

then a Lookup-Step
- lookup the day, return 1 when found, otherwise 0, as non-workday-flag

then a calculator step
- workday-flag = 1 - non-workday-flag

then a grouping step
- group by the original key and set sum(workday-flag), sum(non-workday-flag) as group results

if you don't have a key for grouping, add one artificial key via "add sequence" step and use that one

i hope it helps :-)

3. Member
Join Date
Jul 2008
Posts
72
Thank you very much, I think technically this will work. But I think I have to find an other solution unfortunately.

My table with the data is 140.000 rows the date difference in a row is from1 day (good) to 7 month (very bad) some extreme with 3 years.
The table with the dates to compare with is 800 rows.

Looping the rows will burn my notebook I guess ;-)

Looks like this is not as simple as I thought at the beginning.

4. Senior Member
Join Date
Sep 2007
Posts
834
Here:
you have a ktr that can be a starting point for your ktr

After the filter, add a DB Lookup against your NOWORKDAY table to set a workday-flag. Default: workday, if found: non_workday.

Then change the grouping accordingly to your requirements,
mc

5. Junior Member
Join Date
Feb 2009
Posts
20
Earlier, I wrote a Javascript to determine how many weekends there are in a certain period. Perhaps you can adapt the script to suit your need.

Nr_of_days and startdate is input.

PHP Code:
``` var saturday;var sunday;var counter;var nr_of_weekends;counter = 0;saturday = 0;sunday = 0;nr_of_weekends = 0; while (counter < nr_of_days) {        if (getDayNumber(dateAdd(startdate, "d", counter),"w") == 7) { saturday = 1; }        if (getDayNumber(dateAdd(startdate, "d", counter),"w") == 1) { sunday = 1; }        if (saturday == 1 && sunday == 1) {            nr_of_weekends = nr_of_weekends + 1;            saturday = 0;            sunday = 0;        }        counter = counter + 1    }  ```
P.S. If somebody knows a more efficient way, I'm always interested in learning!
Last edited by rickonline; 08-27-2009 at 06:11 AM.

6. Member
Join Date
Jul 2008
Posts
72
Thanks! I will wok on this as it will greatly reduce the number of lookups.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•