Relatively new to cognos and am trying to create a report but am stumped at the moment. Here is the situation:
[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?