In order to achieve this using Queries, you will have to divide these into multiple steps as following -
Create a DE, say Test_DE and include the following fields -
Add a dummy value to the Counter field of the DE and save it. Then Create a Query with the update operation as below and insert into Test_DE -
CASE WHEN TimeSent > '07:00PM' and TimeSent < '10:00PM'
END AS Flag,
FORMAT(getdate(), 'hh:mmtt') as TimeSent,
getdate() as CurrentDate,
Counter = 'Dummy value that was added'
) as OUTPUT
- Create a new DE similar to above, say Test_DE1. Then create new query with overwrite operation as below and insert into Test_DE1 -
Flag = 'TRUE'
If your file drop had started during the time period specified in step 2 query, then Test_DE1 will contain 1 record. (Note - this is also applicable, if 2 file drops happen)
- Finally, create an automation as below -
In the third step of the automation, you have to select the DE(Test_DE1) and include condition if count is equal to 1. If it contains a record then it will proceed to next steps where you can write the queries to insert your data into specific DE's. Else, if record count is 0, then it will stop at step 3 of the automation.
Hope this helps!!