Assist with query please

Transact-SQL
1

Hi everyone

I have a query that I am not sure how to write.

The first part of the query is to find all guys that logged into an Online app (channel = 1BB)

select **
** distinct
** round(logon_date/100,0) as PERIOD,**
** a.ucn,**
** channel**
** from **
totus_fct.fct_dbanking_logon_dly a **
** left join totus_dm.dim_ol_profile_v b **
** on a.ucn = b.ucn and a.co_cde = b.co_cde
where **
** logon_date between 20180501
** and 20180630 **
** and a.channel = ('1BB')**
** and a.co_cde = 15**
** and b.curfn_status = '1'**
** and b.sso_status = '1'**
** and b.simple = '1'**
** and a.platform_protocol = 'ZOB'**

The second part looks at exactly the same tables just for Channel = 1SA (this is an Android App)

select distinct
** round(logon_date/100,0) as PERIOD,**
** a.ucn,**
** channel,**
** 'logon_app' as Type**

** from **
totus_fct.fct_dbanking_logon_dly a **
** left join totus_dm.dim_ol_profile_v b **
** on a.ucn = b.ucn and a.co_cde = b.co_cde
where **
** logon_date between (vRptStartDate) ** ** and (vRptEndDate) **
** and a.channel = ('1SA')
** and a.co_cde = 15**

Now I want to know

  1. which guys have BOTH - but I dont know how to write it
  2. Which guys have 1SA but not 1BB
  3. Which guys have 1BB but not 1SA

All help will be appreciated

Thanks.

2

please provide DDL, sample data and expected results

3

you can use except operator between two queries
Except returns all rows from first result set that are not in second result set.
As per your condition it will return all the guys who are in 1BB channel and not in 1SA channel .
Then reverse the queries and use except operator that would return all the guys who are in ISA channel and not in IBB channel.

And in order to get all the guys who are in both IBB channel and ISA channel you need to use intersect operator between the above two queries

4

Thank you,

I will need to read up about Except - never used it before.

Thanks for your response

5

The counterpoint to except is intersect, which will return all rows that are in both result sets. So in simplified terms:

Select users From #With1SA
Intersect Select users From #With1BB

Would be those the have both.

Select users From #With1SA
Except Select users From #With1BB

Would be those with only 1SA

Select users From #With1BB
Except Select users From #With1SA

Is those with only 1BB

And if you need something more creative, then it just boils down to selecting the right JOIN conditions, for example:

Select 
    IsNull(A.username, B.username) As username,
    Case
        When A.username Is Null Then '1BB Only'
        When B.username Is Null Then '1SA Only'
        Else 'Both'
    End
From (Select username From #With1SA) A
Full Join (Select username From #With1BB) B 
    On A.username = B.UserName
6

Thank you Andy