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?
