I have an issue where I am trying to identify the last response to a survey question in a given period of time. I am pulling the data from 2 sources, a SQL query gets me all of the resposnes and the period in which they fell in. I have a second source which is an Excel spreadsheet that breaks the period into 2 ranges of dates, for the first half of the period and the second half of the period. What I need to show is the last response within the first half of the period and the last response within the second half of the period.
I can successfully pull the last response within a period since that is on my original SQL table but I don't seem to be able to hit on the correct solution with the XLS data that provides a range of dates to group the responses by.
The following code worked for the last response across the entire period
|StoreID AS RptdStoreID ,|
|upper(PrincipalDescription) as PrincipalDescription,|
|date(LastChange) as SurveyDate ,|
|StoreID & '-' & SurveyTypeDescription & '-' & Questionid as StoreQuestionCount|
|//,FirstValue(ResponseDescription, LastChange) as FirstResponse|
|//FirstSortedValue(ResponseDescription, LastChange, -1) as LastResponse // Last value|
FROM Survey.qvd (qvd);
left join (Survey)
|FirstSortedValue(ResponseDescription, LastChange) as FirstResponse ,|
|FirstSortedValue(ResponseDescription, -LastChange) as LastResponse, // Last value0|
|date(FirstSortedValue(LastChange, LastChange)) as FirstResponseDate ,|
|date(FirstSortedValue(LastChange, -LastChange)) as LastResponseDate|
FROM Survey.qvd (qvd)
group by StoreID, SurveyTypeDescription, Questionid,Period;
Now my issue is getting the second data source in as part of the Grouping fields. The 2nd data source looks as follows
|CYCLE||START DATE||END DATE||Call Number||call start date||call end date|
I have tried playing with IntervalMatch and doing left joins on this table but none of those seem to be getting the correct answer.
If I did this in SQL I would join the 2 tables, join on the period and where survey date between callstartdate and callenddate and then do a grouping by Cycle and CallNumber for a max on SurveyDate.
Appreciate any help on this.