adding rows 2

[stillsteam]

Hello

I use SQL Server 2005 and I have a list of appr. 7700 items.
In the list there is the complete stockquantity for each item and I halso have the typical amount for each pallet.
I need a list with all individual pallets and its stock

Item stock amount/pallet
100-001 2500 1000

I need the list to look like this
100-001 1000
100-001 1000
100-001 500

Is this possible to do?
Thank you in advance


/Jonas

 

[markros]

After struggling with the logic for about 20+ minutes I finally solved it - SQL Server 2005 and up

declare @t table (item Varchar(10), Stock money, Pallet money)


--Item      stock   amount/pallet
insert into @t values('100-001',   2500,    1000)

--I need the list to look like this

;with cte_toInsert as 
(select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from @t) X where Stock = 0 or (Stock >=0 and Amount > 0)
union all 
select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from cte_toInsert) X where (Stock = 0 and Amount > 0) OR Stock > 0 )

select * from cte_toInsert OPTION (MAXRECURSION 5)

--100-001  1000
--100-001  1000
--100-001  500</pre>

 

[markros]

declare @t table (item Varchar(10), Stock money, Pallet money)


--Item      stock   amount/pallet
insert into @t values('100-001',   2500,    1000)

--I need the list to look like this

;with cte_toInsert as
(select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from @t) X where Stock = 0 or (Stock >=0 and Amount > 0)
union all
select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from cte_toInsert) X where (Stock = 0 and Amount > 0) OR Stock > 0 )

select * from cte_toInsert OPTION (MAXRECURSION 5)

--100-001  1000
--100-001  1000
--100-001  500

 

[markros]

I was so concentrated on getting the logic work, that I forgot that we need to do this for all different items, not just one sample. I'm not sure how can we modify the above code to work for every item - I definitely don't want to use cursor approach here.

 

[markros]

Actually, the code works fine for multiple items

declare @t table (item Varchar(10), Stock money, Pallet money)


--Item      stock   amount/pallet
insert into @t values('100-001',   2500,    1000)
insert into @t values('100-002',   3000,    400)
insert into @t values('100-003',   2000,    400)

--I need the list to look like this

;with cte_toInsert as 
(select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from @t) X where Stock = 0 or (Stock >=0 and Amount > 0)
union all 
select Item, Pallet, Amount, Stock - Amount as Stock from (select Item, Pallet, Stock, case when Stock-Pallet > 0 then Pallet else Stock end as Amount from cte_toInsert) X where (Stock = 0 and Amount > 0) OR Stock > 0 )

select * from cte_toInsert order by Item, Amount DESC OPTION (MAXRECURSION 10) 

--100-001  1000
--100-001  1000
--100-001  500

Wow, now I really need to concentrate on my own work.

 

[bborissov]

Hey, that deserves a star.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Definitely And the more - the better

 

[bborissov]

Agreed!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[stillsteam]

Hi,

I agree.

Thank you Markros

/Jonas

 

[markros]

You're welcome - it was fun to solve.