Set Modifiers (field against field) - Point in time reporting

[johnny2005uk]

So I'm totally aware of the resource issues associated with field on field set analysis, but I'm stuck overcoming a scripting issue and wondered if anyone had any suggestions for the following: -

We have a very basic script for point in time reporting in SQL

DECLARE @AS_OF_DATE DATE = '3/15/2018'

SELECT COUNT(DISTINCT NO_SID) as Total

@AS_OF_DATE as Date

FROM DIM

WHERE INQUIRY_DT IS NOT NULL

       AND STATUS_CD = 1

       AND @AS_OF_DATE BETWEEN ROW_START_DAY AND ROW_END_DAY

GROUP BY Date;

Returning one row of results, then I accounted for additional dates by passing through a min/max generate calendar to then loop through each of those dates, creating a history of results e.g.

Date	Total
3/15/18	1,651
3/16/18	1,141
3/17/18	1,135
3/18/18	1,619
3/19/18	1,148
3/20/18	1,668
3/21/18	1,789

But... I don't want to use the loop, since it's very case specific and our data model has multiple metrics with different dates, which would require a loop process creating for each.

I can't find a way with set analysis to make this work, but essentially something along the following in theory would work.

I created a date island not associated to the model, which would act as the date reference. The set analysis expression I imagine would start to look like the following: -

sum({$<CalendarDate >= [ROW_START_DAY], CalendarDate <= [ROW_END_DAY]

, [INQUIRY_DT] = {'>0'}, [STATUS_CD] = {'1'}>} 1)

I know my modifiers are incorrect between date fields, but used them to illustrate what was in my mind. Does anyone have any approach that I've not considered? Interval match somehow?

[petter]

I think this might be a valuable read for you:

   The As-Of Table