February 10, 2017 at 5:33 am
Disclaimer: New to SQL please be patient.
So:
Table A:
Contains list of customers that have recently made a purchase. It also includes Customer I.D,details of purchase,their region, amount they spent etc.
Now what I would like to do is:
Return all the customers in table A that have:
1.Cancelled their subscription (in the last 35 days) - subscription details are in another table (table B for ease)
2. Put their account on hold (not cancelled their subscription but a temporary hold) all the temporary holds are in Table C.
3.Placed an order to purchase another a different subscription or renew - again all stored in a different table.
Essentially I was looking to utilise some form of Case expression where if any of the above conditions is met then 1 else 0.
I would post a code but everything I have tried hasn't worked or returns errors.
Thanks in advance
February 10, 2017 at 5:40 am
Can you provide us with some DDL and (obscured) sample data please? We have little idea from your post what your structure looks like so giving an answer for your needs is quite difficult. See the link in my signature about how to supply.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 10, 2017 at 5:54 am
This is about as far as anyone can help until you've followed up Thom's post:
SELECT columns from c, expressions from sd, th and o
FROM Customers c
LEFT JOIN SubscriptionDetails sd
ON sd.something = c.something
LEFT JOIN TemporaryHold th
ON th.something = c.something
LEFT JOIN Orders o
ON o.something = c.something
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 10, 2017 at 7:31 am
With Customers as(
select
Customer_name,
Account_number,
cast (Purchase_date as date) AS DateofPurchase,
Subscription_type
Amount
from table_a
where Customer_name != 'Test'
)
,
AccountDetails as
(
select
a.AccountNumber,
lastpaymentdate,
BlockCode01,
BlockCode02,
BlockCode1Date,
Blockcode2date,
CurrentBalance,
usercode01,
usercode02,
usercode03
from firstvision.dbo.vw_AVI2BSBaseSegment1Current a
join firstvision.dbo.vw_AVI2BSBaseSegment2Current b
on a.accountnumber=b.accountnumber
)
,
Refinedlist as
(
select
Customer_name,
Account_number,
DateIdentified,
lastpaymentdate,
BlockCode01,
BlockCode02,
BlockCode1Date,
BlockCode2date,
CurrentBalance,
usercode01,
usercode02,
usercode03
from Customers a
join AccountDetails b
on a.account_number=b.accountnumber
)
select*
from
Refinedlist a
February 10, 2017 at 7:37 am
Apologies for the earlier amateur post,
So from the results set that is returned from the CTE's, I want verify those customers against certain conditions.
So if any customer from CTE result set meets any of the following conditions:
1.No payment
2.No prospective purchase
3.Temporary hold on account
4. Change in purchase
I want to flag these Customers if any of the above conditions are met. To clarify all the above conditions are stored in four different tables. I feel like I need to use a case when with a join but I couldn't be sure
thanks in advance
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply