Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping by Month in Common Table Expressions

Author  Topic 

NickG21
Starting Member

6 Posts

Posted - 2012-09-26 : 11:26:44
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 11:35:33
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

6 Posts

Posted - 2012-09-26 : 11:58:15
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-26 : 12:25:36
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
   

- Advertisement -