declare @AsOf as date='6/17/17';

select
partcategory.name as catname,part.id,part.name as partname,pl.locationid,
round(isnull(t1.qtyonhand,0),2) as na,round(isnull(t2.qtyonhand,0),2) as pont,round(isnull(t3.qtyonhand,0),2) as tampa,round(isnull(t4.qtyonhand,2),2) as galax
from 
(SELECT Location.ID AS LocationID,Location.Name AS LocationName,Location.LotDateCutOff,Part.ID AS PartID FROM Location,Part) PL
LEFT JOIN Part ON PL.PartID=Part.ID
left join (select partid,locationid,qtyonhand,qtyonso,qtyonpo,qtyonwo from partlocationhistory where cast(inventorydate as date)=@AsOf) t1 on PL.PartID=t1.partid AND PL.LocationID=t1.LocationID
left join (select partid,locationid,qtyonhand,qtyonso,qtyonpo,qtyonwo from aims_pont.aims_pont.dbo.partlocationhistory where cast(inventorydate as date)=@AsOf) t2 on PL.PartID=t2.partid AND PL.LocationID=t2.LocationID
left join (select partid,locationid,qtyonhand,qtyonso,qtyonpo,qtyonwo from aims_tampa.aims_tampa.dbo.partlocationhistory where cast(inventorydate as date)=@AsOf) t3 on PL.PartID=t3.partid AND PL.LocationID=t3.LocationID
left join (select partid,locationid,qtyonhand,qtyonso,qtyonpo,qtyonwo from aims_galax.aims_galax.dbo.partlocationhistory where cast(inventorydate as date)=@AsOf) t4 on PL.PartID=t4.partid AND PL.LocationID=t4.LocationID
left join partcategory on part.categoryid=partcategory.id
where 
(round(isnull(t1.qtyonhand,0)-isnull(t2.qtyonhand,0),0)<>0
or round(isnull(t1.qtyonhand,0)-isnull(t3.qtyonhand,0),0)<>0
or round(isnull(t1.qtyonhand,0)-isnull(t4.qtyonhand,0),0)<>0
or round(isnull(t2.qtyonhand,0)-isnull(t3.qtyonhand,0),0)<>0
or round(isnull(t2.qtyonhand,0)-isnull(t4.qtyonhand,0),0)<>0
or round(isnull(t3.qtyonhand,0)-isnull(t4.qtyonhand,0),0)<>0)
order by pl.locationid,partcategory.name,part.name