CODEWITHSUNDEEP
timekdb+
Cole MG
Cole MG

Reputation: 331

Split up date range into chunks to join back into one table KDB+/Q

I have a table that is being joined like so

result: select from table where date within (sd;ed)

where sd and ed span multiple months (like sd:2021.07.01 ed:2021.09.30). The table that I'm querying from has a break if you take more than a month, so to get the result I need, I have to do something like the following:

result: uj(uj(select from table where date within (2021.07.01;2021.07.30);select from table where date within (2021.08.01;2021.08.31));select from table where date within (2021.09.01;2021.09.30))

How can I make this dynamic for any sd and ed? That is, how can I break up time range into first days of months, last days of months, and join them all into one table cleanly? My initial idea was to divide the days in the range x amount of time, to be input by a user, then add the number of days that results to the sd to get frames, but that got messy.

Upvotes: 0

Views: 867

Answers (2)

SeanHehir
SeanHehir

Reputation: 1593

One option for converting your start and end dates into an iterable list of dates might be:

f:{0N 2#(x,raze -1 0+/:`date$mx+1+til(`month$y)-mx:`month$x),y}

Where x is start date and y is end date.

f[2021.07.14;2022.02.09]
2021.07.14 2021.07.31
2021.08.01 2021.08.31
2021.09.01 2021.09.30
2021.10.01 2021.10.31
2021.11.01 2021.11.30
2021.12.01 2021.12.31
2022.01.01 2022.01.31
2022.02.01 2022.02.09

Then you could run:

{select from t where date within x} each f[sd;ed]

And join the results using raze or (uj/)

Upvotes: 2

terrylynch
terrylynch

Reputation: 13572

Something like this should chunk it for you:

raze{select from t where date within x}each(first;last)@\:/:d group"m"$d:sd+til 1+ed-sd

Do not use where date.month=x as you had suggested - at least not for historical queries

Upvotes: 2

Related Questions