| Author |
 Topic  |
|
|
NickG21
Starting Member
USA
6 Posts |
 Posted - 09/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 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 |
 |
|
|
NickG21
Starting Member
USA
6 Posts |
Posted - 09/26/2012 : 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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 09/26/2012 : 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; |
|
|
| |
Topic |
|
|
|
Grouping by Month in Common Table Expressions
