Date Range

In EAC, we cannot perform Group By on subquery with filters, we need to join table with DateRangeView function to achieve certain reporting.

This is limitation by SQL Server.

Alternative is to create a cross join with DateRangeView(start, end, step) function which will return actual rows and you can then perform sum/count over query within given date range.

   // make sure you retrieve only one object
   this.entityService.Query(Account)
       .joinDateRange(start, end, "Day")
       .select((x) => ({
           posts: x.entity.posts.where((p) => p.dateCreated >= x.range.startDate 
               && p.dateCreated < x.range.endDate).count(),
           reviews: x.entity.reviews.where((r) => r.dateCreated >= x.range.startDate 
               && r.dateCreated < x.range.endDate).count()
       });

Create DateRangeView SQL Script

CREATE FUNCTION [dbo].[DateRangeView]
(
	-- Add the parameters for the function here
	@start DateTime2,
	@end DateTime2,
	@step nvarchar(20) = 'Day'
)
RETURNS TABLE
AS
RETURN (
	WITH E1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
            ),                          -- 1*10^1 or 10 rows
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
	E4(N) AS (SELECT 1 FROM E2 a, E2 b),
	cteTally10000(N) AS (SELECT TOP (
		CASE
			WHEN @step = 'Year' THEN DATEDIFF(YEAR, @start, @end)
			WHEN @step = 'Quarter' THEN DATEDIFF(QUARTER, @start, @end)
			WHEN @step = 'Month' THEN DATEDIFF(MONTH, @start, @end) 
			WHEN @step = 'Week' THEN DATEDIFF(WEEK, @start, @end)			
			WHEN @step = 'Day' THEN DATEDIFF(DAY, @start, @end) 
			WHEN @step = 'Hour' THEN DATEDIFF(Hour, @start, @end) 
			WHEN @step = 'Year' THEN DATEDIFF(YEAR, @start, @end)
			ELSE DATEDIFF(DAY, @start, @end) 
		END
	) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2)
	SELECT StartDate = CASE
			WHEN @step = 'Year' THEN DATEADD(YEAR, N-1,  @start)
			WHEN @step = 'Quarter' THEN DATEADD(QUARTER, N-1,  @start)
			WHEN @step = 'Month' THEN DATEADD(MONTH, N-1,  @start)
			WHEN @step = 'Week' THEN DATEADD(WEEK, N-1,  @start)
			WHEN @step = 'Day' THEN DATEADD(DAY, N-1,  @start)
			WHEN @step = 'Hour' THEN DATEADD(Hour, N-1,  @start)
			WHEN @step = 'Year' THEN DATEADD(YEAR, N-1,  @start)
			ELSE DATEADD(DAY, N-1,  @start)
		END,
	EndDate = CASE
			WHEN @step = 'Year' THEN DATEADD(YEAR, N,  @start)
			WHEN @step = 'Quarter' THEN DATEADD(QUARTER, N,  @start)
			WHEN @step = 'Month' THEN DATEADD(MONTH, N,  @start)
			WHEN @step = 'Week' THEN DATEADD(WEEK, N,  @start)
			WHEN @step = 'Day' THEN DATEADD(DAY, N,  @start)
			WHEN @step = 'Hour' THEN DATEADD(Hour, N,  @start)
			WHEN @step = 'Year' THEN DATEADD(YEAR, N,  @start)
			ELSE DATEADD(DAY, N,  @start)
		END
	FROM cteTally10000)