SQL2000求定单交期分析

2013-12-05

SQL2000求订单交期分析环境mssql200求订单交期分析采购订单主表porder单据id,供应商，单据日期billid,trade

SQL2000求订单交期分析
环境mssql200  求订单交期分析

采购订单主表
porder
单据id,供应商，单据日期
billid,traderid,billdate
1001  ,A供应商, 2013-11-01

采购订单从表
porderdetail
单据ID，序号  , 物料      ，订单数量,要求交货日期 , 是否中终 (1为终止，0非终止)
billid ,itemno ,materialid,quantity,chargedate,closed
1001   , 1     , a001     , 30000 , 2013-11-07, 0
1001   , 2     , b002     , 4000  , 2013-11-12, 0
1001   , 3     , k123     ,  700  , 2013-11-18, 1
1001   , 4     , j789     ,  600  , 2013-11-18, 1

采购入库主表
purchase
单据id,供应商,实际交货日期
billid,traderid,billdate
1001  ,A供应商, 2013-11-07
7007  ,A供应商, 2013-11-13

采购入库从表
purchasedetail
单据id , 序号 ,  物料     ,入库数量,引用订单id,引用订单序号
billid ,itemno ,materialid,quantity,referbillid,referitemno
7003   , 1     , a001     , 30000 , 1001, 1
7003   , 2     , b002     , 1000  , 1001, 2
7003   , 3     , k123     ,  500  , 1001, 3

7007   , 1     , b002     , 3000  , 1001, 2

注:porderdetail.billid=purchasedetail.referbillid  and  porderdetail.itemno=purchasedetail.referitemno
表示同一个单
采购单订可以分多次收货，采购订单可以终止，终止的采购订单也作完成。

求视图：采购订单交货期分析
订单ID，供应商，订单日期，物料   ，订单数量，要求交货日期，交货数量，未交货数量     ，实际交货完工日期，超期天数 ,终止
billid,traderid,billdate,materialid,quantity,chargedate ,referquantity,unreferquantity,finishdate,outfinishdate,closed
1001  ,A供应商,2013-11-01,a001     ,30000   ,2013-11-07  , 30000       ,  0            ,2013-11-07  , 0    , 0
1001  ,A供应商,2013-11-01,b002     , 4000   ,2013-11-12  , 4000        ,  0            ,2013-11-13  , 1    , 0
1001  ,A供应商,2013-11-01,k123     , 700    ,2013-11-18  , 500         ,  0            ,2013-11-07  , 0    , 1
1001  ,A供应商,2013-11-01,j789     , 600    ,2013-11-18  , 0           ,  0            ,2013-11-18  , 0    , 1
[解决办法]
porder 表有没有哪个字段？

帮你格式化一下

SELECT  a.billid ,
        a.traderid ,
        a.billdate ,
        b.materialid ,
        b.quantity ,
        b.chargedate ,
        c.quantity AS referquantity ,
        unreferquantity = b.quantity
        - ISNULL(( SELECT   SUM(Quantity)
                   FROM     purchasedetail e ,
                            purchase f
                   WHERE    e.billid = f.billid
                            AND referbillid = b.billid

                            AND referitemno = b.itemno
                            AND f.billdate <= c.billdate
                 ), 0) ,
        finishdate = d.billdate ,
        outfinishdate = CASE WHEN DATEDIFF(dd, b.chargedate, d.billdate) <= 0
                             THEN 0
                             ELSE DATEDIFF(dd, b.chargedate, d.billdate)
                        END ,
        b.closed
FROM    porder a
        INNER JOIN porderdetail b ON a.billid = b.billid
        LEFT JOIN purchasedetail c ON b.billid = c.referbillid
                                      AND b.itemno = c.referitemno
        LEFT JOIN purchase d ON d.billid = c.billid

热点排行

SQL Server

SQL2000求定单交期分析