先进先出的数据冲销处理.docVIP

先进先出的数据冲销处理 --示例数据:create?table?销售订单(单号?varchar(10),日期?datetime,货品编码?varchar(10),数量?int)insert?销售订单?select?A1,2005-5-1,ABCD,100?union?allselect?A2,2005-5-3,ABCD,200?union?allselect?A3,2005-5-4,ABCD,300?union?allselect?A4,2005-5-8,ABCD,500create?table?销售发货单(单号?varchar(10),日期?datetime,货品编码?varchar(10),数量?int)insert?销售发货单select?B1,2005-5-05,ABCD,250?union?allselect?B2,2005-5-07,ABCD,100?union?allselect?B3,2005-5-10,ABCD,150GO 问题描述:????对于示例数据中的销售订单和销售发货单，由于客户在做销售发货时无法指定本次是要发那一张订单的东西。所以销售订单无法与销售发货单关联。客户要求以先进先出的方式统计销售订单的执行情况。对于示例数据，要求结果如下：单号????????日期???????????? 货品编码????订单数量????发货数量?????待执行数量???????----------?-----------------?-------------?--------------?---------------?------------------?A1?????????2005-05-01???ABCD???????100???? ???????100??????????? ??0A2?????????2005-05-03???ABCD???????200??????? ????200???????? ????0A3?????????2005-05-04???ABCD???????300?????????? ?200???????? ???100A4?????????2005-05-08???ABCD???????500??????????? 0?????????? ???? ?500???????????????????????????????????? ?合计?????? ???1100?????? ??500??????????? ?600 --查询处理的过程select?????a.单号,a.日期,a.货品编码,????订单数量=a.数量,????发货数量=isnull(case?when?a.s数量b.数量?then?a.数量?else?b.数量-a.p数量?end,0),????待执行数量=case?when?a.s数量b.数量?then?0?else?a.s数量-isnull(b.数量,a.p数量)?endinto?#?from(????select?单号,日期=convert(char(10),日期,120),货品编码,????????数量=sum(数量),????????p数量=isnull((select?sum(数量)?from?销售订单????where?货品编码=a.货品编码????????and(datediff(day,日期,min(a.日期))0????????or?datediff(day,日期,min(a.日期))=0?and?单号a.单号)),0),????????s数量=isnull((select?sum(数量)?from?销售订单????????????where?货品编码=a.货品编码????????????????and(datediff(day,日期,min(a.日期))0????????????????or?datediff(day,日期,min(a.日期))=0?and?单号=a.单号)),0)????from?销售订单?a????group?by?单号,convert(char(10),日期,120),货品编码)a?????left?join(????????select?货品编码,数量=sum(数量)????????from?销售发货单????????group?by?货品编码????)b?on?a.货品编码=b.货品编码????????and?a.p数量b.数量order?by?a.单号,a.日期,a.货品编码select?*?from?#union?allselect?,,合计,sum(订单数量),sum(发货数量),sum(待执行数量)?fro