Hi All
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, |
| | SurveyTypeDescription, |
|
| Questionid , |
|
| Questiontext, |
| ResponseTypeCode, |
|
| | ResponseID , |
| ResponseDescription , |
| SurveyYesNo, |
| SurveyQuantity, |
| SurveyPrice, |
|
| SurveyResponseText, |
|
| date(LastChange) as SurveyDate , |
| MinResponseId, |
| Period, |
| PeriodLastResponse, |
| 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)
| 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.
Appreciate any help on this.
