Reduce X% records - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Reduce X% records

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 09:05:01

Hello,

This is my table structure - BanTbl

Id
BanId
DateAdded
Type

This is the sample data

ID      BanId   DateAdded               Type
195769	1	2009-04-28 09:02:32.923	0
195768	1	2009-04-28 09:02:25.957	0
195767	1	2009-04-28 09:02:15.800	0

Each day, I receive around 10,000 new Id's. I want to do the following. For each day after the 19th of April, I want to delete 45% random Id's for each day whose type = 1

How can I write the query for this?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:15:22

[code]DELETE
FROM (
SELECT ID,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded) ORDER BY NEWID()) AS recID,
COUNT(*) OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded)) AS maxID
FROM TallyNumbers
WHERE Type = 1
) AS f
WHERE 0.45E * recID <= maxID[/code]

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 09:32:55

Thanks Peso,

Where can I write the condition that the date is greater than 19th April 2009?

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 09:35:18

Can I also want to run this query seperately for each date.

For eg: I first delete 45% for April 20th, then I run the query again and delete 45% for April 21st and so on..Is this possible in SQL Server?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:35:19

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 09:43:44

Thanks, when I run this query, I get this error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'AS'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:47:02

[code]DELETE f
FROM (
SELECT ID,
ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded) ORDER BY NEWID()) AS recID,
COUNT(*) OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded)) AS maxID
FROM BanTbl
WHERE Type = 1
AND DateAdded >= '20090421'
AND DateAdded < '20090422'
) AS f
WHERE 0.45E * recID <= maxID[/code]

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 10:06:10

Peso..I ran this query and I lost all records..not just 45%

Before running this query, the number of records were 11690..after running this query, the records are 0

select Count(*) from BanTbl
where DateAdded >= '20090421'
AND DateAdded < '20090422' and type = 1

return 0

What could have gone wrong?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 10:50:17

I'll set up a test bench soon.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 10:58:30

Works for me. I fixed it by exchanging maxID and recID in the WHERE clause.

DECLARE	@Sample TABLE
	(
		ID INT,
		DateAdded DATETIME,
		Type TINYINT
	)

INSERT	@Sample
SELECT	 1, '20090427', 1 UNION ALL
SELECT	 2, '20090427', 1 UNION ALL
SELECT	 3, '20090427', 1 UNION ALL
SELECT	 4, '20090427', 0 UNION ALL
SELECT	 5, '20090427', 0 UNION ALL
SELECT	 6, '20090427', 0 UNION ALL
SELECT	 7, '20090428', 1 UNION ALL
SELECT	 8, '20090428', 1 UNION ALL
SELECT	 9, '20090428', 1 UNION ALL
SELECT	10, '20090428', 0 UNION ALL
SELECT	11, '20090428', 0 UNION ALL
SELECT	11, '20090428', 0

SELECT		*
FROM		@Sample
ORDER BY	ID

DELETE	f
FROM	(
		SELECT	ID,
			ROW_NUMBER() OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded) ORDER BY NEWID()) AS recID,
			COUNT(*) OVER (PARTITION BY DATEDIFF(DAY, 0, DateAdded)) AS maxID
		FROM	@Sample
		WHERE	Type = 1
			AND DateAdded >= '20090428'
			AND DateAdded < '20090429'
	) AS f
WHERE	0.45E * maxID >= recID

SELECT		*
FROM		@Sample
ORDER BY	ID

E 12°55'05.63"
N 56°04'39.26"

lols
Posting Yak Master

174 Posts

Posted - 2009-04-28 : 12:12:41

Thanks for being so helpful.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 12:13:21

You're welcome.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources