help with query - 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

General SQL Server Forums

New to SQL Server Programming

help with query

Author

Topic

nkz
Starting Member

1 Post

Posted - 2009-11-23 : 14:26:49

I'm beginner programmer.
Please help me with correct query for this task:

select * from ml_OrderHeader
Order, Product, Qty, Price
1 1010 4 88
1 1020 3 45
2 1010 4 90
description: one delivery of two products in 4 and 3 boxes. The purchase price is in the last column.

select * from ml_DespatchLines
Id, Product, Price
1002 1010 115
1002 1010 115
1052 1010 200
1323 1020 65
1323 1020 65
description: sold 5 boxes with price in the last column.
The rest of boxes are stay in the stock.

I would like to make report for all bought products (boxes) with selling price. Boxes from stock should have the price from last purchase price for right products.
The final results should be like this:

1 1010 1 115
1 1010 2 115
1 1010 1 200
1 1010 1 90
1 1020 1 65
1 1020 1 65
1 1020 1 45
2 1010 1 90
2 1010 1 90
2 1010 1 90
2 1010 1 90

april198474
Starting Member

11 Posts

Posted - 2009-11-24 : 06:27:40

I am not so clear about third column in final results. I guess it means the numbers of box. If so, the "2" in the second row is not correct. I think it should be "1".

Then the script is as follows:

create table #temp
(
id int primary key identity(1,1),
[order] int,
product int,
Qty int default(1),
price int
)

declare @order int, @product int, @price int, @Qty int, @i int
declare cursor_Qty cursor for
(select Qty, [order], product from ml_OrderHeader)

open cursor_Qty
fetch next from cursor_Qty into @Qty, @order, @product

while @@fetch_status = 0
begin
select @i = @Qty
while @i<>0
begin
insert into #temp
([order], product, price)
(select [order], product, Price from ml_Orderheader where Qty = @Qty and [order] = @order)
update #temp
set price =
(select max (Price) from ml_Orderheader where Qty = @Qty and product = @product)
where #temp.product = @product
select @i = @i-1
end

fetch next from cursor_Qty into @Qty, @order, @product
end

close cursor_Qty
deallocate cursor_Qty

declare cursor_Change cursor for
(select price, product from ml_Despatchlines)

open cursor_Change
fetch next from cursor_Change into @price, @product
set @i = 0
while @@fetch_status = 0
begin
update #temp set price = @price
where id =
(select top 1 id from #temp where price <> @price and product = @product and id >@i)

set @i=@i+1

fetch next from cursor_Change into @price, @product
end

close cursor_Change
deallocate cursor_Change
select [order], product, qty, price from #temp
drop table #temp

Good Luck!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources