random by sum - 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)

random by sum

Author

Topic

partitur
Starting Member

12 Posts

Posted - 2009-05-28 : 04:17:21

Hi guys.
I would like to select a random output.
However, the number of random rows is not important, but I want the total sum of all fields T1.BALANCE to be between 1000-2000 (not for each row) for all rows in the random output.

This is the downsized query:
SELECT T1.AAA, T1.BALANCE, T2.BBB,
FROM T1 INNER JOIN T2 ON (T1.AAA=T2.AAA)
WHERE T1.BALANCE>0
AND NOT EXISTS (SELECT * FROM T3 WHERE (T1.AAA = T3.CCC)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 04:24:33

if you just want a result with random number why do you still need to select from the tables ?

KH
[spoiler]Time is always against us[/spoiler]

partitur
Starting Member

12 Posts

Posted - 2009-05-28 : 04:37:30

quote:
Originally posted by khtan

if you just want a result with random number why do you still need to select from the tables ?

KH
[spoiler]Time is always against us[/spoiler]

Hey khtan.
I'm not quite sure what you mean...
But instead of doing a :
SELECT TOP 10 T1.AAA, T1.BALANCE
FROM T1
ORDER BY NEWID()

which will always give me 10 rows, I want the query to give me different number of rows every time I run it but the total sum for all fields T1.BALANCE should always be between say 1000-2000.

partitur
Starting Member

12 Posts

Posted - 2009-05-28 : 05:17:12

Hmm, I just talk to the SQL Guru here at work.
He said that what I want to do is absolutely impossible....
What do you think ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-28 : 05:24:16

http://weblogs.sqlteam.com/peterl/archive/2008/08/12/How-to-sum-up-an-unknown-number-of-records.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 06:39:07

quote:
Originally posted by partitur

Hmm, I just talk to the SQL Guru here at work.
He said that what I want to do is absolutely impossible....

http://weblogs.sqlteam.com/peterl/archive/2008/11/23/Bin-packaging.aspx

Change guru

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 07:13:03

For your need, this is even simpler

-- Prepare sample data
DECLARE	@Sample	TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		Value INT
	)
 
INSERT	@Sample
	(
		Value
	)
SELECT	899 UNION ALL
SELECT	100 UNION ALL
SELECT	 95 UNION ALL
SELECT	 50 UNION ALL
SELECT	 55 UNION ALL
SELECT	 40 UNION ALL
SELECT	  5 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	 50 UNION ALL
SELECT	250 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	 90 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	 50 UNION ALL
SELECT	350 UNION ALL
SELECT	450 UNION ALL
SELECT	450 UNION ALL
SELECT	100 UNION ALL
SELECT	100 UNION ALL
SELECT	 50 UNION ALL
SELECT	 50 UNION ALL
SELECT	 50 UNION ALL
SELECT	  1 UNION ALL
SELECT	 10 UNION ALL
SELECT	  1

-- Prepare staging
DECLARE	@Stage TABLE
	(
		RowID INT,
		Value INT
	)

-- Initialize user supplied parameters
DECLARE	@FromValue INT,
	@ToValue INT

SELECT	@FromValue = 1000,
	@ToValue = 2000

-- Do the work
WHILE @@ROWCOUNT > 0 AND NOT (SELECT COALESCE(SUM(Value), 0) FROM @Stage) BETWEEN @FromValue AND @ToValue
	INSERT		@Stage
			(
				RowID,
				Value
			)
	SELECT TOP 1	x.RowID,
			x.Value
	FROM		@Sample AS x
	WHERE		NOT EXISTS (SELECT * FROM @Stage AS s WHERE s.RowID = x.RowID)
	ORDER BY	NEWID()

SELECT	*
FROM	@Stage

SELECT	SUM(Value) FROM @Stage

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 07:29:14

Or even more elaborate?

-- Prepare sample data
CREATE TABLE	tblSample
		(
			RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			CustomerID INT,
			Value INT
		)
 
INSERT	tblSample
	(
		CustomerID,
		Value
	)
SELECT	1, 899 UNION ALL
SELECT	1, 100 UNION ALL
SELECT	1,  95 UNION ALL
SELECT	1,  50 UNION ALL
SELECT	1,  55 UNION ALL
SELECT	1,  40 UNION ALL
SELECT	1,   5 UNION ALL
SELECT	1, 100 UNION ALL
SELECT	1, 100 UNION ALL
SELECT	1, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2,  50 UNION ALL
SELECT	2, 250 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2, 100 UNION ALL
SELECT	2,  90 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3,  50 UNION ALL
SELECT	3, 350 UNION ALL
SELECT	3, 450 UNION ALL
SELECT	3, 450 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3, 100 UNION ALL
SELECT	3,  50 UNION ALL
SELECT	3,  50 UNION ALL
SELECT	3,  50 UNION ALL
SELECT	3,   1 UNION ALL
SELECT	3,  10 UNION ALL
SELECT	3,   1
GO
-- Create a view to bypass limitation in function
CREATE VIEW dbo.vwSample
AS
	SELECT	RowID,
		CustomerID,
		Value,
		ROW_NUMBER() OVER (ORDER BY NEWID()) AS totID,
		ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY NEWID()) AS cusID
	FROM	tblSample
GO
-- Create a function to get my data
CREATE FUNCTION dbo.fnGetMySum
(
	@CustomerID INT,
	@FromValue INT,
	@ToValue INT
)
RETURNS @Data TABLE
	(
		RowID INT,
		CustomerID INT,
		Value INT
	)
AS
BEGIN
	WHILE @@ROWCOUNT > 0 AND NOT (SELECT COALESCE(SUM(Value), 0) FROM @Data) BETWEEN @FromValue AND @ToValue
		INSERT	@Data
			(
				RowID,
				CustomerID,
				Value
			)
		SELECT	TOP 1
			RowID,
			CustomerID,
			Value
		FROM	(
				SELECT	RowID,
					CustomerID,
					Value
				FROM	vwSample
				WHERE	(totID = 1 AND @CustomerID IS NULL)
					OR (cusID = 1 AND CustomerID = @CustomerID)
			) AS s
		WHERE	NOT EXISTS (SELECT * FROM @Data AS d WHERE d.RowID = s.RowID)

	RETURN
END
GO
-- Use function either directly ot with CROSS APPLY, for all customers
SELECT	*
FROM	dbo.fnGetMySum(NULL, 1000, 2000)
GO
-- Use function either directly ot with CROSS APPLY, for customer #2
SELECT	*
FROM	dbo.fnGetMySum(2, 1000, 2000)
GO
DROP VIEW dbo.vwSample
GO
DROP FUNCTION dbo.fnGetMySum
GO
DROP TABLE tblSample
GO