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
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语句
- 查看数据库备份计划,怎么一闪而过?
- 关于统计查询的问题
- 急:数据库复制问题:不能创建项目,提示错误5007 xp_execresultsed
- 如何利用sql查询语句备份数据库!
- moss数据库备份失败,提示必须串行化,请高手帮忙解决,谢谢!
- ???Analysis services我注册新服务器的时候的错误
- sql声明变量的问题
- SQL日期的比较,用什么啊?大于号怎么不行啊?在线等,急啊!
- 如何启动一个JOB?
- 函数查询where条件变量
- 分发代理无法在“C:\Program Files (x86)\Microsoft SQL Server\100\COM”目录中创建临时文件。系统返回的错误代码为
服务器: 消息 207,级别 16,状态 3,行 1
列名 'billdate' 无效。
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