Can you please help me to convert this complex SQL in to KQL?
It’s generating list of days as a temp table (Alias days) and joining with table1 using joining condition of created_date between start_date (“01-01-2024”) and end_date (“12-31-2024”).
SELECT pk,day FROM table1 JOIN UNNEST (GENERATE_DATE_ARRAY(DATE_SUB(DATE("12-31-2024"), INTERVAL 30 DAY), DATE("12-31-2024"))) AS days ON DATE(created_date, "US/East") between DATE_SUB(day, INTERVAL DATE_DIFF(DATE("12-31-2024"), DATE("01-01-2024"), DAY) DAY) AND day;
My solution is something like this below: But this KQL doesn’t produce expected output.
let days = range day from "01-01-2024" to "12-31-2024" step 1d; let temp_table = table1 | join kind=inner (days) on $left.created_date == $right.day