Set based ideas ? Cursors way too slow....
-
Howzit all,
I have the following scenario... and I am trying to figure out how to do it in a set based scenario...
I have the following table..
ItemCode Date Sales Purchases
A 2006-01-01 5 0
A 2006-02-01 0 0
A 2006-03-01 2 0
A 2006-04-01 0 20
A 2006-05-01 0 0
A 2006-06-01 6 0
A 2006-07-01 0 35
I need to produce the following output... (given that I know Item A had
starting inventory prior to 2006-01-01 of 10
ItemCode Date Sales Purchases EndingInventory FreeToSell
A 2006-01-01 5 0 5 3
A 2006-02-01 0 0 5 3
A 2006-03-01 2 0 3 3
A 2006-04-01 0 20 23 17
A 2006-05-01 0 0 23 17
A 2006-06-01 6 0 17 17
A 2006-07-01 0 35 52 52
The logic goes something like this..
EndingInventory = EndingInventory on previous line (i.e. Month or day etc)
- Sales
+ Purchases
So.. 1st line gives : 10 - 5 + 0 = 5.
FreetoSell = EndingInventory - (All Sales in following dates prior to the next date where a purchase takes place).
So on line 1 it is : 5 - 2 ( Sum Sales where Date > 2006-01-01 and Date < 2006-04-01 (As 2006-04-01 contains purchases).. Obviously if there are no future purchases.. it would be Sum Sales where Date > 2006-01-01...
The logic follows down row by row and the results are generated..
Writing this logic with a cursor is simple, but takes FOREVER to run...
Any smart idea's on a better solution ?
BTW... there are about 65k different combinations of Items and Dates in that table.. my example assumes 1 item code... obviously this calculation is per item.
My calculation using Cursors takes nearly an hour to run for all 65k lines..
-
The "FreeToSell" concept is difficult. Here is the first part:
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 10)
declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert @purchase values ('A', '2006-01-01', 5, 0)
insert @purchase values ('A', '2006-02-01', 0, 0 )
insert @purchase values ('A', '2006-03-01', 2, 0 )
insert @purchase values ('A', '2006-04-01', 0, 20 )
insert @purchase values ('A', '2006-05-01', 0, 0 )
insert @purchase values ('A', '2006-06-01', 6, 0 )
insert @purchase values ('A', '2006-07-01', 0, 35 )
select p.Itemcode, p.Date, p.Sales, p.Purchases, i.qty + chg.netchange EndingInventory, 0 FreeToSell
from @purchase p
inner join @inventory i
on p.itemcode = i.itemcode
inner join (select pd.itemcode, pd.date, coalesce(sum(tdp.purchases - tdp.sales), 0) netchange
from @purchase pd
left outer join @purchase tdp
on pd.itemcode = tdp.itemcode and tdp.Date <= pd.Date
group by pd.itemcode, pd.date) chg
on p.itemcode = chg.itemcode and p.Date = chg.date
-
Try this. You may want to use temporary tables (#change and #lowinv) so you can add some indexes on them to speed up the process with all the items.
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 10)
declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert @purchase values ('A', '2006-01-01', 5, 0)
insert @purchase values ('A', '2006-02-01', 0, 0 )
insert @purchase values ('A', '2006-03-01', 2, 0 )
insert @purchase values ('A', '2006-04-01', 0, 20 )
insert @purchase values ('A', '2006-05-01', 0, 0 )
insert @purchase values ('A', '2006-06-01', 6, 0 )
insert @purchase values ('A', '2006-07-01', 0, 35 )
declare @change table (ItemCode char(1), Date datetime, netchange int)
insert @change
select pd.itemcode, pd.date, coalesce(sum(tdp.purchases - tdp.sales), 0) netchange
from @purchase pd
left outer join @purchase tdp
on pd.itemcode = tdp.itemcode and tdp.Date <= pd.Date
group by pd.itemcode, pd.date
declare @lowinv table (ItemCode char(1), Date datetime, lowinv int)
insert @lowinv
select p.itemcode, p.date, min(netchange) lowinv
from @purchase p
left outer join @purchase pp
on p.itemcode = pp.itemcode and pp.purchases > 0 and pp.date < p.date
inner join @change chg1
on p.itemcode = chg1.itemcode and p.Date >= chg1.date and chg1.date > coalesce(pp.date, 0)
where p.purchases > 0
group by p.itemcode, p.date
select p.Itemcode, p.Date, p.Sales, p.Purchases, i.qty + chg.netchange EndingInventory, i.qty + coalesce(inv.lowinv, chg.netchange) FreeToSell
from @purchase p
inner join @inventory i
on p.itemcode = i.itemcode
inner join @change chg
on p.itemcode = chg.itemcode and p.Date = chg.date
left outer join @lowinv inv
on p.itemcode = inv.itemcode and inv.date = (select min(x.date) from @lowinv x where x.date > p.date)
-
Try this due to a slight modification required when more with more dates:
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 10)
declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert @purchase values ('A', '2006-01-01', 5, 0)
insert @purchase values ('A', '2006-02-01', 0, 0 )
insert @purchase values ('A', '2006-03-01', 2, 0 )
insert @purchase values ('A', '2006-04-01', 0, 20 )
insert @purchase values ('A', '2006-05-01', 0, 0 )
insert @purchase values ('A', '2006-06-01', 6, 0 )
insert @purchase values ('A', '2006-07-01', 0, 35 )
insert @purchase values ('A', '2006-08-01', 10, 0 )
insert @purchase values ('A', '2006-09-01', 0, 2 )
declare @change table (ItemCode char(1), Date datetime, netchange int)
insert @change
select pd.itemcode, pd.date, coalesce(sum(tdp.purchases - tdp.sales), 0) netchange
from @purchase pd
left outer join @purchase tdp
on pd.itemcode = tdp.itemcode and tdp.Date <= pd.Date
group by pd.itemcode, pd.date
declare @lowinv table (ItemCode char(1), Date datetime, lowinv int)
insert @lowinv
select p.itemcode, p.date, min(netchange) lowinv
from @purchase p
left outer join @purchase pp
on p.itemcode = pp.itemcode and pp.purchases > 0 and pp.date = (select max(x.date) from @purchase x where x.date < p.date and x.purchases > 0)
inner join @change chg1
on p.itemcode = chg1.itemcode and p.Date >= chg1.date and chg1.date > coalesce(pp.date, 0)
where p.purchases > 0
group by p.itemcode, p.date
select p.Itemcode, p.Date, p.Sales, p.Purchases, i.qty + chg.netchange EndingInventory, i.qty + coalesce(inv.lowinv, chg.netchange) FreeToSell
from @purchase p
inner join @inventory i
on p.itemcode = i.itemcode
inner join @change chg
on p.itemcode = chg.itemcode and p.Date = chg.date
left outer join @lowinv inv
on p.itemcode = inv.itemcode and inv.date = (select min(x.date) from @lowinv x where x.date > p.date)
-
The best solution is to return raw data set and to calculate cumulative values on the client. For more details read: http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp
-
Hi Guys...
Thanks for the responses...
I don't really have the choice of doing it on the client, as I need this figure to put on a table so the users can have it added to their cubes.
Jeff... your solutions works perfect sometimes... and other times falls over... any ideas ?
I have messed around with your queries a lot, but have not had much luck... heres an example thats way wrong.... Then Ending inventory is perfect.. but that free to sell column is way out.. and I can't see why this is different to others... other than having more dates with both purch and sales... but in theory the same logic should work... any idea's
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 1386)
declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert @purchase values ('A', '2006-06-01', 228, 1320)
insert @purchase values ('A', '2006-07-01', 40, 4812 )
insert @purchase values ('A', '2006-08-01', 0, 1775 )
insert @purchase values ('A', '2006-09-01', 20, 0 )
insert @purchase values ('A', '2006-10-01', 0, 2975 )
insert @purchase values ('A', '2006-11-01', 0, 0 )
insert @purchase values ('A', '2006-12-01', 0, 400 )
declare @change table (ItemCode char(1), Date datetime, netchange int)
insert @change
select pd.itemcode, pd.date, coalesce(sum(tdp.purchases - tdp.sales), 0) netchange
from @purchase pd
left outer join @purchase tdp
on pd.itemcode = tdp.itemcode and tdp.Date 0 and pp.date = (select max(x.date) from @purchase x where x.date
0)
inner join @change chg1
on p.itemcode = chg1.itemcode and p.Date >= chg1.date and chg1.date > coalesce(pp.date, 0)
where p.purchases > 0
group by p.itemcode, p.date
select p.Itemcode, p.Date, p.Sales, p.Purchases, i.qty + chg.netchange EndingInventory, i.qty + coalesce(inv.lowinv, chg.netchange) FreeToSell
from @purchase p
inner join @inventory i
on p.itemcode = i.itemcode
inner join @change chg
on p.itemcode = chg.itemcode and p.Date = chg.date
left outer join @lowinv inv
on p.itemcode = inv.itemcode and inv.date = (select min(x.date) from @lowinv x where x.date > p.date)
-
The problem that I see has to do with the rows have both values for sales and purchases and I didn't consider this based on the initial data. I'll see what I can do with your data. What should the output be based upon your data?
-
Hi all,
I've just come to this and, since it looked like fun, thought I'd have a stab
I've just coded for the 1st set of data and expected results, but - like Jeff - await the expected results for the 2nd set of data so more fun can be had...
--data
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 10)
declare @purchase table (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert @purchase values ('A', '2006-01-01', 5, 0)
insert @purchase values ('A', '2006-02-01', 0, 0 )
insert @purchase values ('A', '2006-03-01', 2, 0 )
insert @purchase values ('A', '2006-04-01', 0, 20 )
insert @purchase values ('A', '2006-05-01', 0, 0 )
insert @purchase values ('A', '2006-06-01', 6, 0 )
insert @purchase values ('A', '2006-07-01', 0, 35 )
insert @purchase values ('A', '2006-08-01', 10, 0 )
insert @purchase values ('A', '2006-09-01', 0, 2 )
--calculation
select ItemCode, Date, Sales, Purchases, EndingInventory,
isnull(FreeToSell, EndingInventory) as FreeToSell
from (
select a.*,
(select b.qty + sum(purchases - sales) from @purchase where Date <= a.Date) as EndingInventory,
(select b.qty + sum(purchases - sales) from @purchase where Date <
(select min(date) from @purchase where purchases > 0 and Date > a.Date)) as FreeToSell
from @purchase a inner join @inventory b on a.itemcode = b.itemcode
) c
/*results
ItemCode Date Sales Purchases EndingInventory FreeToSell
-------- ----------- ----------- ----------- --------------- -----------
A 2006-01-01 5 0 5 3
A 2006-01-02 0 0 5 3
A 2006-01-03 2 0 3 3
A 2006-01-04 0 20 23 17
A 2006-01-05 0 0 23 17
A 2006-01-06 6 0 17 17
A 2006-01-07 0 35 52 42
A 2006-01-08 10 0 42 42
A 2006-01-09 0 2 44 44
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
And here's a very silly method (loosely based around another one of the techniques in the link Mirko provided)...
--data
declare @inventory table(ItemCode char(1), qty int)
insert @inventory values('A', 10)
if object_id('tempdb.dbo.#purchase') is not null drop table #purchase
create table #purchase (ItemCode char(1), Date datetime, Sales int, Purchases int)
insert #purchase values ('A', '2006-01-01', 5, 0)
insert #purchase values ('A', '2006-02-01', 0, 0 )
insert #purchase values ('A', '2006-03-01', 2, 0 )
insert #purchase values ('A', '2006-04-01', 0, 20 )
insert #purchase values ('A', '2006-05-01', 0, 0 )
insert #purchase values ('A', '2006-06-01', 6, 0 )
insert #purchase values ('A', '2006-07-01', 0, 35 )
insert #purchase values ('A', '2006-08-01', 10, 0 )
insert #purchase values ('A', '2006-09-01', 0, 2 )
go
--table setup (1)
alter table #purchase add EndingInventory int, FreeToSell int
alter table #purchase add constraint myOrder unique clustered (Date)
go
--calculation (1)
declare @i int
set @i = 10
update #purchase set @i = @i + Purchases - Sales, EndingInventory = @i
--table setup (2)
alter table #purchase drop constraint myOrder
alter table #purchase add descDate datetime
go
update #purchase set descDate = '99991231'-Date
alter table #purchase add constraint myOrder unique clustered (descDate)
--calculation (2)
declare @f int, @purchases int
update #purchase set
FreeToSell = @f,
@f = case when Purchases = 0 and not @purchases = 0 then EndingInventory else isnull(@f, EndingInventory) end,
@purchases = purchases
--tidy (1)
alter table #purchase drop constraint myOrder
alter table #purchase drop column descDate
go
--select (1)
select * from #purchase a order by Date
--tidy (2)
alter table #purchase drop column EndingInventory
alter table #purchase drop column FreeToSell
go
/*results
ItemCode Date Sales Purchases EndingInventory FreeToSell
-------- ----------- ----------- ----------- --------------- -----------
A 2006-01-01 5 0 5 3
A 2006-01-02 0 0 5 3
A 2006-01-03 2 0 3 3
A 2006-01-04 0 20 23 17
A 2006-01-05 0 0 23 17
A 2006-01-06 6 0 17 17
A 2006-01-07 0 35 52 42
A 2006-01-08 10 0 42 42
A 2006-01-09 0 2 44 44
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
ItemCode Date Sales Purchases EndingInventory FreeToSell
A 2006-06-01 00:00:00.000 228 1320 1102 1102
A 2006-07-01 00:00:00.000 40 4812 5874 5874
A 2006-08-01 00:00:00.000 0 1775 7649 7629
A 2006-09-01 00:00:00.000 20 0 7629 7629
A 2006-10-01 00:00:00.000 0 2975 10604 10604
A 2006-11-01 00:00:00.000 0 0 10604 10604
A 2006-12-01 00:00:00.000 0 400 11004 11004
Your solution is almost right.. will see if I can resolve this.. as just the first couple are incorrect...
The Free to Sell Column is calculated as :
EndingInventory - Sum(Sales) where Date > and Date < (Min Date where Purchases are not Zero) .. for some reason my join like this does not work so well...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply