Pinned topic Query Assistance
Employee Employee # Survey Name Question Name Answer
Jim 123 New Survey ......... ...........
Bob 456 New Survey blah blah blah blah
Bob 456 Old Survey ........ ............
Sue 789 Old Survey ......... .............
Scenario: Originally the survey was named Old Survey and some employees completed that survey, a new survey was then created and some employees completed that survey as well.
In the example I want Jim's answers from new survey as that is the only survey he completed. Bob's answers from New survey even though he completed both surveys (as the new survey contains the most current responses) and Sues answers from Old Survey as she has never completed a new survey.
Any thoughts on a query that would basically say
if new survey exists and old survey does not exist use new survey
if old survey exists and new survey does not exist use old survey
if both surveys exists use new survey???
This report is being created through a hosted solution so I can not make a direct call to SQL. Can anyone offer any help?
Re: Query Assistance2013-04-06T14:24:27ZThis is the accepted answer. This is the accepted answer.Hi,
I don't know what model is yours. If there is any date field then you can make it like maximum(date for employee), then it will fetch latest record for each employee.
Mir Mohammed Ali 270002N6571 Post
Re: Query Assistance2013-06-03T16:28:56ZThis is the accepted answer. This is the accepted answer.
How about creating a data item in the existing query (lets call this existing query 'Base Query' for the sake of discussion) called 'Survey Type Flag'. The logic for this data item would be: if(Survey Name='New Survey' then 1, else 0).
Next, pull another query based on top of the 'Base Query' and grab just the Employee and create the following expression on the 'Survey Type Flag' : Maximum( Survey Type Flag for Employee). Lets name this data item 'Recent Survey Flag'. Basically, this query (lets name it 'Employees by recent Survey Flag' would give us the Employee and the most recent survey flag.
Finally, we'll pull in another query where we inner join 'Base Query' and 'Employees by recent Survey Flag' on the Employee field and 'Base Query'.Survey Type Flag='Employees by recent Survey Flag'.Recent Survey Flag
Hope that helps.
Prinny 270006E4B91 Post
Re: Query Assistance2013-06-13T16:50:25ZThis is the accepted answer. This is the accepted answer.
Split the query into 2, 1 with new surveys and 1 with old surveys, do a left join with old being on the left. Then use a if statement if new is null then old else new.