Try to isolate the error. Run this code out of the stored procedure and debug it to see where is the problem.

Then this should do the trick :

I think what you want is to query all routing_history rows and show period_month column only if available. This is a left join :

select

p.period_month,r.warehouse_code,r.item_number,r.origin_warehouse_code,r.Fromdate

from

Routing_History r

left join Period p...

What is the datatype of LH.hazardCode column ?

fragment count 2000 means that index data is spread accross 2000 blocks of consecutive pages.