[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

filter out result in query

Posted on 2012-09-03
4
Medium Priority
?
584 Views
Last Modified: 2012-09-04
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
0
Comment
Question by:PeterBaileyUk
  • 2
  • 2
4 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38362598
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
 

Author Comment

by:PeterBaileyUk
ID: 38362714
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
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38363000
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 38363011
worked a treat thank you
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question