MS SQL (Management Studio): How would I best populate a column with a value that occurred in the prior year?

If my SQL table has these 4 columns:


Machine Date Sales PY Sales


A Jan-08 $10 $8


B Jan-08 $15 $12


C Jan-08 $20 $15





A Jan-07 $8


B Jan-07 $12


C Jan-07 $15





What would be the easiest syntax to use to create the Prior Year (PY Sales) column? Basically, the code would look at the Machine and Date for each row and then find the Date 1 year ago for that same Machine and populate PY Sales with the Sales for that Date.





Is there an easy way to do this? Appreciate any advice you might offer as I'm a SQL rookie.

MS SQL (Management Studio): How would I best populate a column with a value that occurred in the prior year?
Update tablename


set PYSales = B.Sales


from Tablename A join TablenameB on A.Machine = B.Machine


and B.Machine = left(A.machine,4) + int(right(A.machine,2)-1)





you might need to convert the INT() calculation to force the leading zero.