Skip to Content
Former Member
Nov 04, 2016 at 03:44 PM

need help creating a variable in web intelligence which counts edits in a form


a scorecard is a form where the supervisor goes and scores the employee which are called ipas while the IPA is doing an encounter, encounter is where the IPA is helping a customer and every time he is done helping a customer a unique ID is created which is called encounter ID and the scorecard has lots of categories that the Supervisor scores on like compliance, accuracy, completeness, the problem is there is no object that keeps track of all the individuals that are changed if the supervisor goes back to the scorecard to change something after submitting it but they do have a object which keeps count every time the whole scorecard is updated the scorecard id changes as well, for example if the original ID is 122058 the new ID will be 122059, the key identifier is the encounter ID which has same ID as the original scorecard and they also have a object which shows the previous ID for the original scorecard that was edited. If compliance was updated in a scorecard from yes to no that would be 1 count.

here is a case statement that I created in SQL Server which is not showing the correct update.

case when min([PreviousID]) over ( order by [Scorecard_ID]) is null and max([PreviousID])over ( order by [Scorecard_ID]) is not null and LAG([Compliance1]) over ( order by [Scorecard_ID]) <> LEAD ([Compliance1]) over ( order by [Scorecard_ID]) then '1' else '0'