CTE Join query issues

this problem has me head going round in circles at the moment and i wondering if anyone could give any pointers as to where im going wrong.

Im trying to produce a SPROC that produces a dataset to be called by SSRS for graphs spanning the last 6 months.

The data for example purposes uses three tables (theres more but the it wont change the issue at hand) and are as follows:

tbl_ReportList:

Report      Site
----------------
North       abc
North       def
East        bbb
East        ccc
East        ddd
South       poa
South       pob
South       poc
South       pod
West        xyz

tbl_TicketsRaisedThisMonth:

Date                     Site     Type      NoOfTickets
---------------------------------------------------------
2010-07-01 00:00:00.000 abc      Support        101
2010-07-01 00:00:00.000 abc      Complaint       21
2010-07-01 00:00:00.000 def      Support          6
...
2010-12-01 00:00:00.000 abc      Support         93
2010-12-01 00:00:00.000 xyz      Support          5

tbl_FeedBackRequests:

Date                       Site  NoOfFeedBackR
----------------------------------------------------------------
2010-07-01 00:00:00.000 abc           101
2010-07-01 00:00:00.000 def            11
...
2010-12-01 00:00:00.000 abc       开发者_Go百科     63
2010-12-01 00:00:00.000 xyz             4

I'm using CTE's to simplify the code, which is as follows:

DECLARE @ReportName VarChar(200)
SET @ReportName = 'North';

WITH TicketsRaisedThisMonth AS
(
    SELECT
            [Date],
            Site,
            SUM(NoOfTickets) AS NoOfTickets
    FROM    tbl_TicketsRaisedThisMonth
    WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0)
    GROUP BY [Date], Site
),

FeedBackRequests AS
(
    SELECT
            [Date],
            Site,
            SUM(NoOfFeedBackR) AS NoOfFeedBackR
    FROM    tbl_FeedBackRequests
    WHERE [Date] >= DATEADD(mm, DATEDIFF(m,0,GETDATE())-6,0)
    GROUP BY [Date], Site
),

SELECT
    trtm.[Date]
    SUM(trtm.NoOfTickets) AS NoOfTickets,
    SUM(fbr.NoOfFeedBackR) AS NoOfFeedBackR,

FROM tbl_ReportList rpts

LEFT OUTER JOIN TotalIncidentsDuringMonth trtm ON rpts.Site = trtm.Site
LEFT OUTER JOIN LoggedComplaints fbr ON rpts.Site = fbr.Site

WHERE rpts.report = @ReportName
GROUP BY trtm.[Date]

And the output when the sproc is pass a parameter such as 'North' to be as follows:

Date                       NoOfTickets             NoOfFeedBackR
-----------------------------------------------------------------------------------
2010-07-01 00:00:00.000             128                              112
2010-08-01 00:00:00.000     <data for that month>       <data for that month>
2010-09-01 00:00:00.000     <data for that month>       <data for that month>
2010-10-01 00:00:00.000     <data for that month>       <data for that month>
2010-11-01 00:00:00.000     <data for that month>       <data for that month>
2010-12-01 00:00:00.000             122                              63

The issue I'm having is that when i execute the query I'm given a repeated list of values of each month, such as 128 will repeat 6 times then another value for the next months value repeated 6 times, etc. argh!

---

You need to change the way you return the months. In your resultset you're returning trtm.[Date], but with a LEFT JOIN to trtm so this may not exist.
If it always exists, turn it into an INNER JOIN and change the other LEFT JOIN to include a link from trtm.[Date] To fbr.[Date].
Otherwise, you need a 'months' table to return the list of months, and left join from that to trtm and fbr on the date field (as well as the existing joins on Site)