-
If you wanted to touch the files as little as possible you could work out ranges and overlaps
//Create a spec ensuring to include some overlaps and gaps to test code better q)spec:flip `inst`startDate`endDate!(`A`B`C;2022.01.01 2022.02.01 2022.06.01;2022.03.31 2022.04.30 2022.07.31) q)spec inst startDate endDate -------------------------- A 2022.01.01 2022.03.31 B 2022.02.01 2022.04.30 C 2022.06.01 2022.07.31 //Explode the ranges into their individual dates q)ranges:ungroup select inst,date:startDate+til each 1+endDate-startDate from spec //Now regroup to gather instruments by date q)ranges:0!select inst by date from ranges //Find cases where there are gaps or the instruments change q)ranges:update dDate:deltas[date],dInst:differ inst from ranges //Grab the indexes from your ranges table for the beginning and ending of our needed queries q)rInds:{-1_x,'-1+next x}(exec i from ranges where (dDate>1) or dInst),count ranges q)rInds 0 30 31 89 90 119 120 180 //Pulling out the indexes you can see each pair of rows forms a query q)select from ranges where i in raze rInds date inst dDate dInst --------------------------- 2022.01.01 ,`A 8036 1 2022.01.31 ,`A 1 0 2022.02.01 `A`B 1 1 2022.03.31 `A`B 1 0 2022.04.01 ,`B 1 1 2022.04.30 ,`B 1 0 2022.06.01 ,`C 32 1 2022.07.31 ,`C 1 0 //Sample table of data q)trades:([] date:2022.01.01+til 365;sym:365?`A`B`C) //Build and execute functional selects to query from disk q)result:raze {?[`trades;((within;`date;x`date);(in;`sym;enlist x[`inst]0));0b;()]} each ranges each rInds //Visually inspect result to see months and instruments pulled q)select count i by sym,date.month from result sym month | x ----------| -- A 2022.01 | 8 A 2022.02 | 11 A 2022.03 | 12 B 2022.02 | 8 . B 2022.03 | 9 B 2022.04 | 10 C 2022.06 | 15 C 2022.07 | 11