asked on

filter out result in query

I have a table that has been populated with change events ie where data has changed between months.

In some cases where there are two or more events the data value has gone back to its original value and i want to filter those out.

it could only happen where there is an even number of events for that particular code.

in the example shown its gone from M to A in 2012-07 and then back to M in 2012-08 so in effect no change took place.

SELECT TblTransmission.ChangeId, TblTransmission.AbiCode, TblTransmission.TransmissionPrev, TblTransmission.TransmissionChange, TblTransmission.ChangeYearMonth
FROM TblTransmission;

Open in new window

ee.PNG

Lee Wadwell

Do you only want to filter out where the changes were over contiguous months?
What do you want to include in the output ... only the first and last in a set of changes, every row, or something else?
If it went from M->A then A->M and finally M->A ... do you want all three or just the last one and filter out the first two?

Why can it only occur in even numbers? Is there only two states ... or could it go from M->X, X->A then A->M ... i.e. three events.

PeterBaileyUk

ASKER

Ok you have said very valid things...how about does the first data match the last irrespective of number of events?

so the first event [TransmissionPrev].value = most recent event [TransmissionChange].value

that would work and then it acts for all possible state values

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PeterBaileyUk

ASKER

worked a treat thank you