So there's always exactly 1S and 1E record? And potentially many As?

If so, join table to itself with 1 side being S and another being E. Create a CTE that sums up the As by manifest.

My query above does that except that I didn't use a CTE on the As part having assumed there could only be one A record per item. You can make that change pretty easily.