Do you know what would really help, besides direct access to your system? The DDL for the table(s), some sample data for the tables, the expected results of the query you are working on based on the sample data, and all of this in a readily consumable (meaning cut/paste/run in SSMS) format.

It is really hard to provide good answers based on just some code that apparently doesn't really work.