CODEWITHSUNDEEP
sql-serverandroid-activity
user3487243
user3487243

Reputation: 193

SQL Select Highest Surge of Activity in period of time

I am working on a database dealing with information regarding red-light violations from the beginning of the year until present-day.

The table I'm working with is Violations[TicketID, CameraID, DateOfViolation]

I want to find a CameraID with the most amount of tickets serviced (which amounts to a new entry in Violations) in, say, the last 7 days. If there is no activity in the last 7 days, I want null to be returned (i.e. don't open the range to be the last 14 days).

What is the command for this? I'm not sure how to even begin adding constraint for past 7 days. I am connecting to a Microsoft SQL Server.

Upvotes: 0

Views: 58

Answers (1)

juergen d
juergen d

Reputation: 204784

select top 1 CameraID
from violations
where DateOfViolation >= dateadd(day, -7, getdate())
group by CameraID
order by count(*) desc

Upvotes: 1

Related Questions