sql库房数量均摊的问题

[netcup]

有两个表:A和B
A:
SPID HW QTY PRICE PIHAO YXQ
SP0001 HW0001 100 10.00 050212 200807
SP0002 HW0001 408 5.00 051225 200809
SP0002 HW0002 800 5.00 050105 200801
SP0002 HW0002 1600 5.00 050409 200804
.
.
B:
DJBH SPID XSQTY ZL PRICE
XS0001 SP0001 800 100 5.00
XS0002 SP0002 108 100 5.10

说明:A表为库存数量批号表,B为销售单据明细表.字段为:DJBH单据编号,SPID商品ID,QTY数量,ZL装箱规格,PRICE单价,HW库房ID
需求:要求按照条件生成表C.
最终目的:
生成表C:
DJBH SPID XSQTY ZL HW PRICE PIHAO SXRQ
XS0001 SP0001 800 100 HW0001 10.00 050212 200807
XS0002 SP0002 8 100 HW0001 5.00 051225 200809
XS0002 SP0002 100 100 HW0002 5.00 050105 200801
条件:若B.XSQTY>A.QTY则只取A.QTY的数量,若B.XSQTY<A.QTY,才取B.XSQTY到C作为C.XSQTY.
满足此条件后,则(B.XSQTY % B.ZL),在C表中分为两行,如SP0002,100个是一个整件,则余数8个的库房ID为HW0001,整件数为100,库房ID为HW0002,同时HW0002的有两个批号和有效期,则取有效期最近的,即yxq为200801的.
请问如何实现?
这个问题挺难的.忘各位指教.