How to filter out days in a date range

[niyaz006]

Hi,

I have a list of tasks and their start and end date in an index. And list of holidays in a lookup file. I want to find the difference between start and end date. Also this should exclude the day if there are any holidays in between start and end date.

Task	Start	End	Days
A	5-Jan-20	5-Feb-20
B	15-Jan-20	5-Mar-20
C	1-Apr-20	30-Apr-20

Holidays:
1-Jan-20
27-Jan-20
10-Apr-20
11-Apr-20
12-Apr-20
13-Apr-20
....

Am currently during this using appendcols and mv commands which is making it very slow. Am looking for a direct/easier/perf optimized  solution

| appendcols
[ inputlookup Holidays.csv
| tableHolidays ]
| reverse
| eventstats list(Holidays) as Holidays delim="|"
| nomv Holidays
| eval between= mvrange(start, end, "1d"), date_diff = strftime(between,"%d/%m/%y %H:%M:%S")
| mvexpand date_diff
| where NOT match(date_diff, Holidays)
| stats list(date_diff) AS date_diff BY tasks

[to4kawa]

sample:

| makeresults
| eval _raw="Task	Start	End	Days
A	5-Jan-20	5-Feb-20	 
B	15-Jan-20	5-Mar-20	 
C	1-Apr-20	30-Apr-20"
| multikv forceheader=1
| table Task	Start	End	Days
| eval Start_epoch=strptime(Start,"%d-%B-%y"), End_epoch=strptime(End,"%d-%B-%y")
| eval date_range=mvrange(Start_epoch,End_epoch,60*60*24)
| appendcols [|makeresults
| eval _raw="Holidays
1-Jan-20
27-Jan-20
10-Apr-20
11-Apr-20
12-Apr-20
13-Apr-20"
| multikv forceheader=1 
| eval query=strptime(Holidays,"%d-%B-%y")
| stats values(query) as Holidays ]
| filldown Holidays
| stats values(Task) as Task values(Holidays) as Holidays by date_range
| where !match(Holidays,date_range)
| stats count by Task

Your query is not full. so I can't make the query.