Column Difference from same table

[Ice Rhino]

I have a table that contains a significant amount of data. The columns are as follows;

ID, WeekNo, Ref, Cost

What I want a query to do is only show me differences in Cost value where the Ref is the same

For example

ID, WeekNo, Ref, Cost
1, 1, ABC, 123
2, 1, DEF, 456
3, 2, ABC, 123
4, 2, DEF, 789

The query I would like, would only show me the result of Rows 2 & 4 because the value of cost is different between the weeks.

At the moment I have two queries, One that pulls out the last weeks value (WeekNo 1), and the other that pulls out the same information but for the comparison week (WeekNo 2). In the third query I created I linked the two columns from the Ref fields, but could not work the logic to only show me ones where the differences in cost were displayed.

Thanks in advance of the resolution to this. I know it is basic query work, but just have a logical block in getting it to work.

Regards

 

[FoFa]

Try this:
SELECT T1.COST, T2.COST, (T1.COST - T2.COST) as COSTDIFF
FROM MyTable AS T1
INNER JOIN MyTable as T2 on T1.Ref = T2.Ref
WHERE T1.WeekNo = 1
AND T2.WeekNo = 2

 

[Ice Rhino]

Ummm, yeah, that could work. I will give it a try. That also kind of helps with a problem I was thinking about anyway in showing the difference between last week's and this week's values.

How would I then only display the records that have a changed value? A new query that only shows the records in the previous query result that are not equal to zero? I am not sure if taht makes sense, if a value was a 1000 and is now a 1000, the difference would be zero so that record would not display, is that right? What scenario could arise where that simple logic would not be applicable? Just thinking aloud

Ummm

Thanks

 

[FoFa]

Add to the criteria:
AND T1.COST <> T2.COST

 

[Ice Rhino]

Doh!!! of course

Stupid of me to ask that one. You can tell I have been away from work for three weeks

Thanks Mate