Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I think this might be a valuable read for you: