T-SQL search for returning customer

Question

I've got a question, where I'm struggeling with at the moment. I really don't know how to solve this problem, it seems so simple.

I've got a Customer ID, and an Order Date. I only want to show customer, that ordered things before 2015 AND buyed something lets say in the last 10 days.

I created a little test table for that - lets say it's January 2016:

Now there is Customer 1, that did a purchase on January and in year 2010. Ok that fits my need, I want to show him. But customer 2 did a purchase on December last year, so he is a not a "returning" customer, but a customer that often buys my things. I dont want to show him.

I tryed something like this, but it didn't work:

SELECT [Kunden_ID],Bestellung
FROM [Immo].[dbo].[TEST] AS A
WHERE (Bestellung >=DATEADD (day,-10,getdate()) 
AND Bestellung <= DATEADD (month,-12,getdate()))

Answer 1

You need two separate queries. The first finds those customers that bought something in the last 10 days. The second uses the exists query to find those same customers (join using ID) that bought more than 12 months ago.

Try this:

SELECT [Kunden_ID],Bestellung
FROM [Immo].[dbo].[TEST] AS A
WHERE (Bestellung >=DATEADD (day,-10,getdate())) 
and exists (
select 1
from [Immo].[dbo].[TEST] AS B
where a.[Kunden_ID] = b.[Kunden_ID]
AND b.Bestellung <= DATEADD (month,-12,getdate())
)

Another way to do this uses a Common Table Expression (CTE). It's a little easier to see the different queries.

With Get10Days as (
SELECT [Kunden_ID],Bestellung
FROM [Immo].[dbo].[TEST] AS A
WHERE (Bestellung >=DATEADD (day,-10,getdate())) 
)


select b.Kunden_ID
from [Immo].[dbo].[TEST] AS B
join Get10Days as A on a.Kunden_ID = b.Kunden_ID
where b.Bestellung <= DATEADD (month,-12,getdate())