SQL Server get records per minute? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server get records per minute?

Author

Topic

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 07:01:03

this is an odd one. but should be simple for you gurus as my mind is bruised!

Lets say there is a voting system.
a user can multiple vote.
however for a "security measure" (which it isnt), they want to remove any votes by a single user where the condition is as follows:

any votes more than 16 per minute are to be removed
any votes more than 8 per minute are to be removed
any votes more than 6 per minute are to be removed

for that single user that has multiple votes.

how can this be achieved in SQL Server?

so for each email address in the table, check to see how many votes they have placed within a minute - if they have placed more than x votes in that minute (16, 8 or 6) then show me those votes (then eventually using this list, remove it from the database)

using SQL Server 2005

thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 07:07:28

If you can retrieve the users who have more than 6 votes per minute then this also gives you the users with more than 8 or more than 16 votes.
So I don't understand this clearly.

Can you give table structure, example data and wanted output?

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 07:09:07

quote:
Originally posted by tech
any votes more than 16 per minute are to be removed
any votes more than 8 per minute are to be removed
any votes more than 6 per minute are to be removed

I'm unsure why you specified 3 different quantities here. If the first is true, then the others will also be true. (ie. if there's more than 16, then there's more than 8 and 6).

Anyway, a question. Say you have the following vote times for a single user, would they be considered more than 6 within 1 minutes?

10:09:40
10:09:47
10:09:55
10:10:03
10:10:12
10:10:17
10:10:22

If yes, then the query to identify them is going to be a lot more complex. Also, can you post a DDL script for the table where the data is stored, including the primary key.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 07:10:32

quote:
Originally posted by webfred

If you can retrieve the users who have more than 6 votes per minute then this also gives you the users with more than 8 or more than 16 votes.

PSST. It's the other way round.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 07:18:53

Give me all users with more than 6 votes in a minute gives me all what I may want.
Give me all users with more than 16 votes in a minute will give not 7,8,9,10,11,12,13,14,15,16.

So it depends on requirement

No, you're never too old to Yak'n'Roll if you're too young to die.

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 07:28:33

haha thanks folks.

well im just going by what the client wants really. i dont get a say on many things here!

the table is just a simple flat table with these fields:

email
dateVoted
ID (PK int, NOT NULL)
Name

and thats it!

so, I need to get records where if the current email address has more than x amount of votes per minute, then give me the list and delete the remaining records of more than x amount of votes (ie if they voted 20 times in a minute, then remove the 4 entries as 16 is the max they can vote per minute)

lets focus on this one :)

DBA in the making: no is the answer to your question :-)

so if they voted 20 times and the time was 10:00 then 4 entries should be removed.
if they voted 10 times in 10:01 then this is valid
if they voted 16 times in 10:02 then this is valid

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 07:35:09

Which SQL Server version?
Is dateVoted a datetime column?
What if email and Name are not the same?

No, you're never too old to Yak'n'Roll if you're too young to die.

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 07:38:51

it doesnt matter about email and name. just focusing on the email :-)

SQL Server version: 2005
datevoted is a datetime column

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 07:49:46

Try this:

DELETE 
FROM Votes
WHERE ID IN ( 
	SELECT ID FROM (
		SELECT ID, ROW_NUMBER() OVER (PARTITION BY email, DATEDIFF(mi, 0, dateVoted) ORDER BY ID) AS Row
		FROM Votes) z 
	WHERE Row > 16 )

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 07:55:28

Thanks. will give that a bash. Anyone else please feel free to post your versions! much appreciated.

now how to validate this..... doh!

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 07:58:06

right ok, my mind is really buzzing and is congested.

this query, what does it do? gets me the list of records that have more than 16 records per minute for an email address?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 08:02:24

quote:
Originally posted by tech

right ok, my mind is really buzzing and is congested.

this query, what does it do? gets me the list of records that have more than 16 records per minute for an email address?

Yes, and then it deletes the records that were added after the first 16, for any given minute/email.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 08:05:15

thanks. really appreciate this. i think this is working as im comparing against my own list..... :-)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 08:14:03

With some testdata you can check it:

declare @Votes table(Id int identity(1,1),
                    email varchar(255),
                    dateVoted datetime,
                    Name varchar(255))
insert @Votes
select 'a@b.com','2010-04-27T13:59:00.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:01.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:02.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:03.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:04.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:05.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:05.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:05.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:05.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:09.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:10.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:11.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:12.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:13.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:14.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:15.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:16.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:17.000','Fred' union all
select 'a@b.com','2010-04-27T13:59:18.000','Fred' union all
select 'b@c.com','2010-04-27T13:58:18.000','WebFred' union all
select 'a@b.com','2010-04-27T13:59:19.000','Fred' 

select * from @Votes

DELETE 
FROM @Votes
WHERE ID IN ( 
	SELECT ID FROM (
		SELECT ID, ROW_NUMBER() OVER (PARTITION BY email, DATEDIFF(mi, 0, dateVoted) ORDER BY ID) AS Row
		FROM @Votes) z 
	WHERE Row > 16 )

select * from @Votes

No, you're never too old to Yak'n'Roll if you're too young to die.

tech
Starting Member

32 Posts

Posted - 2010-04-27 : 08:50:20

you are a legend!

thanks folks! works great!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-27 : 08:57:08

Yes "DBA in the making" is maybe a legend.
But I am only an old rocker.

You're welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-27 : 09:53:15

This will do the same thing, but will run faster that the other one.

DELETE z 
FROM (
		SELECT ID, ROW_NUMBER() OVER (PARTITION BY email, DATEDIFF(mi, 0, dateVoted) ORDER BY ID) AS Row
		FROM Votes) z 
	WHERE Row > 16

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources