We're a community of 1076K IT Pros here for help, advice, solutions, professional growth and fun. Join us!
1,075,585 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Start New Discussion Reply to this Discussion

Common Table Expression Grouping numbers incorrect

Hey everyone,
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;

;With CTE(total, InitDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.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.')AND TransferInquiryID_fk IS NULL
Group By Inquiry.Date
)
,CTE2(total, TransDate) as
(
SELECT count(Inquiry.ID), Inquiry.Date from Inquiry
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.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.') AND TransferInquiryID_fk IS NOT NULL
Group By Inquiry.Date
)
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

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

1
Contributor
0
Replies
2
Views
Question
Answered
nickg21
Newbie Poster
22 posts since Nov 2011
Reputation Points: 10
Solved Threads: 0
Skill Endorsements: 0
Question Self-Answered as of 7 Months Ago

This question has already been solved: Start a new discussion instead

Post: Markdown Syntax: Formatting Help
 
You
 
© 2013 DaniWeb® LLC
Page rendered in 0.5154 seconds using 2.67MB