CTE Join query issues
- by Lee_McIntosh
Hi everyone, 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 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 Reports 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!
