Query Advice 3

[spiral123]

I have a table with an ID field a year field and an odometer field, I need to show all records that have an odometer reading higher in a previous year than in a later year.
So
0001 1999 20000
0001 2000 25000
Is OK But I need to see where
0001 1999 20000
0001 2000 19000
Im not sure where to even start

 

[Golom]

Try

Select Y1.ID, Y1.YearValue, Y1.Odometer, Y2.YearValue, Y2.Odometer

From tbl As Y1 INNER JOIN tbl As Y2 ON Y1.ID = Y2.ID

Where     Y1.YearValue < Y2.YearValue
      AND Y1.Odometer  > Y2.Odometer

 

[r937]

assuming the ID field is for a vehicle, in other words, to compare rows, it is necessary to match on ID...

[tt]select t1.ID
, t1.yr
, t1.odometer
, t2.yr
, t2.odometer
from atable t1
inner
join atable t2
on t1.ID = t2.ID
and t1.yr > t2.yr
where t1.odometer < t2.odometer[/tt]


rudy

http://r937.com/

SQL Consulting

 

[Quehay]

Nice posts guys**

If you have to compare specific years, and there are more than two years represented it would be best to have views/queries that are year specific:

qry_2002 as

select id,year,odo
from table
where year = 2002

qry_2003 as

select id,year,odo
from table
where year = 2003

qry_comparison

select id,year,odo
from qry_2003 inner join qry_2002 on
id = id
where qry_2003.odo > qry_2002.odo

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[r937]

Quehay, you're not serious, are you?

you must love going in to all your applications and adding new queries every time a new year rolls around

this still being January, i'm surprised you have time for forums...

;-)

rudy

http://r937.com/

SQL Consulting

 

[Quehay]

But for those of us who read the requirements carefully...
;-)

Comparison is between two years, which means 2003-2003, 2004-2003, etc., rather than show me any auto that had a greater mileage in any subsequent year. The original model will show the latter rather than the former.

But sorry, gotta go and write up invoices for all those query hard codings.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Quehay]

But wait...

higher in a previous year than in a later year

You're right--I was imposing my requirements!!

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Golom]

Yeah ... grudgingly ... Quehay may have a point ... but I think it can be handled by

Select Y1.ID, Y1.YearValue, Y1.Odometer, Y2.YearValue, Y2.Odometer

From tbl As Y1 INNER JOIN tbl As Y2 ON Y1.ID = Y2.ID

Where

Y1.YearValue = Y2.YearValue - 1

      AND Y1.Odometer  > Y2.Odometer

 

[r937]

yeah, i though about something like t1.yr = t2.yr - 1 in order to test consecutive years

but then i realized that my solution has the added benefit that it will show you how long the problem has been going on, i.e. how far back

consider the readings --

1999 21405
2000 23753
2001 13342
2002 20793
2003 21598

my query should yield --

2001 13342 1999 21405
2001 13342 2000 23753
2002 20793 1999 21405
2002 20793 2000 23753

i'm sure if you played with max and min (max and moritz?) it would show that the &quot;rollback&quot; was in effect from 1999 to 2003

rudy

http://r937.com/

SQL Consulting

 

[Quehay]

Yes that's optimum for bringing in adjacent year comparisons (then when information is needed about specific years you can write a magic number query)!! If only I had more time to think about this...there's just so much query updating to do...

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com