In-db - sql - bigfiles - Please help to create rule to fetch data.

[Edu_XGomes]

Hi everyone, can you please help me to think how to solve this question:

There is a file with millions lines but the group of line I have to fetch must follow the follwing rule:

It must be in the last sunday or last saturday or last friday of the month but before the las wednesday of the month.

For instance  the last august the best date was 21st because the last wednesday was 25nd and the last sunday 22nd there were no data in the big file.

I am using in-db and the variable content is YYYYMMDD but I think in creating a rule to fetch the 3 elegible days and try to join with the big file to get the best data one. 

But I need your help, please alone I cannot.

Thanks 

Eduardo Xavier Gomes.

[danilang · Accepted solution]

Hi @Edu_XGomes 

If you're using In-DB Tools, this is really a SQL question about how to write the filter expression.  What you looking for is the proper syntax to find the date of last Wednesday of the month in the database that you're using.   Once you find that date X, you filter for days that are >=X-5 and <=X-3 

Since date function syntax varies you'll need to find the correct format for your database.  To get you started, here's a link on how to find the last Friday of the month in Oracle and another to find the last Wednesday of month in SQL Server

Dan