# Thread: Union two sets (consolidating results)

1. Registered User
Join Date
Oct 2014
Posts
291

## 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.

## "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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

4. Registered User
Join Date
Oct 2014
Posts
291
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by VLOOKUP
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!

Originally Posted by VLOOKUP
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???

Originally Posted by VLOOKUP
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

#### Posting Permissions

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