split on quantity field - 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 2000 Forums

SQL Server Development (2000)

split on quantity field

Author

Topic

cDc
Starting Member

30 Posts

Posted - 2003-10-17 : 13:26:07

Hi there. I am trying to work out the easiest way to do the following. I am trying to take data in the table below and create temporary records based on the quantity of items ordered and also divide the postage value up. for example

CREATE TABLE [dbo].[orderedItems] (
[lineid] [int] IDENTITY (1, 1) NOT NULL ,
[orderid] [int] NOT NULL ,
[itemid] [int] NOT NULL ,
[price] [money] NOT NULL ,
[postage] [money] NOT NULL ,
[qty] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO orderedItems(orderid,itemid,price,postage,qty) VALUES (1,1,9.99,1.5,3)
INSERT INTO orderedItems(orderid,itemid,price,postage,qty) VALUES (2,2,19.99,3,2)

the result should look like this

select * from #tempOrderedItems

orderid,itemid,price,postage,qty
1,1,9.99,.50,1
1,1,9.99,.50,1
1,1,9.99,.50,1
2,2,19.99,1.5,1
2,2,19.99,1.5,1

is there an easy way to do this :)
thanks
chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-17 : 13:41:10

Your data should have been inserted that way.

INSERT INTO orderedItems VALUES(1,1,9.99,.50,1)
INSERT INTO orderedItems VALUES(1,1,9.99,.50,1)
INSERT INTO orderedItems VALUES(1,1,9.99,.50,1)
INSERT INTO orderedItems VALUES(2,2,19.99,1.5,1)
INSERT INTO orderedItems VALUES(2,2,19.99,1.5,1)

Then you could have performed a GROUP BY with two SUMs to get your grouped data:

SELECT orderid, itemid, price, SUM(postage), SUM(qty)
FROM orderedItems
GROUP BY orderid, itemid, price, postage

Tara

cDc
Starting Member

30 Posts

Posted - 2003-10-17 : 15:16:20

thanks for your speedy response. I understand this would have been easier in this context however my question relates to an invoicing system and I need to take my existing order data and generate data to represent invoices for that particular order record. Ordinarily it would be straightforward however certain rules apply to my invoices that are created - such as they cannot go over a certain value (so i must generate enough invoices to cover the total quantity of items ordered).

ideally what i want to end up with out of my sample data is

invoiceid,orderid,itemid,price,postage,qty
1,1,1,9.99,.50,1
2,1,1,19.97,1.00,2
3,2,2,19.99,1.5,1
4,2,2,19.99,1.5,1

so no invoice exceeds $20 in value (excluding the postage value)

I do this in a very long-winded client side program at the moment and wanted to convert it to a stored procedure. I was thinking along the lines of splitting it all out into seperate rows first hence my question - then doing some more processing to merge rows where possible.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-18 : 03:46:29

If you have a sequence table in your database it's easy. Sequence tables rock

Create Table Sequence (seq int not null)
SET nocount on
declare @val int
select @val = 1
while @val <= 8000
begin
Insert into sequence values (@val)
select @val = @val + 1

end

Set nocount off

Keep that in the database, then write your query like this :

SELECT orderid,itemid,price,postage / qty as postage, qty
FROM orderedItems
INNER JOIN SEQUENCE s ON s.seq BETWEEN 1 and qty
ORDER BY orderID

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources