UnDP value change alert

Hi there, I've been working on this today and I think I finally worked up a solution. Basically this alert uses a custom SQL query which will check if the status value of the UnDP has changed, if so it will get triggered.

I've searched in the forums for this solution but I couldn't find it so I though I might share it in case someone finds it helpful.

Here is the query:

SELECT CustomPollerAssignmentView.AssignmentName
     , CustomPollerAssignmentView.CustomPollerAssignmentID
FROM CustomPollerAssignmentView
JOIN (
     SELECT CustomPollerAssignmentID
          , DateTime
          , Status
     FROM CustomPollerStatus
     WHERE CustomPollerAssignmentID IN (
               SELECT CustomPollerAssignmentID
               FROM CustomPollerAssignment
               WHERE CustomPollerID = '3c52aa41-28a2-40bf-914c-9c94041ae545'
               )
     ) AS t1
     ON t1.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID
JOIN (
     SELECT *
     FROM (
          SELECT CustomPollerAssignmentID
               , DateTime
               , Status
               , ROW_NUMBER() OVER (
                    PARTITION BY CustomPollerAssignmentID ORDER BY DATETIME DESC
                    ) AS rn
          FROM CustomPollerStatistics AS cps
          WHERE CustomPollerAssignmentID IN (
                    SELECT CustomPollerAssignmentID
                    FROM CustomPollerAssignment
                    WHERE CustomPollerID = '3c52aa41-28a2-40bf-914c-9c94041ae545'
                    )
          ) AS tt
     WHERE tt.rn = 2
     ) AS t2
     ON t1.CustomPollerAssignmentID = t2.CustomPollerAssignmentID
WHERE t1.Status <> t2.Status

Anonymous
Parents Comment Children