Audit Query

[Onyxpurr]

I need to create a query to return 5% of all items matching a certain critera. e.g.

5% of records for Joe on 10/16
5% of records for Jane on 10/16
5% of records for Bob on 10/16

I'm fairly green when it comes to sql. I know how to use TOP N PERCENT and I know how to create temp tables in a stored procedure. I just don't know how to get 5% of each user for that date. (Rather than 5% of all the records for that date.)

Please help!! Thank you!!

 

[SamGarland]

Here is an example solution to this type of problem I have made using the Northwind database, you can add additional selection criteria to the subquery as needed:

use Northwind
select a.*
FROM
(select TOP 10 PERCENT * from orders) as a
JOIN orders b
ON b.CustomerID = a.CustomerID
ORDER BY b.CustomerID

 

[maswien]

That helps! I don't know there is a 'top percent' eithor.

I thought it would be:

drop table #temp1
select * into #temp1 from test1 where thekey = 1
-- get the rows for some condition
alter table #temp1 add rid int identity(1,1)
go
select thekey, fname from #temp1
where rid <= ((select count(*) from #temp1 )/20)

 

[maswien]

Onyxpurr,

SQL Server will take care of all your criteras first and then return the 5% of them, so you can add whatever filters
you like, date and user...

 

[Onyxpurr]

Thank you!! I'll try this out.

 

[Onyxpurr]

It's still selecting a total of 5% rather than 5% from each user. I even tried altering the joins, but to no avail. Thanks anyway!

 

[SQLSister]

This is one case where you might have to use a cursor. Write the statement to get the specific top 5% from one user by adding a where USerID = @userId and insert to a tem p table or table variable. Use a cursor to loop through all the distinct usert IDs adding each to the temp table as it goes through and chooses the top 5 % for each user.

NOw to make life more difficult, i'm going to point out that you will always get the same records if you do this, the first 5% of the daily records for that person. I fyou need to pick random records that day for audit purposes (which is more commonly the true requirement in choosing records to audit), then you need to do something more complex. You will need 2 temp tables. The first one will have the fields you need plus a GUID field which will autofill in a random id number for each record when you add it to the table. Insert your records for each user one at a time into this table, order by guid and then take the top 5% and copy to the temp table which holds the final results. Then clear the first table and insert the records for the next userID and do the same thing runnning trhrough each ID with a cursor.

Don;thave time to actually write any of this code, sorry. Good luck.

 

[Onyxpurr]

No, that's okay. I understand, I was just hoping I wouldn't have to use temp tables. But that's okay.

I do have other queries that I create random selections on. In that case, I parse out another field and convert it to INT then randomize it.

Thanks again!!!

 

[JamesLean]

I think you can use a correlated subquery to achieve the desired result. Say you have your table set up like this:

Payments
User
Date
Amount

For a particular date you want to return the top 5% highest amounts for each user:

SELECT user, amount
FROM payments p
WHERE amount IN (
  SELECT TOP 5 PERCENT amount
  FROM payments
  WHERE user = p.user
    AND date = '20031030'
  ORDER BY amount DESC
)
ORDER BY user, amount DESC

--James