Changes in flag captured weekly

[cognos79]

I have a requirement where I need to report on the changes in the flag values for the Member.

Lets say Member ID is 98789 has 'Premium' flag as 'Y' currently.
Other possible values for this flag is 'N' and Null. At one point of time, one member ID can have only 1 flag value.

Report requirement is to identify the members that changed. Below are the scenarios -

Flag Change              Change
(from old report)       name (in new report)
'Y' -> 'N'                A1
'N' -> 'Y'                B1
Null -> 'N'              N1
Null -> 'Y'              N2
'Y' -> Null              A2
'N' -> Null             B2

Its a relational database. Plan is to use event studio, but my concern is that the 'Flag' table does not have any date field to use. Is there a way to compare the old report output with the new report since there is no date field in the table.

This is the first time I am trying to use event studio. I would appreciate your inputs on the approach or if there is any alternative and easier way of doing it.  Please suggest..

Thanks in advance !!

[dougp]

A little more data would be helpful...

Assuming your table is like this...

MemberId	Premium
98789	Y
98790	N
98791	N

...but yesterday looked like this...

MemberId	Premium
98789
98790	N
98791	Y

...are you saying you want your report output to look like...

MemberId	Change
98789	A2
98791	B1

...?

You don't need dates for Event Studio to work.  It will detect that there was a change (a row was added to or removed from the results of the query that Event Studio is using) between yesterday (old) and today (new) and run the report (or perform whatever actions you told the agent to do).  The trick will be computing the "change name".  You will need to store yesterday's data somewhere for that to be possible.  I don't see a way to use the "old" data set in the comparison as a data set in a report, or a way to cause a data set or upload to refresh.  Maybe MFGF or Cognos Paul know how.

[cognos79]

Thank you DougP for adding the data for better understanding ..  you got it right.. that's what the requirement is..