CTE finds the initial inquiries for the respective date range, and CTE2 finds what inquiries are transfers, the problem is that when running the queries individuall CTE has a total of 170, and CTE2 has a total of 26, but when I run the SELECT Statement with them

SELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
left outer Join CTE2 B on A.InitDate = B.TransDate
Group By Month(A.InitDate), A.InitDate, B.TransDate

I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.Thanks for any help,NickG

That seems like it would be because of the join rather than the grouping . Because you are grouping by InitDate from A and TransDate from B, the grouping by month should have no impact.

IF there are some rows in CTE2 which don't have corresponding rows in CTE, this can happen. Just for testing purposes, you can check to see if this is the case by changing the left join to a full join and looking for rows where values in the left table are null.

SELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE A
FULL outer Join CTE2 B on A.InitDate = B.TransDate
Group By Month(A.InitDate), A.InitDate, B.TransDate

hey sunita,by checking with the full outer join, I do get the 7 records that were being excluded, the issue is that I am using this as a Report Builder query, and since it delivers the InitDate as a NULL value for these last records, the grouping is off. Since these results all exist within a month that is available in InitDate is there a way I could do the grouping by just the month? I have tried to do

full join month(A.InitDate) = Month(B.TransDate) but doesn't seem to do it.

I don't understand the business logic completely, but can you write the query without using the CTE's ? I am thinking something like this. If you want to group by date, use it as it is (assuming the date does not have the time part). If you want to group by month, use the commented out portion and remove the date column.

SELECT
i.Date,
--YEAR(i.Date) AS Yr, MONTH(i.Date) AS Mnth,
COUNT(CASE WHEN t.TransferInquiryID_fk IS NULL THEN 1 ELSE 0 END ) InitCount,
COUNT(CASE WHEN t.TransferInquiryID_fk IS NOT NULL THEN 1 ELSE 0 END ) TransCount
FROM
Inquiry i
INNER JOIN Inquirer ir
ON ir.ID = i.InquirerID_fk
LEFT JOIN TRANSFER t
ON t.TransferInquiryID_fk = i.ID
WHERE
Inquiry.Date >= '3/1/2012'
AND Inquiry.Date <= '9/26/2012'
AND Inquiry.Date IS NOT NULL
AND (Inquirer.Program = 'Res. Referral Coord.')
GROUP BY
-- YEAR(i.Date), MONTH(i.Date)
i.Date;