Finding a customer who has visited two venues within a time period
-
I need to find a customer who has visited two of our sites between a given time period.
The table holds the following columns:
Venueid, UsageTime, Workstation, Custid
I am trying to get a list of custid's that have visited both venueid 5 & 12 between the time range below. I tried this query but it returned 1500 rows.
select * from Txusage
where UsageTime between '2005-06-14 11:59' and '2005-06-14 17:00' and Venueid in (5,12)
order by custid
This returns some 1500 rows, but what I would really like if possible is just to have a list of custids who have visited both venueid's within that time period ... is this possible?
Can anyone help me?
-
So close yet so far
:select custid from dbo.Txusage
where UsageTime between '2005-06-14 11:59' and '2005-06-14 17:00' and Venueid in (5,12)
group by custid
having count(*) = 2 --number if items in the in list
--order by custid useless with the group by
-
Select distinct a.Custid
From Txusage a
Join Txusage b
On b.Custid = a.Custid
Where a.venueid = 5
And a.usagetime between '2005-06-14 11:59' and '2005-06-14 17:00'
And b.venueid = 12
And b.usagetime between '2005-06-14 11:59' and '2005-06-14 17:00'
Remi I think you solution might return a custid that visits the same venue twice but never the second venue?
-
You're right... first time I see this situation.
Always something else to learn
.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply