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.
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 InquiryInner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fkLeft Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.IDWHERE (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 NULLGroup By Inquiry.Date),CTE2(total, TransDate) as(SELECT count(Inquiry.ID), Inquiry.Date from InquiryInner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fkLeft Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.IDWHERE (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 NULLGroup By Inquiry.Date)SELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE Aleft outer Join CTE2 B on A.InitDate = B.TransDateGroup 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 themSELECT A.InitDate, B.TransDate, Sum(A.total) As InitCount, Sum(ISNULL(B.total,0)) As TransCount from CTE Aleft outer Join CTE2 B on A.InitDate = B.TransDateGroup 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 AFULL outer Join CTE2 B on A.InitDate = B.TransDateGroup By Month(A.InitDate), A.InitDate, B.TransDate |
|
|
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 dofull join month(A.InitDate) = Month(B.TransDate) but doesn't seem to do it.Thanks for your help,NickG |
|
|
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 ) TransCountFROM Inquiry i INNER JOIN Inquirer ir ON ir.ID = i.InquirerID_fk LEFT JOIN TRANSFER t ON t.TransferInquiryID_fk = i.IDWHERE 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; |
|
|
|
|
|
|
|