SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping by Month in Common Table Expressions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickG21
Starting Member

USA
6 Posts

Posted - 09/26/2012 :  11:26:44  Show Profile  Reply with Quote
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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  11:35:33  Show Profile  Reply with Quote
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
Go to Top of Page

NickG21
Starting Member

USA
6 Posts

Posted - 09/26/2012 :  11:58:15  Show Profile  Reply with Quote
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.

Thanks for your help,
NickG
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/26/2012 :  12:25:36  Show Profile  Reply with Quote
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;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000