Stumped and Looking for Assitance on a Query

[dbowen1]

Relatively new to cognos and am trying to create a report but am stumped at the moment.  Here is the situation:

Example:

Data Items:

[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?

[Lynn]

You could try to build a query that returns just Employee, Employee #, and Survey Name with aggregate functions set to None for the first two columns, but set to minimum for Survey Name. See what that returns for Jim, Bob, and Sue. If everyone turns up as expected, then use that query to join to another query with questions and answers, joining on the three key fields. That should make Bob's old survey drop out of the result.

Maybe you need maximum rather than minimum, but whichever returns New Survey is what you want.

Probably not a terribly well performing option if you have a very high data volume, but might spur other ideas for you.

[dbowen1]

In the example I only provided a portion of the data size, there are actually multiple different surveys that I would need to apply the query to (my bad for only applying a small sampling of the data).  If there were only two possibilities I could go with minimum aggregation on one query and max on the other, however once additional surveys (New survey, old survey, newer survey (or whatever they call the next survey they publish) are published I don't think aggregate will work.  I do appreciate the suggestion though.

[RKMI]

Hi,

This might not be the best answer but, let me know if this would make sense. Since your survey is constantly changing and not employee are likely to fill an older survey. Why don't you write multiple query with different filter condition and then union them in final query. And the unioned query would be the driver for the output list.

Example: Query 1 has four colums and filter as [Survey Name] in ('New Survey') which will give you all the Jim's and Bob's.
               Query 2 same four colums and the filter as [Survey Name] not in ('New Survey') and [Survey Name] in ('Old Survey') which should give you all the Sue's.
Then Query 3 is the Union of 1&2 so you will get a unique record for each Employee based on the survey they filed.

If you ever want to make a newest survey then just add a query with the same cols and apply filters.

I hope this makes sense.

Another better way to apporach this is thru a concept called SCD ( Slowly changing dimension) where the preson who built the table have to add last updated column and if the user has filled an updated survey then leave it blank otherwise whatever last survey was filled will be blank/ if the same user filled an old survey add a last updated date and in the report you can just add a simple filter where last updated is null.

Thanks,
RK

[dbowen1]

Query 1 works great, however query 2 ends up giving me 1 record for Bob and 1 record for Sue.  The filter does not eliminate Bob's Old survey from showing up as it is looking at each data item individually rather than applying the filter against the sum of the whole.  Hope that makes sense.  Based on the fact that it pulls data for Bob in each query the union query then gives me two records for Bob and puts me back where i began.

[Lynn]

Perhaps you could create a derived query item that assigns a number to each survey with a case statement, and then set maximum on that query item. You'd need to update the expression as more surveys are added but it would let you create a single driver query to join with.

RKMI has certainly given the best advice in terms of getting some assistance on the database side if that is possible.

[CognosPaul]

Can you describe the survey results table? If you have an incrementing unique id, then I have another solution.

[wyconian]

Lynn's solution sounds good :-)

Do you have any kind of date when the survey was completed?  If you do you could maybe just filter on the maximum completed date for the employee.

Good luck :-)

[dbowen1]

Unfortunately no unique incrementing ID to use nor any form of date/time stamp.  As this is a hosted solution/database we have extremely limited control on the database/reporting structure. 

Lynn's recommendation appears like it will work and seems like my best shot.  Using her recommendation I can easily add new surveys as they are published and assign the appropriate values accordingly.

Thanks to each of you for your recommendations.  There definitely seems to be a number of experienced users on this board and your assistance/words of advise are greatly appreciated.