I agree with GSquared. Some sample structures & data will make it easier to test.

However a few things jump out of this right away:

SELECT itl.user_id, au.notes, au.shift,

sum(update_qty * m.avg_cubes) as total_cubes,

(select sum(itl2.update_qty * m2.avg_cubes)

from

rp_sku s2,

rp_inventory_transaction itl2,

rp_application_user au2,

rp_minor_average m2

where itl2.dstamp between convert(datetime, '02-APR-09' + ' 03:00:00', 101)

and convert(datetime, '02-APR-09' + ' 20:00:00', 101)

and itl2.code in ('Putaway', 'Pick', 'Relocate')

and (itl2.from_loc_id <> 'SUSPENSE' or itl2.to_loc_id <> 'SUSPENSE')

and au2.user_id = itl2.user_id

and m2.mnr_cd = s2.v_userdef1

and au.shift = au2.shift

and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))

and s2.sku_id = itl2.sku_id ) as shift_total_cubes,

What you have there is a SELECT within the SELECT statement. depending on the structures, the data, etc., you're likely to see some pretty poor performance from this type of querying. In general, I'd recommend changing this to a sub-select as part of the FROM clause and JOIN it to the rest of your query.

You're also likely to run into performance issues around this type of code

and itl2.from_loc_id not in (select location_id from rp_location where loc_type in ('Stage', 'Marshalling'))

Again, you might be better off, depending on structures, data, etc., moving this into a LEFT JOIN and checking for NULL values in the WHERE clause of the main part of the query, or in this case, the sub-query.

Without execution plans, sample structures, etc., it's hard to make anything more than general statements about how to improve the code. Those functions that GSquared mentioned on your date columns are going to prevent index seek operations which will slow things down.