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

    Answered: Union two sets (consolidating results)

    Code:
     SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6) as REPORTING_MONTH, t.EMPLOYEE, 
     '' as COUNT_FTP,
     CASE WHEN [MEDIUM_RCVD] = 'EMAIL' THEN COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_EMAIL
        
      FROM [GPO].[dbo].[DW_SUBMISSION] as s
      JOIN #TEMPActivity as t
      ON  t.SUPPLIER = s.DW_SELLER_NM 
      AND t.INV_YEAR_MO = LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING([REPORTING_MONTH],5,6)
     
     
      GROUP BY REPORTING_MONTH, MEDIUM_RCVD, t.EMPLOYEE
      --ORDER BY REPORTING_MONTH
      
      UNION
      
      
       SELECT DISTINCT LEFT([REPORTING_MONTH], 4)+'-'+SUBSTRING(s1.[REPORTING_MONTH],5,6) as REPORTING_MONTH, t1.EMPLOYEE, 
     CASE WHEN [MEDIUM_RCVD] = 'FTP' THEN  COUNT(MEDIUM_RCVD) ELSE '' END AS COUNT_FTP
     '' as COUNT_EMAIL 
        
      FROM [GPO].[dbo].[DW_SUBMISSION] as s1
      JOIN #TEMPActivity as t1
      ON  t1.SUPPLIER = s1.DW_SELLER_NM 
      AND t1.INV_YEAR_MO = LEFT(s1.[REPORTING_MONTH], 4)+'-'+SUBSTRING(s1.[REPORTING_MONTH],5,6)
     
     
      GROUP BY s1.REPORTING_MONTH, s1.MEDIUM_RCVD, t1.EMPLOYEE
      ORDER BY REPORTING_MONTH

    I'm trying to get the set to come out all on one line. REPORTING_MONTH, EMPLOYEE, COUNT_FTP, COUNT_EMAIL

    But when I try null or '' it creates a second record and doesn't merge the two results. Frustrating.

    Thanks for any help.

  2. Best Answer
    Posted by Pat Phelan

    "Just for giggles and grins, try:
    Code:
    SELECT -- never mix GROUP BY with DISTINCT, it is bad karma!
       Convert(CHAR(7), s.[REPORTING_MONTH], 121) as REPORTING_MONTH
    ,  t.EMPLOYEE
    ,  Sum(CASE WHEN 'FTP'   = [MEDIUM_RCVD] THEN 1 END) AS COUNT_FTP
    ,  Sum(CASE WHEN 'EMAIL' = [MEDIUM_RCVD] THEN 1 END) AS COUNT_EMAIL
       FROM [GPO].[dbo].[DW_SUBMISSION] as s
       JOIN #TEMPActivity as t
          ON  t.SUPPLIER = s.DW_SELLER_NM 
          AND t.INV_YEAR_MO = Convert(CHAR(7), s.[REPORTING_MONTH], 121)
       GROUP BY REPORTING_MONTH, t.EMPLOYEE
       ORDER BY REPORTING_MONTH, t.EMPLOYEE
    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just for giggles and grins, try:
    Code:
    SELECT -- never mix GROUP BY with DISTINCT, it is bad karma!
       Convert(CHAR(7), s.[REPORTING_MONTH], 121) as REPORTING_MONTH
    ,  t.EMPLOYEE
    ,  Sum(CASE WHEN 'FTP'   = [MEDIUM_RCVD] THEN 1 END) AS COUNT_FTP
    ,  Sum(CASE WHEN 'EMAIL' = [MEDIUM_RCVD] THEN 1 END) AS COUNT_EMAIL
       FROM [GPO].[dbo].[DW_SUBMISSION] as s
       JOIN #TEMPActivity as t
          ON  t.SUPPLIER = s.DW_SELLER_NM 
          AND t.INV_YEAR_MO = Convert(CHAR(7), s.[REPORTING_MONTH], 121)
       GROUP BY REPORTING_MONTH, t.EMPLOYEE
       ORDER BY REPORTING_MONTH, t.EMPLOYEE
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Pathetic.

    Been at this for ~ 2 years and I still stink! Ugh, maybe I should go back to project management.

    Thanks Pat, I did change the date back to the substring due to the match up. One date is 201409 and the other is 2014-09 so that's why I was using left and substring.

    Long way to go to handle this DevOps role.

    PS did sign up with Pass and on LinkedIn. There is a dinner and guest speak next week at the local chapter. Pretty excited, I'll keep you posted.

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by VLOOKUP View Post
    Been at this for ~ 2 years and I still stink! Ugh, maybe I should go back to project management.
    Based on the questions that you are asking and the progress that you've made, you are just reaching the point where you are ready to "take off" with SQL. It is always darkest just before the dawn!

    Quote Originally Posted by VLOOKUP View Post
    Thanks Pat, I did change the date back to the substring due to the match up. One date is 201409 and the other is 2014-09 so that's why I was using left and substring.
    Based on the code that you originally posted, there should be dashes in the value, like 2014-09 so that's what I provided. Are you saying that you really wanted 201409 instead???

    Quote Originally Posted by VLOOKUP View Post
    PS did sign up with Pass and on LinkedIn. There is a dinner and guest speak next week at the local chapter. Pretty excited, I'll keep you posted.
    Very cool! PASS and LinkedIn will both do wonders for helping you along, but keep in mind that both of them take a couple of months to really produce benefits. Check out your local (and the online virtual chapters) of PASS, find out how you can volunteer to help. That is the fastest and best way to get involved and to start getting the real value from PASS. LinkedIn takes time too, spread out as an hour or so every week but you have to be consistent over the long term.

    As a side note, did you connect with me via Linked-In yesterday? I had one unexpected connection show up, and I'm leery of it because there were three other profiles with similar profiles and identical pictures yesterday... Then to further spook me, two of those profiles were "remodeled" while I watched (in a few minutes) so I assume that they are being used to phish people for contact information. If you decide to connect, PM me your name so that I accept your invitation!

    -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
  •