I found some inaccuracy in your example table1, the one of Transaction, because you have there all 1 as Transactions' ID(so I don't understand if it is the number of the transaction placed by a customer, but in this case should be unique, or is referring to a transaction type, but also in this case it should be unique, that could be simply a way to be fast and give us an example, but instead an ID in general is really important in a table, both in a Operational one or a DWH,as is used as reference, so I have recreated your case in a more precise and understandable way
--Transactions issued by your Customer
create table TransactionT
( [ID] int,
[TransactionID] int,
[Y] date,
[Month] int ,
[Channel] int ,
[Transaction_Type] varchar(10),
[CustomerID] int
)
--Customer table
create table Customer
( [CustomerID] int,
[Name] varchar(10)
)
-Transactions Type possible
create table TransactionList
( [TransactionID] int,
[Transaction_Type] varchar(10)
)
--then inserting some data in those tables
insert into Customer(CustomerID,Name )
values (1,'First'),
(2,'Second'),
(3,'Third'),
(5,'Jay'),
(6,'PiT'),
(4,'Marco');
insert into TransactionT (ID,TransactionID,Y,Month,Channel,Transaction_Type,
CustomerID )
values (1,1,'2008-11-10',2,4,'Deposit', 2),
(2,2, '2008-11-11',3,4,'Inquiry', 2),
(3,3,'2008-11-14',3,4,'Withdraw', 2),
(4,1,'2017-11-15',3,4,'Deposit', 5),
(5,1,'2017-11-16',3,4,'Deposit', 6),
(6,3,'2017-11-16',4,5,'Withdraw', 6),
(7,1,'2017-11-16',3,4,'Deposit', 2),
(8,2,'2017-11-16',5,6,'Inquiry', 5),
(9,3,'2017-11-18',3,4,'Withdraw', 6),
(10,1,'2017-11-19',3,4,'Deposit', 3);
insert into TransactionList(TransactionID,Transaction_Type)
values (1,'Deposit'),
(2,'Inquiry'),
(3,'Withdraw');
So to answer your first question(How many customers have used specific channel and transaction in Year 2017 month 2), I have made it more general solution, so suitable for any month, but applying simply a where condition you can have the filter for a specific month,check here:
select
count (Transaction_Type) as COUNT_TR,
count (Channel) as COUNT_CN,
count (C.CustomerID) AS COUNT_CS,
Transaction_Type,
Month,
Channel,
C.CustomerID
from
TransactionT as T
join Customer as C
on T.CustomerID = C.CustomerID
group by Transaction_Type, Channel,month, C.CustomerID
Instead your second one,"and how many have not used that transaction type and in which channels." Has some grade of "lacking information", indeed how a customer can have used a channel, if not making a Transaction?
I diveded then the question in 2 steps, to know Customers that did not made any Transaction, and here the SQL for SQL Server:
select CustomerID from Customer
except (
select distinct C.CustomerID from TransactionT C
left join TransactionT T
on T.CustomerID = C.CustomerID
)
This can be obtained also in this way
Instead if want know those Customers that do not made a particular Transaction Method then that is less intuitive and require a subquery, because SQL Server do not support this, but logically is using this.
select distinct C.CustomerID,T.Transaction_Type
from TransactionT T
cross join Customer as C
left join (
select A.TID as TransactionID,A.T as Transaction_Type ,B.C as CustomerID
from
(
select TransactionID TID,Transaction_Type T, C.CustomerID C
from TransactionT T join Customer C
on T.CustomerID = C.CustomerID
) A join
(
select TransactionID TID,Transaction_Type T, C.CustomerID C
from TransactionT T join Customer C
on T.CustomerID = C.CustomerID) B
on A.C = B.C
group by A.TID, A.T, B.C
) sub
on sub.TransactionID = T.TransactionID and sub.CustomerID = C.CustomerID
where sub.TransactionID is null
As you can see here
Hope this helps
