Need to see if acustomer has visited the same store

Question

Post reply

Need to see if acustomer has visited the same store

24tjf

Old Hand

Points: 351
More actions
December 18, 2009 at 2:16 pm

#137769

Example: Need to see if the customer's last 2 out of 3 visits were at the same store.
StoreID: MemberID: DateVisited:
888 100100 2009-01-25
888 100100 2009-02-15
999 200200 2009-07-01
777 200200 2009-09-25
555 200200 2009-10-30
888 300300 2009-01-25
888 300300 2009-02-01
777 300300 2009-03-01
333 300300 2009-04-15
With the above data MemberID: 100100 would meet the above criteria because 2 out of the last 3 visits were at the same store.
I have tried derived tables, temp tables, etc but not coming up with a solution.
Any ideas?

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Based on the available information, I did the following:

Create Table #StoreVisit (StoreID int, MemberID int, DateVisited datetime)

go

Insert into #StoreVisit(StoreID,MemberID,DateVisited)

Select 888,100100,'2009-01-25'

Union All

Select 888,100100,'2009-02-15'

Union All

Select 999, 200200,'2009-07-01'

Union All

Select 777, 200200, '2009-09-25'

Union All

Select 555, 200200, '2009-10-30'

Union All

Select 888, 300300, '2009-01-25'

Union All

Select 888, 300300, '2009-02-01'

Union All

Select 777, 300300, '2009-03-01'

Union All

Select 333, 300300, '2009-04-15'

;

With custvisits as (select memberid,storeid, row_number() over (partition by memberid,storeid order by memberid) as VisitCount

from #StoreVisit)

Select Memberid,StoreID

From custvisits

Where VisitCount >=2

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

24tjf Old Hand Points: 351 More actions · Answer 2

Thanks for the help.

If I am selecting from the temp table, i.e. MemberID: xxx has had 4 visits (all at the same store) [Not in the example]

Then the query produces 3 results. I also tested with other examples and the query is always one less then the temp table; make sense?

So I put a DISTINCT in the query:

With custvisits as (select memberid,storeid, row_number() over (partition by memberid,storeid order by memberid) as VisitCount

from #StoreVisit)

Select DISTINCT Memberid,StoreID

From custvisits

Where VisitCount >=2

Wondering if I am omitting any results by doing the distinct?

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 3

I don't think Jason's query will work.

This might not either as I haven't tested it.

;WITH cteVisit AS (

SELECT

MemberID,

StoreID,

ROW_NUMBER() OVER (PARTITION BY MemberID ORDER BY DateVisited DESC) AS VisitNumber

FROM #StoreVisit

)

SELECT MemberID, StoreID, COUNT(*)

FROM cteVisit

WHERE (VisitNumber <= 3)

GROUP BY MemberID, StoreID

HAVING (COUNT(*) >= 2)

--Andrew

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

24tjf (12/18/2009)
Thanks for the help.
If I am selecting from the temp table, i.e. MemberID: xxx has had 4 visits (all at the same store) [Not in the example]
Then the query produces 3 results. I also tested with other examples and the query is always one less then the temp table; make sense?
So I put a DISTINCT in the query:
With custvisits as (select memberid,storeid, row_number() over (partition by memberid,storeid order by memberid) as VisitCount
from #StoreVisit)
Select DISTINCT Memberid,StoreID
From custvisits
Where VisitCount >=2
Wondering if I am omitting any results by doing the distinct?

The query groups memberid and storeid together and then looks for the rowcount. The distinct should not have an effect since we are only looking at those two criteria and grouping the member to the store and producing a count.

One thing that is not done in this query is the comparison of 2 of last 3 visits at the same store. I omitted that leaving it for you to try. It should be pretty straight forward to figure that part out.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

Jason's query is just off. Andrew's is right. At least based on the test data provided.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

When tested against the data provided I got distinct records on my end. I scaled the testing up and started seeing what you guys saw.

Here is the revised:

;

With custvisits as (select memberid,storeid, row_number() over (partition by memberid,storeid order by memberid) as VisitCount

from #StoreVisit)

, totalcustvisits as (select memberid, COUNT(MemberID) as MemVisitCount

From #StoreVisit

Group by Memberid

)

Select c.Memberid,StoreID,MAX(c.visitcount) as StoreVisit,MAX(t.MemVisitCount) as MemVisit

From custvisits c

Inner Join totalcustvisits t

On t.MemberID = c.MemberID

Where (t.MemVisitCount >= c.VisitCount)

And (cast(c.VisitCount as float)/cast(t.MemVisitCount as float) * 100) >= CAST(60 as float)

Group By c.MemberID,StoreID

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

BTW...Thanks Jack and Andrew for pointing it out.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

No problem. Of course I was an idiot and didn't completely read Andrew's post until after I had re-written the whole thing EXACTLY as he had :crying:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

I was initially leaving out the 2/3 comparison due to this looking like a homework assignment. I was hoping to leave out just enough he would finish it up.

But since they found errors with it, I had to finish it out.

I guess it is better to have multiple solutions and multiple people writing it the same way (concurs that it will work);-)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

24tjf Old Hand Points: 351 More actions · Answer 10

Thank you all very much for the help. It is greatly appreciated.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events