Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: CTE with Union All

    In one line of data I am trying to pull in Year Month, Total Spend for a Month, and Price Discrepancy

    I have one table that I am summing the total amount of records in the table which gives me my total spend. Then the next table is flagged as discrepant so that sums the dollars of discrepant. I want to union these two together bring in 3 columns. One column for each table could be a dummy table = '0' or maybe = ''. I want to smash these together to bring into an excel report.


    Thoughts?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I don't think you need a UNION, that sounds more like a LEFT JOIN to me. Something like:
    Code:
    SELECT DateAdd(m, DateDiff(m, 0, DohickyDate), 0) AS daMonth
    ,  Sum(bucks) AS totalSpend
    ,  Sum(ohCrud) AS discrepancy
       FROM detailSpending
       LEFT JOIN discrepancies
          ON discrepancies.thingamabob = detailSpending.thingamabob
       GROUP BY DateAdd(m, DateDiff(m, 0, DohickyDate), 0)
       ORDER BY DateAdd(m, DateDiff(m, 0, DohickyDate), 0) DESC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pat I ended up using a virtual table to get there. Thanks for the heads up I did join instead of union.

Posting Permissions

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