Mathematical Algorithms in SQL

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 2000 Forums

SQL Server Development (2000)

Mathematical Algorithms in SQL

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-14 : 05:52:46

I have the following data:


number	SetQuantity	Desc	LineQuantity
1	2	NULL	1
2	2	NULL	1
1	2	Table	1
2	2	Table	1
1	2	Chair	4
2	2	Chair	4
3	2	Chair	4
4	2	Chair	4
5	2	Chair	4
6	2	Chair	4
7	2	Chair	4
8	2	Chair	4

I want to add a column to assign the items to a set. In this scenario, a set equals 1 table, and 4 chairs, like so:

1 2 NULL 1 Set 1
1 2 Table 1 Set 1
1 2 Chair 4 Set 1
2 2 Chair 4 Set 1
3 2 Chair 4 Set 1
4 2 Chair 4 Set 1

However, if a customer orders more than 1 set (SetQuantity), then I need to relate the items to 2 sets, like so:

1 2 NULL 1 Set 1
2 2 NULL 1 Set 2
1 2 Table 1 Set 1
2 2 Table 1 Set 2
1 2 Chair 4 Set 1
2 2 Chair 4 Set 1
3 2 Chair 4 Set 1
4 2 Chair 4 Set 1
5 2 Chair 4 Set 2
6 2 Chair 4 Set 2
7 2 Chair 4 Set 2
8 2 Chair 4 Set 2

The setquantity is dynamic, so I need an algorithm that can deduce which set the item belongs to depending on the SetQuantity, LineQuantity and number field. For example, dynamically build a range table??????

Anyway, if anyone can deduce a mathematical function that can derive this on the fly, I would be eternally grateful!!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 06:09:35

So this is not ok then?

-- prepare test data

declare	@test table (number int, SetQuantity int, [Desc] varchar(50), LineQuantity int)

insert	@test
select	1, 2, NULL, 1 union all
select	2, 2, NULL, 1 union all
select	1, 2, 'Table', 1 union all
select	2, 2, 'Table', 1 union all
select	1, 2, 'Chair', 4 union all
select	2, 2, 'Chair', 4 union all
select	3, 2, 'Chair', 4 union all
select	4, 2, 'Chair', 4 union all
select	5, 2, 'Chair', 4 union all
select	6, 2, 'Chair', 4 union all
select	7, 2, 'Chair', 4 union all
select	8, 2, 'Chair', 4

-- do the work
select		number,
		setquantity,
		[desc],
		linequantity,
		1 + (number - 1) / linequantity as newcolumn
from		@test

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 06:11:05

[code]-- prepare test data

declare @test table (number int, SetQuantity int, [Desc] varchar(50), LineQuantity int)

insert @test
select 1, 2, NULL, 1 union all
select 1, 2, 'Table', 1 union all
select 1, 2, 'Chair', 4 union all
select 2, 2, 'Chair', 4 union all
select 3, 2, 'Chair', 4 union all
select 4, 2, 'Chair', 4

-- do the work
select number,
setquantity,
[desc],
linequantity,
1 + (number - 1) / linequantity as newcolumn
from @test[/code]

Peter Larsson
Helsingborg, Sweden

Bex
Aged Yak Warrior

580 Posts

Posted - 2006-11-14 : 06:31:02

Peter, once again you have come through with a great result. Thankyou so much!!! You're a GENIUS!!!

Hearty head pats

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 07:17:01

I'm happy it worked out for you, even if the answer was not that advanced.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources