How to filter indb database by datetime coming from input tool?

[arjunt1217]

I have a database with data over the past 2 years. I only require data from the past few days to a week. The specific date i need is located in a yxdb file. What can i do to get this date in the in-db filter tool?

One option is to use a data stream out tool, an append tool, then a normal filter, but the problem with that is it takes way too long! I would like to keep this workflow under 5 mins to run, but a data stream out tool takes >30 mins to finish.

My current solution is to use an indb filter to filter for the past 7 days, then a data stream out tool, then an append tool, then a normal filter with the datetime from the input tool. This is an adequate solution, but i would really like to just get the specifc date into the indb filter directly. The potential worry with my current solution is if the specific datetime from the input tool is >7 days in the past, i would lose some data. This in itself is running at about 4.5 mins which is about the longest i want it to run.

I have attached some sample data if it helps! One sheet has a timestamp, Last_Run_DT, this will change so i cant hard code it. 

The other sheet is what my sql data looks like. I want to filter for Closed_Date > Last_Run_DT.

Could a data stream in tool help me with this?

[apathetichell]

you have three options

my prefered option:

1) store your queries in text input tools - use summarize tools to query them. summarize your dates. append your dates to your query. use formula to edit your query so your dates are in an inclause. use dynamic input-in-db to feed in your query. advantage - this limits your query pool to only the dates selected. when used in a macro this is the most dynamic and would allow batching of queries and troubleshooting. requires knowing your db, knowing datetime in your db - knowing how to use your summarize tool. requires a connection name.

2) bring your .yxdb into your db using datastream in.  inner join on date column. requires knowing date syntax. formating dates correctly. having the ability to create temp tables in your db.

3) use a filter in-db in a batch macro. update filter parameter via control paramter -> action tool -> filter in-db. update your filter clause. with an in clause and your dates from your .yxdb. can be iffy on some dbs. requires knowledge of date formats etc...

reminder in-db values are 'value' - not "value" for strings and dates. for in clause it is usually ('value1','value2','valuen')