Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    3

    Unanswered: Grouping by Month in Common Table Expression counts wrong

    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;

    Code:
    ;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

    Code:
    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

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    What are the totals without grouping?
    Last edited by corncrowe; 09-26-12 at 13:42.

  3. #3
    Join Date
    Sep 2012
    Posts
    3
    Without the grouping, CTE2 has a total of 26, but with the grouping only a total of 19. The counts that are missing are 1 for May, 4 for July and 2 for August; totaling 7
    Thank you,
    NickG

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Have you tried using coalesce instead of isnull? Not sure if this will help...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •