I try to use SQLKata with SQL Server for the following use case: Group turnover data per day/week/month/year for a configurable interval. As I also need to include the time units without any turnover data I chose to join this with a table valued function that returns a set of dates (see this link for more info about this SQL Server range of dates)
I query the table valued function in a CTE and would like to left join it with my turnover data. In order to do so I would need to use functions in the join condition though as the dates of the turnover data are more granular than the dates from the DateRange function.
As I have not seen an example in the SQL Kata documentation using a function in a join condition and trying it out gave an error too I guess it's not possible to do it like this.
What I tried:
- Include a function in the join condition to reduce some details from the turnover date objects
- Use 2 CTES like this: new Query().With(...).With(...) but it complained about the empty base query
What would be the best approach to implement this in a query?