日期:2014-05-17 浏览次数:20595 次
SELECT S1.年份 ,
M.number AS 月份 ,
S1.物料代码 , --這個地方不是很嚴謹,假設的是有採購的話,肯定會就在入庫或出庫的範圍
ISNULL(S1.采购重量, 0) AS 采购重量 ,
ISNULL(S2.入库重量, 0) AS 入库重量 ,
ISNULL(S3.出库重量, 0) AS 出库重量
FROM master..spt_values M
LEFT JOIN selete1 S1 ON M.number = S1.月份
LEFT JOIN selete2 S2 ON M.number = S2.月份
AND S1.年份 = S2.年份
LEFT JOIN selete3 S3 ON M.number = S3.月份
AND S1.年份 = S3.年份
WHERE m.type = 'P'
AND m.number BETWEEN 1 AND 12
AND S1.年份 = 2012
---1、采购的查询
select YEAR(copurchase.PurDate) AS NF,MONTH(copurchase.PurDate) AS YF,
pbMaterial.MaterialID AS WLDM,pbMaterial.MaterialName AS WLName,SUM(coPurchaseDetail.Amount) AS BZSL
from copurchase
inner join coPurchaseDetail on copurchase.PurNo=coPurchaseDetail.PurNo
inner join pbMaterial on coPurchaseDetail.MaterialNo=pbMaterial.MaterialNo
where YEAR(copurchase.PurDate)=2012 and MONTH(copurchase.PurDate)=9