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
PeterBaileyUkAsked:
Who is Participating?
 
lwadwellCommented:
Something like:
SELECT *
FROM ((SELECT TblTransmission.AbiCode, MIN(TblTransmission.ChangeYearMonth) as minYM, MAX(TblTransmission.ChangeYearMonth) as maxYM FROM TblTransmission GROUP BY TblTransmission.AbiCode) as v
INNER JOIN TblTransmission as first ON first.ChangeYearMonth = v.minYM and first.AbiCode = v.AbiCode)
INNER JOIN TblTransmission as last ON last.ChangeYearMonth = v.maxYM and last.AbiCode = v.AbiCode
WHERE first.TransmissionPrev <> last.TransmissionChange;

Open in new window

takes the minimum ans maximum ChangeYearMonth for each AbiCode and joins back to the table for each to determine whether the first TransmissionPrev  is not equal to the last TransmissionChange.

You could then use that query as the basis to get the full rows like:
SELECT *
FROM TblTransmission
WHERE AbiCode IN (SELECT v.AbiCode
FROM ((SELECT TblTransmission.AbiCode, MIN(TblTransmission.ChangeYearMonth) as minYM, MAX(TblTransmission.ChangeYearMonth) as maxYM FROM TblTransmission GROUP BY TblTransmission.AbiCode) as v
INNER JOIN TblTransmission as first ON first.ChangeYearMonth = v.minYM and first.AbiCode = v.AbiCode)
INNER JOIN TblTransmission as last ON last.ChangeYearMonth = v.maxYM and last.AbiCode = v.AbiCode
WHERE first.TransmissionPrev <> last.TransmissionChange);

Open in new window

0
 
lwadwellCommented:
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.
0
 
PeterBaileyUkAuthor Commented:
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
0
 
PeterBaileyUkAuthor Commented:
worked a treat thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.