I have two not very complex SELECT queries that are not returning any output when i run in SSMS. When I run both of these queries separately, i just see "Executing query" and it stays like that for a really long time.
[database1].[dbo].[customer] is where all customer data is stored
[database2].[mail].[emailsent] is where email sent log is stored, basically which customer received which email. In this table, row only exists when an email was sent to a customer. If it was not sent then there shouldn't be any row.
What i am trying to do - I am trying to get all customer count (& customer keys as well) for all customers who did not receive specific emailids so i think i will need have to 2 conditions in where clause since these customers may exists with other emailids or they may not exists at all in the emailsent table.
here is my query 1:
-- query1 to get customer count
SELECT count(DISTINCT [customerkey]) AS My_Count
FROM [database1].[dbo].[customer]
WHERE
[signupdate] BETWEEN '2019-01-01' AND '2019-05-05' AND [type] = 1
AND
(
([accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent]))
OR
(
[accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent] where [id] IN (
'10',
'11',
'12',
'13',
'14',
'15',
'16') )
)
)
here is my query 2
-- query2 to get all customers
SELECT DISTINCT [customerkey]
FROM [database1].[dbo].[customer]
WHERE
[signupdate] BETWEEN '2019-01-01' AND '2019-05-05' AND [type] = 1
AND
(
([accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent]))
OR
(
[accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent] where [id] IN (
'10',
'11',
'12',
'13',
'14',
'15',
'16') )
)
)
Try something like this instead if you only need to know if the customer has no emails.
SELECT count(DISTINCT [customerkey]) AS My_Count
FROM [database1].[dbo].[customer] c LEFT JOIN [database2].[mail].[emailsent] e ON c.accountid = e.accountid
WHERE
[signupdate] BETWEEN '2019-01-01' AND '2019-05-05' AND [type] = 1
AND e.id IS NULL
If you need to know if the customer does not have specific emails then this
SELECT count(DISTINCT [customerkey]) AS My_Count
FROM [database1].[dbo].[customer] c LEFT JOIN [database2].[mail].[emailsent] e ON c.accountid = e.accountid AND e.id IN ('10','11','12','13','14','15','16')
WHERE
c.signupdate BETWEEN '2019-01-01' AND '2019-05-05' AND c.type = 1
AND
e.id IS NULL
Hi @btrimpop
I am trying to get customers who did not receive specific emailids so my original approach is to have 2 conditions with OR in where clause since these customers may exists with other emailids (who received other emails) or they may not exists at all in the emailsent table (who did not receive any email at all).
Is this correct approach?
Now, is my original approach:
WHERE (
([accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent]))
OR
(
[accountid] NOT IN (select distinct [accountid] from [database2].[mail].[emailsent] where [id] IN (
'10',
'11',
'12',
'13',
'14',
'15',
'16') )
)
)
Same as your approach ?
SELECT count(DISTINCT [customerkey]) AS My_Count
FROM [database1].[dbo].[customer] c LEFT JOIN [database2].[mail].[emailsent] e ON c.accountid = e.accountid
WHERE c.signupdate BETWEEN '2019-01-01' AND '2019-05-05' AND c.type = 1
AND
(
e.id NOT IN ('10','11','12','13','14','15','16')
OR
e.id IS NULL
)
You shows each separately so I am a little confused and you are doing IN (for checking ID list) instead of NOT IN.
Could you please clarify?
To give you an example...if you are a contact and received email A, B & C from us...Now your record will exists in emailsent table. If I have a contact and I never received any email then my record won't exists in emailsent table. Now, if i want to count contacts who did not receive email X, Y & Z...I will have to contact myself, who did not receive any email+ you, who received other email (anything other than X, Y & Z). This is what i am trying to do.
You want a count of all contacts who did not receive email X,Y, or Z. Your last query above still counts contacts that received X,Y, or Z emails it just doesn't count the X,Y or Z emails. If you move the NOT IN list to the LEFT JOIN criteria and get rid of the NOT the result will have all contacts and only those contacts that had those emails will have an e.id that is not null. Thus the e.ID IS NULL in the where clause will count only contacts that had no emails or did not have X,Y,Z emails.
SELECT count(DISTINCT [customerkey]) AS My_Count
FROM [database1].[dbo].[customer] c LEFT JOIN [database2].[mail].[emailsent] e ON c.accountid = e.accountid AND e.id IN ('10','11','12','13','14','15','16')
WHERE c.signupdate BETWEEN '2019-01-01' AND '2019-05-05' AND c.type = 1
AND
e.id IS NULL
Understood - Thank you so much