Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Combining results of two similar queries into one result set?

    I feel silly for not being able to figure this one out. Here is the background. Customers order a product and enter in a source code (sourceCd). This sourceCd is tied to a marketing program. Idea being we can see that 100 customers ordered from this promo, 200 from this catalog, etc etc. The sourceCd that a customer enters is not always accurate so there is a magic process that adjusts this OrigSourceCd into a final SourceCd, that may or may not be the same.

    I am trying to generate a result set of customer count by sales program based on both the original and final source code. Problem is, I have to do each query separately because in one, I have to join SourceCdKey to SourceCdKey to get the program associated with that SourceCd and in the other i have to join OrigSourceCdKey to SourceCdKey to get the program associated with the original sourceCd. There are some programs is one results set that are not in the other, and vice versa.

    I'm trying to generate a list of that shows customer counts before and after for each program, some which may be null for one, but have counts for the other. I have tries creating 2 separating views and joining them but that doesn't work because it only returns the ones they have in common. Ideas?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Can you give us some sample data and the result set you want based on that sample data?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    I have attached some sample data in a spreadsheet.

    But the idea is this:

    OrigSourceCd
    Program Customers
    A___________10
    B___________20
    D___________25

    SourceCd
    Program Customers
    B___________50
    C___________60
    E___________72

    Result Set I want:
    SourceCd
    Program Orig Custs Final Custs
    A___________10___________0
    B___________20___________50
    C___________0___________60
    D___________25___________0
    E___________0___________72
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT d.Program, COALESCE(o.Customers,0), COALESCE(s.Customers,0)
    FROM (SELECT Program FROM OrigSourceCd UNION SELECT Program FROM SourceCd) AS d
    LEFT JOIN OrigSourceCd o
    ON d.Program = o.Program
    LEFT JOIN SourceCd s
    ON d.Program = s.Program
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser View Post
    SELECT d.Program, COALESCE(o.Customers,0), COALESCE(s.Customers,0)
    FROM (SELECT Program FROM OrigSourceCd UNION SELECT Program FROM SourceCd) AS d
    LEFT JOIN OrigSourceCd o
    ON d.Program = o.Program
    LEFT JOIN SourceCd s
    ON d.Program = s.Program
    Thanks this makes sense. Now in reality, the program actually goes down 3 levels (Program, SubProg1, SubProg2). I just need to figure out how to adapt this to work with that...

    EDIT: hm, definitely not working correctly. showing too many results that dont make sense. ugh. this is a nightmare.
    Last edited by clawlan; 03-05-12 at 15:01.

  6. #6
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    I think much of my issues with this is that I have to output by month and the programs go 3 levels deep. Still not having luck. You can see the data i am working with in the attachment above.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    --  Setup for the big show
    
    CREATE TABLE #OrigSource (
       Program      CHAR(1)
    ,  Customers    INT
       )
    INSERT INTO #OrigSource
       SELECT 'A', 10 UNION ALL
       SELECT 'B', 20 UNION ALL
       SELECT 'D', 25
    
    CREATE TABLE #Source (
       Program      CHAR(1)
    ,  Customers    INT
       )
    INSERT INTO #Source
       SELECT 'B', 50 UNION ALL
       SELECT 'C', 60 UNION ALL
       SELECT 'E', 72
    
    --  vvvv   This is the big show!!!  vvv
    
    SELECT Coalesce(o.Program, s.Program) AS 'Program'
    ,  o.Customers AS 'Orig Custs'
    ,  s.Customers AS 'Final Custs'
       FROM #OrigSource AS o
       FULL OUTER JOIN #Source AS s
          ON (s.Program = o.Program)
    
    --  ^^^  That was the big show!!!  ^^^
    
    DROP TABLE #OrigSource
    DROP TABLE #Source
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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