CODEWITHSUNDEEP
sqlsql-server
JBryanB
JBryanB

Reputation: 33

SQL To Get Me Repeat Visits

I have a table of vehicle visits. The vehicle tag number (PlateID) is stored in it along with a timestamp (TimeStamp).

I need to query the table by date range and get only the records where a vehicle has visited multiple times. I want to show the plate number, the number of visits, and the last time it visited.

Thanks in advance.

Edit: sorry, too hastily done. I'm using SQL Server Express.

I've tried something like this:

Select PlateID1, count(1) as VisitCount 
From ActivityLog
Where (TimeStamp > '10/19/1977' And TimeStamp < '12/12/2017')
Group By PlateID1
Order By VisitCount Desc

But this returns all records, where I just want the ones that have visited more than once. Also, I can't get additional columns with this query, like TimeStamp, Location, etc etc.

Upvotes: 0

Views: 168

Answers (1)

StephaneM
StephaneM

Reputation: 4899

You were almost there. Try this

Select PlateID1, count(1) as VisitCount from ActivityLog
where (TimeStamp > '10/19/1977' and TimeStamp < '12/12/2017')
Group by PlateID1
having count(PlateID1) > 1
Order BY VisitCount Desc

See https://msdn.microsoft.com/fr-fr/library/ms180199(v=sql.120).aspx

Upvotes: 2

Related Questions