How do I pull data based on consecutive purchase criteria

Hi,
We would like to find customers who have made 10 consecutive year purchases with us. The date range wouldn't be limited. Meaning, we are looking between the years of 2000 and 2015, so not limited to the past 10 years only.

A 10 year consecutive purchase would mean the patron purchased tickets each year between 2002-2011 for example. If a patron purchased each year between 2002-2007 (5 years), skipped 2008 and purchased each year between 2009-2013, (total of 11 years in length but two sets of 5 years) we would not want to include that person.

I hope that makes sense. Thoughts?
rwaterzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
This is going to require more than air code so please put some time into providing schema scripts, sample data, and expected output.
0
rwaterzAuthor Commented:
Hi Brian - Thanks for the quick reply, happy to provide more info. Small query below has two tables and image directly below that is sample data it provides; LT_SUB_HIST which has the customer ticket purchase info and TR_SEASON which I am filtering on.

select
	sh.*
	,s.id
	,s.description
	,s.type
	,s.start_dt
	,s.end_dt
	,s.fyear
	,s.inactive
from
	LT_SUB_HIST sh
	join TR_SEASON s on s.id = sh.season
where
	s.type = 1

Open in new window

Sample Working Data
Desired output would be simple:
expected output
Notice in the sample data a field named "location". This field has (mainly) three possible records: "New", "Renew", or "Recapture {fiscal year}". New means absolute first year as a subscriber. Renew means they were either "New" or "Recapture" the year prior. Recapture{fiscal year} means they renewed but not directly from the year prior, thus the fiscal year of their last activity is present.

After writing this I realize it will be incredibly unlikely that anyone who was a recapture at any point won't fit the 10 consecutive year data pull were looking for.
0
Scott PletcherSenior DBACommented:
This is a type of gaps-and-islands problem.  Thus, there's a quick solution; here is the generalized format:


SELECT customer_no, COUNT(*) AS consec_year_ct, order_group + 1 AS first_year
FROM (
    SELECT customer_no, order_year -
        ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY order_year) as order_group
    FROM (
        SELECT DISTINCT sh.customer_no, YEAR(sh.order_dt) AS order_year
        FROM LT_SUB_HIST sh
        --INNER JOIN ...
        --WHERE ...
    ) AS derived1
) AS derived2
GROUP BY customer_no, order_group
HAVING COUNT(*) >=10
ORDER BY customer_no, order_group


For example, with some dummy/test data included, generated using a "standard" tally table of sequential numbers from 0 to at least 15:

select customer_no, order_group + 1 as first_year, count(*) as number_of_years
from (
    select customer_no,
    order_year - ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY order_year) as order_group
    from (
    select distinct customer_no, year(order_dt) as order_year
    from (
        select 1 as customer_no, DATEADD(year, t.tally, '20000505') as order_dt
        from dbo.tally t
        where t.tally between 0 and 14
        union all
        select 2 as customer_no, DATEADD(year, t.tally, '20010505') as order_dt
        from dbo.tally t
        where t.tally between 0 and 8
        union all
        select 2 as customer_no, DATEADD(year, t.tally, '20110505') as order_dt
        from dbo.tally t
        where t.tally between 0 and 8
    ) as derived1
    ) as derived2    
) as test_data
group by customer_no,order_group
having count(*) >=10
order by customer_no
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Brian CroweDatabase AdministratorCommented:
I have a pretty good understanding of what you are trying to accomplish.  What would make this go faster is some useful sample data.  A picture of data isn't very helpful.  If we are working off the same data then we can bounce back and forth working toward a solution without worrying about the data being the issue.

Obviously don't include anything proprietary or private.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
0
rwaterzAuthor Commented:
Scott - the generalized format comes extremely close to what I am looking for. There are some slight adjustments I would need to make but this looks like it will work quite well. I am testing this now, more to follow.
0
rwaterzAuthor Commented:
Scott,
I am not sure I understand the "tally" table or its purpose. Can you explain?
0
Scott PletcherSenior DBACommented:
That was just to generate sample data for me to test with.

A tally table is literally simply a table of sequential numbers from 0 to (typically) 1 million.

This is a remarkably useful device for processing many things, including adjusting dates, duplicating rows, finding substrings within a long string, etc..
0
Scott PletcherSenior DBACommented:
CREATE TABLE dbo.tally (
    tally int NOT NULL,
    CONSTRAINT tally__PK PRIMARY KEY CLUSTERED ( tally )
    )
INSERT INTO dbo.tally VALUES(0);

;WITH
cteTally10 AS (
    SELECT 0 AS tally UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
    SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
cteTally100 AS (
    SELECT 1 AS tally
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT 1 AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
),
cteTally1Mil AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.tally) AS tally
    FROM cteTally100 c1
    CROSS JOIN cteTally10K c2
)
INSERT INTO dbo.tally
SELECT tally
FROM cteTally1Mil
ORDER BY tally

Open in new window

0
rwaterzAuthor Commented:
Excellent Scott, thank you!
0
Scott PletcherSenior DBACommented:
You're welcome!  But the original idea/technique was Itzik Ben-Gan's.  Btw, ANY/ALL of his books on SQL/TSQL are awesome.
0
rwaterzAuthor Commented:
That's really great, thanks for sharing!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.