Ask a question
In your now on hold question you never seem to actually ask a question.
After a bit of reading I figured out the implicit question; that you wanted the data to output as per the last table, but it was coming out like the one to last table. Describing what you're doing is useful, but it's easier to understand if you also ask an explicit question.
Make your question as simple as possible
You seem to have a lot of extraneous information, 4 tables of data, each with many columns, provided as images. I'm sure that’s the real situation you're dealing with but is it really necessary for the question? Try to reduce it down to the bare minimum that replicates the problem; then the question will be far clearer.
Your simpler version
You created a simpler version here (ideally edit the existing question rather than reposting a new version), this suffers from the opposite problem; it isn't clear what you're stuggling with without seeing an attempt. Ideally try to find some middle ground between the two, the simplicity of this version with the attempted solution of the more complicated version.
It would be better to have really simple source data (2 tables, 2-3 columns each), the sql you use to try to get the output. An example of the output, and whats wrong with that output.
However, while not ideal this version is at least clearer and seems to be attracting answers