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
LOAD StoreID,
StoreID AS RptdStoreID ,
upper(PrincipalDescription) as PrincipalDescription,
//,FirstValue(ResponseDescription, LastChange) as FirstResponse
//FirstSortedValue(ResponseDescription, LastChange, -1) as LastResponse // Last value
FROM Survey.qvd (qvd);
left join (Survey)
Load StoreID,
SurveyTypeDescription,
Questionid ,
Period,
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
201201
1/1
1/28
1
1/1
1/14
201201
1/1
1/28
2
1/15
1/28
201202
1/29
2/25
1
1/29
2/11
201202
1/29
2/25
2
2/12
2/25
201203
2/26
3/24
1
2/26
3/10
201203
2/26
3/24
2
3/11
3/24
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.
