Need to determine warehouse count

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

Transact-SQL (2005)

Need to determine warehouse count

Author

Topic

phishCoder
Starting Member

4 Posts

Posted - 2007-06-08 : 18:14:23

I need to count the number of warehouses that an order will ship from to help figure shipping costs.


select 
	c.ProductID,
	c.Qty,
	s.WH1OnHand, 
	s.WH2OnHand, 
	s.WH3OnHand, 
	s.WH4OnHand
from 
	Stock s left outer join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID

Sample1 results:
ProductID Qty WH1OnHand WH2OnHand WH3OnHand WH4OnHand
598 1 40 132 137 117
100 10 6 0 12 3

This order will ship from 1 warehouse

Sample2 results:
ProductID Qty WH1OnHand WH2OnHand WH3OnHand WH4OnHand
598 1 40 132 0 117
100 10 6 0 12 3

This order will ship from 2 warehouses

One bit of information that I would expect that would make this query a little easier: If the products will not ship from 1 warehouse then we will be charging for shipping from 2 warehouses even if the actual count is higher.

I have already written a cursor containing a one way switch to deal with the situation where none of the warehouses are able to fulfill the quantity ordered for a particular ProductID.

Any help would be greatly appreciated.

J

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-10 : 15:49:09

I started working on this, and its taking me way longer then expected.

The following query will currently tell you if the order can ship from one warehouse. I haven't gotten the calculation done for how many warehouses the minumum will be if it can't ship from one yet because I need to leave for somewhere. The main issue was dealing with the warehouse in the column format rather then row. Once I got it into rows it was going a lot easier. Hopfully this is a start I'll try to finish it later if I have time.




create table #tmp(Productid varchar(50),whqty int,whID int)
insert into #tmp(ProductID,whqty,whid)
select s.ProductID,s.wh1onhand,1 from 
	Stock s left join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID
union all
select s.ProductID,s.wh2onhand,2 from 
	Stock s  left join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID
union all
select s.ProductID,s.wh3onhand,3 from 
	Stock s  left join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID
union all
select s.ProductID,s.wh4onhand,4 from 
	Stock s  left join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID


select 
	c.ProductID,
	c.Qty,
	t.whqTY,
	t.WHID,
	case 
	when t.qty >= c.qty then 1
	else 0
	end as [Inwarehouse]
 --A ID letting me know that there is a warehouse
 --that has the entire qty
into #tmpStaging
from		Cart c 
Inner JOIN	#TMP t
on			t.ProductID = c.ProductID
group by tt.whid
where
	c.UserID = @UserID

--Any item that show up will meant the order can ship from 1 warehouse = 1 ship charge
select	a.WHID
from #TmpStaging a
where a.InWarehouse = 1
group by a.whid
having count(*) = (Select count(*) from cart c where c.UserID = @UserID))

phishCoder
Starting Member

4 Posts

Posted - 2007-06-10 : 21:11:15

Because I don't frequent discussion groups often, I had forgotten to post the solution. I apologize to Vinnie881 but I think you'll enjoy the solution. I left the variable declarations out for brevity. The temp table is not necessary and possibly unwise but the code is cleaner.

Here it is:


set @ShipmentCount = 1
select 
	c.ProductID,
	c.Qty,
	s.WH1OnHand, 
	s.WH2OnHand, 
	s.WH3OnHand, 
	s.WH4OnHand
into
   #Stock
from 
	Stock s left outer join 
	Cart c on s.ProductID = c.ProductID
where
	c.UserID = @UserID

select @CountWH1 = count(*) from #Stock where WH1OnHand < Quantity
select @CountWH2 = count(*) from #Stock where WH2OnHand < Quantity
select @CountWH3 = count(*) from #Stock where WH3OnHand < Quantity
select @CountWH4 = count(*) from #Stock where WH4OnHand < Quantity

if (@CountWH1 > 0) and (@CountWH2 > 0) and (@CountWH3 > 0) and (@CountWH4 > 0) begin
	set @ShipmentCount = 2
end

If "they" ever decide they want an accurate shipment count rather than this bit type count then this code can easily be changed accordingly. Pretty cool eh.

Thanks again for your help Vinnie881.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-11 : 08:52:55

Your count(*) from #stock can be simplified to


select 
      @CountWH1 = sum(case when WH1OnHand < Quantity then 1 else 0 end)
      @CountWH2 = sum(case when WH2OnHand < Quantity then 1 else 0 end)
      .
from
      #stock

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources