Query to Exclude results

Question

Query to Exclude results

radoslavov91

SSC Enthusiast

Points: 128
More actions
January 22, 2020 at 11:07 am

#3717312

I have the following request and I'm not sure how to achieve it SQL. Let's say we have three tables Contacts, Subscription and Products. Let me edit the information and add more context.
We have the following structure: The user buys a Subscription, the Subscription contains a Product. We have two types of Products Trial and Monthly. Users can buy a trial product, and after that, he can decide to buy a monthly one. Our Marketing Team wants to have a results of all users who have Trial subscriptions (let's say we have 5 people), but also if any of these 5 people bought a Monthly Subscription to Exclude it from the results because we don't want to send him promotional emails since he bought a license.
This how currently SQL looks likes:
select cs_subscription.cs_contactyominame as 'Contact Name',
product.name as 'Product Name',
cs_subscription.createdon as 'Created On',
cs_subscription.cs_expirationdate as 'Expiration Date',
case
when cs_subscription.cs_status=110000000 then 'Active'
end as 'Status'
from cs_subscription
join product on cs_subscription.cs_product=product.productid
where product.name='Trial'
and cs_status=110000000
and cs_contactyominame IS NOT NULL
and name IS NOT NULL
order by cs_expirationdate asc
One of the Users in the results - Mebi Fo has also Monthly Subscription, so we want it out of the results so he doesn't receive any Trial emails.
The 2.png screenshot is from our internal system that stores the subscription details for every user.
I hope I explained the scenario well enough? Any ideas on how should I achieve that?Query to Exclude results
Attachments:
You must be logged in to view attached files.

January 22, 2020 at 11:52 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Difficult to help without table DDL or sample data, but try this. You should be able to tweak it if it doesn't work out of the box.

SELECT
s.cs_contactyominame AS 'Contact Name'
,p.name AS 'Product Name'
,s.createdon AS 'Created On'
,s.cs_expirationdate AS 'Expiration Date'
,CASE
WHEN s.cs_status = 110000000 THEN 'Active'
END AS 'Status'
,MAX(name) AS Max
,MIN(name)
FROM cs_subscription s
JOIN product p ON s.cs_product = p.productid
WHERE p.name = 'Trial'
AND s.cs_status = 110000000
AND s.cs_contactyominame IS NOT NULL
AND p.name IS NOT NULL
GROUP BY
s.cs_contactyominame 
,p.name 
,s.createdon
,s.cs_expirationdate 
,CASE
WHEN s.cs_status = 110000000 THEN 'Active'
END
HAVING MIN(p.name) = 'Trial'
AND MAX(p.name) = 'Trial'

John

radoslavov91 SSC Enthusiast Points: 128 More actions · Answer 2

Hi John, i tried this:

select cs_subscription.cs_contactyominame as 'Contact Name',
product.name as 'Product Name',
cs_subscription.createdon as 'Created On',
cs_subscription.cs_expirationdate as 'Expiration Date',
case
when cs_subscription.cs_status=110000000 then 'Active'
end as 'Status',
MAX(product.name) as 'Monthly',
MIN(product.name) as 'Trial'
from cs_subscription
join product on cs_subscription.cs_product=product.productid
where product.name='Trial'
and cs_status=110000000
and cs_contactyominame IS NOT NULL
and name IS NOT NULL
having MIN(product.name)='Trial'
and MAX(product.name)='Monthly'

In this scenarios, MIN will be Trial product and MAX will be Monthly right?

This reply was modified 4 years, 10 months ago by radoslavov91.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

No. Trial comes after Monthly alphabetically. That query can't produce any results for that reason. Surely you're looking for customers who have a Trial but don't have a Monthly? That's why I set MIN and MAX to Trial - did that not work when you tried it?

John

radoslavov91 SSC Enthusiast Points: 128 More actions · Answer 4

Hey John,

i see now, i tried your query but doesn't return anything, just blank results

SELECT
s.cs_contactyominame AS 'Contact Name'
,p.name AS 'Product Name'
,s.createdon AS 'Created On'
,s.cs_expirationdate AS 'Expiration Date'
,CASE
WHEN s.cs_status = 110000000 THEN 'Active'
END AS 'Status'
,MAX('Office 365 - Monthly') AS Max
,MIN('Office 365 - Trial')
FROM cs_subscription s
JOIN product p ON s.cs_product = p.productid
WHERE p.name= 'Office 365 - Trial'
AND s.cs_status = 110000004
AND s.cs_contactyominame IS NOT NULL
AND p.name IS NOT NULL
GROUP BY
s.cs_contactyominame 
,p.name 
,s.createdon
,s.cs_expirationdate 
,CASE
WHEN s.cs_status = 110000000 THEN 'Active'
END
HAVING MIN(p.name) = 'Office 365 - Monthly'
AND MAX(p.name) = 'Office 365 - Trial'

Am I missing something in the lines? I know for sure that there are users having both subscriptions.

Surely you're looking for customers who have a Trial but don't have a Monthly? I'm actually looking to exclude from the query those users who already have Monthly subscriptions.

So basically if you buy Office 365 Trial, our marketing team will start sending you promotional emails because you appear in the results of user with a trials, but if 2 days later you buy Office 365 Monthly, you still will be in the trial list because your trial is still active but you also now have a Monthly Subscription, so we want to exclude you from the emails that we send to the trial users because since you bought Monthly Subscription you don't need this promotional emails telling to Upgrade to Monthly.

If you need more clarification let me know.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

That's not the query I posted, though. Yes, you want customers who have a trial but not a monthly. In other words, the highest product they have is Trial, and the lowest is also Trial. Now, if you have lots of different products that have Trial and Monthly versions, then you'll need to make the query a little more sophisticated - maybe by including product type (Office 365, Oracle, SQL Server, whatever) in the SELECT list and GROUP BY clause. As I said earlier, it's difficult to help without seeing what you can see, so it would make things easier if you could post some table DDL in the form of CREATE TABLE statements, some sample data in the form of INSERT statements, and the results you expect to see based on the sample data.

John

radoslavov91 SSC Enthusiast Points: 128 More actions · Answer 6

Sure John,

I will generate a Table DDL and provide it here so can be easy for you.

Thanks for the assistance so fat and for the clarification on the MIN and MAX statements.

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 7

This looks like a great place to use the EXCEPT statement. EXCEPT is basically the opposite of UNION; UNION joins two sets of data, while EXCEPT subtracts one set from another. You want the set of subscribers who have a trial product EXCEPT the set of users who have a monthly product, so that could work for you.

Here is an introductory article that could help: https://www.sqlservertutorial.net/sql-server-basics/sql-server-except/

drew.allen SSC Guru Points: 76989 More actions · Answer 8

EXCEPT is unlikely to be the best performing solution here, because it requires you to read the table twice and there are other solutions that should only require reading the table once. The best solution will depend on the indexes in use and the density of the orders with respect to the customers. I would investigate GROUP BY, a CTE with ROW_NUMBER(), and CROSS APPLY. If the OP wants tested queries, he should post sample data in a consumable format and expected results.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 9

I would use a NOT EXISTS in some form

;WITH CTE AS
(
    SELECT cs_subscription.cs_contactyominame AS 'Contact Name',
           product.name AS 'Product Name',
           cs_subscription.createdon AS 'Created On',
           cs_subscription.cs_expirationdate AS 'Expiration Date',
           CASE WHEN cs_subscription.cs_status = 110000000 THEN 'Active'
           END AS 'Status',
           product.productid AS 'productid'
      FROM cs_subscription
      JOIN product ON cs_subscription.cs_product = product.productid
     WHERE product.name IN ('Trial','Monthly')
       AND cs_status = 110000000
       AND cs_contactyominame IS NOT NULL
       AND name IS NOT NULL
)
SELECT a.[Contact Name],
       a.[Product Name],
       a.[Created On],
       a.[Expiration Date],
       a.Status
  FROM CTE a
 WHERE NOT EXISTS(SELECT *
                    FROM CTE b
                   WHERE b.[Contact Name] = a.[Contact Name]
                     AND b.productid = a.productid
                     AND b.[Product Name] = 'Monthly')
 ORDER BY a.[Expiration Date] asc

This reply was modified 4 years, 10 months ago by Jonathan AC Roberts.

radoslavov91 SSC Enthusiast Points: 128 More actions · Answer 10

Hi Guys, I'm unable to generate a table for you, but hopefully, the next screenshot will give some highlights.

This is a list of people that have Office 365 product, regardless of its billing cycle (Trial, Monthly, or Annual), there are a feel more further down, but I cut them of the picture.

Now If i say:

select cs_subscription.cs_contactyominame,
product.name,
case
when cs_subscription.cs_status=110000000 then 'Active'
when cs_subscription.cs_status=110000001 then 'Pastdue'
when cs_subscription.cs_status=110000002 then 'Pending Activation'
when cs_subscription.cs_status=110000003 then 'Canceled'
when cs_subscription.cs_status=110000004 then 'Expired'
end as 'Status'
from cs_subscription
join product on cs_subscription.cs_product=product.productid
where cs_contactyominame IS NOT NULL
and product.name='Office 365 Trial'
and name IS NOT NULL 
and cs_status=110000000
order by cs_contactyominame asc

This will return 7 results (meaning that only 7 people have Trial Products)

However, from these 7 people, we have two persons that also have Office 365 Monthly subscription (Manual Created and Marztin Zipov).

How can I execute the same query above (for the users who have Office 365 trial) by excluding from the 7 results, the two guys who actually have Office 365 Monthly?

I'm really sry for the inconvenience way of providing info, and really appreciated the help you are giving me guys. Let me know if you need more information or context.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

I don't understand why you can't generate a table? Here's an outline to show how easy it is:

CREATE TABLE #Subscriptions (
cs_contactyominame <datatypename(nnn)>
,name <datatypename(nnn)>
,Status <datatypename(nnn)>
)

INSERT INTO #Subscriptions
VALUES
('Emil Salha', '- Monthly', 'Active')
,('Emil Salha401', 'Trial', 'Active')
,...

Please make sure your query (the table and column names) matches your sample data as well.

John

Query to Exclude results

Attachments: