系统统计

 SELECT x.Dept_Code AS 科室编码,n.Dept_Name AS 科室名称,x.Goods_Code AS 商品编码,       k.Goods_Name AS 商品名称,k.Goods_Specification AS 商品规格,k.Goods_PackUnit AS 商品单位,k.DefaultPrice AS 商品单价,       x.startStock AS 期初库存,isnull(z.outStockCount,0) AS 本期出库总数,       isnull(z.outStockCount*k.DefaultPrice,0) AS 本期出库金额,isnull(y.inStockCount,0) AS 本期入库总数,       isnull(y.inStockCount*k.DefaultPrice,0) AS 本期入库金额,x.endStock as 期末库存,m.Currently_Stock AS 当前库存,isnull(m.Currently_Stock*k.DefaultPrice,0) AS 当前库存金额 FROM     (SELECT a.Dept_Code,a.Goods_Code,a.Initialize_Stock,            (a.Initialize_Stock+isnull(b.startCount,0)) AS startStock,            (a.Initialize_Stock+isnull(c.endCount,0)) AS endStock      FROM InitializeStock a      LEFT JOIN         (SELECT o.Dept_Code,o.Goods_Code,sum(o.Count) AS startCount          FROM Goods_OutInStock o          LEFT JOIN             (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date              FROM InitializeStock) p          ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code          WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 00:00:00'          GROUP BY o.Dept_Code,o.Goods_Code) b      ON a.Goods_Code=b.goods_code AND a.Dept_Code=b.Dept_Code      LEFT JOIN         (SELECT o.Dept_Code,o.Goods_Code,sum(Count) AS endCount          FROM Goods_OutInStock o          LEFT JOIN             (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date              FROM InitializeStock) p          ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code          WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 23:59:59'          GROUP BY o.Dept_Code,o.Goods_Code) c      ON a.Goods_Code=c.Goods_Code AND a.Dept_Code=c.Dept_Code) x      LEFT JOIN         (SELECT Dept_Code,Goods_Code,sum(Count) AS inStockCount,count(OutInStockFlag) AS 入库次数          FROM Goods_OutInStock WHERE Use_Date BETWEEN '2011-10-27 00:00:00' AND '2011-10-27 23:59:59'          AND OutInStockFlag='入库' GROUP BY Dept_Code,Goods_Code) y      ON x.Dept_Code=y.Dept_Code AND x.Goods_Code=y.Goods_Code      LEFT JOIN         (SELECT Dept_Code,Goods_Code,sum(Count) AS outStockCount,count(OutInStockFlag) AS 出库次数          FROM Goods_OutInStock WHERE Use_Date BETWEEN '2011-10-27 00:00:00' AND '2011-10-27 23:59:59'          AND OutInStockFlag='出库' GROUP BY Dept_Code,Goods_Code) z      ON x.Dept_Code=z.Dept_Code AND x.Goods_Code=z.Goods_Code ,     Dept_Stock m, Dept_JDE n, Goods_JDE k WHERE x.Dept_Code=m.Dept_Code AND x.Goods_Code=m.Goods_Code AND x.Dept_Code=n.Dept_Code AND x.Goods_Code=k.Goods_Code AND x.Dept_Code='1001'  ORDER BY x.Dept_Code,x.Goods_Code DESC

 

select tmp.spbm as 商品编码,tmp.spmc as 商品名称,tmp.spgg as 商品规格,tmp.spdw as 商品单位,tmp.spdj as 商品单价,sum(cqkc) as 期初库存 ,sum(bqckzs) as 本期出库总数,sum(bqckje)as 本期出库金额,sum(bqrkzs) as 本期入库总数,sum(bqrkje) as 本期入库金额,sum(qmkc)as 期末库存,sum(dqkc)as 当前库存,sum(dqkcje)as 当前库存金额 from (SELECT x.Dept_Code AS ksbh,n.Dept_Name AS ksmc,x.Goods_Code AS spbm,
k.Goods_Name AS spmc,k.Goods_Specification AS spgg,k.Goods_PackUnit AS spdw,k.DefaultPrice AS spdj,       x.startStock AS cqkc,isnull(z.outStockCount,0) AS bqckzs,
isnull(z.outStockCount*k.DefaultPrice,0) AS bqckje,isnull(y.inStockCount,0) AS bqrkzs, isnull(y.inStockCount*k.DefaultPrice,0) AS bqrkje,x.endStock as qmkc,m.Currently_Stock AS dqkc,isnull(m.Currently_Stock*k.DefaultPrice,0) AS dqkcje
FROM (SELECT a.Dept_Code,a.Goods_Code,a.Initialize_Stock,(a.Initialize_Stock+isnull(b.startCount,0)) AS startStock,            (a.Initialize_Stock+isnull(c.endCount,0)) AS endStock      FROM InitializeStock a
LEFT JOIN (SELECT o.Dept_Code,o.Goods_Code,sum(o.Count) AS startCount
FROM Goods_OutInStock o  LEFT JOIN (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date FROM InitializeStock) p ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code
WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 00:00:00' GROUP BY o.Dept_Code,o.Goods_Code) b      ON a.Goods_Code=b.goods_code AND a.Dept_Code=b.Dept_Code      LEFT JOIN         (SELECT o.Dept_Code,o.Goods_Code,sum(Count) AS endCount
FROM Goods_OutInStock o LEFT JOIN (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date FROM InitializeStock) p          ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code          WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 23:59:59'
GROUP BY o.Dept_Code,o.Goods_Code) c ON a.Goods_Code=c.Goods_Code AND a.Dept_Code=c.Dept_Code)
x
LEFT JOIN (SELECT Dept_Code,Goods_Code,sum(Count) AS inStockCount,count(OutInStockFlag) AS 入库次数
FROM Goods_OutInStock WHERE Use_Date BETWEEN
'2011-10-27 00:00:00' AND '2011-10-27 23:59:59' AND OutInStockFlag='入库' GROUP BY Dept_Code,Goods_Code)
y
ON x.Dept_Code=y.Dept_Code AND x.Goods_Code=y.Goods_Code
LEFT JOIN (SELECT Dept_Code,Goods_Code,sum(Count) AS outStockCount,
count(OutInStockFlag) AS 出库次数 FROM Goods_OutInStock WHERE Use_Date BETWEEN '2011-10-27 00:00:00' AND '2011-10-27 23:59:59' AND OutInStockFlag='出库' GROUP BY Dept_Code,Goods_Code)
z
 ON x.Dept_Code=z.Dept_Code AND x.Goods_Code=z.Goods_Code ,
Dept_Stock m, Dept_JDE n, Goods_JDE k WHERE x.Goods_Code=m.Goods_Code AND x.Dept_Code=n.Dept_Code AND x.Goods_Code=k.Goods_Code AND x.Dept_Code='1001'

) as tmp group by tmp.spbm,tmp.spmc,tmp.spgg,tmp.spdw,tmp.spdj

 

select tmp.spbm as 商品编码,tmp.spmc as 商品名称,tmp.spgg as 商品规格,tmp.spdw as 商品单位,tmp.spdj as 商品单价,sum(cqkc) as 期初库存 ,sum(bqckzs) as 本期出库总数,sum(bqckje)as 本期出库金额,sum(bqrkzs) as 本期入库总数,sum(bqrkje) as 本期入库金额,sum(qmkc)as 期末库存,sum(dqkc)as 当前库存,sum(dqkcje)as 当前库存金额 from (SELECT x.Dept_Code AS ksbh,n.Dept_Name AS ksmc,x.Goods_Code AS spbm,
k.Goods_Name AS spmc,k.Goods_Specification AS spgg,k.Goods_PackUnit AS spdw,k.DefaultPrice AS spdj,       x.startStock AS cqkc,isnull(z.outStockCount,0) AS bqckzs,
isnull(z.outStockCount*k.DefaultPrice,0) AS bqckje,isnull(y.inStockCount,0) AS bqrkzs, isnull(y.inStockCount*k.DefaultPrice,0) AS bqrkje,x.endStock as qmkc,m.Currently_Stock AS dqkc,isnull(m.Currently_Stock*k.DefaultPrice,0) AS dqkcje
FROM (SELECT a.Dept_Code,a.Goods_Code,a.Initialize_Stock,(a.Initialize_Stock+isnull(b.startCount,0)) AS startStock,            (a.Initialize_Stock+isnull(c.endCount,0)) AS endStock      FROM InitializeStock a
LEFT JOIN (SELECT o.Dept_Code,o.Goods_Code,sum(o.Count) AS startCount
FROM Goods_OutInStock o  LEFT JOIN (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date FROM InitializeStock) p ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code
WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 00:00:00' GROUP BY o.Dept_Code,o.Goods_Code) b      ON a.Goods_Code=b.goods_code AND a.Dept_Code=b.Dept_Code      LEFT JOIN         (SELECT o.Dept_Code,o.Goods_Code,sum(Count) AS endCount
FROM Goods_OutInStock o LEFT JOIN (SELECT DISTINCT Dept_Code,Goods_Code,Initialize_Date FROM InitializeStock) p          ON o.Dept_Code=p.Dept_Code AND o.Goods_Code=p.Goods_Code          WHERE o.Use_Date>p.Initialize_Date AND o.Use_Date<='2011-10-27 23:59:59'
GROUP BY o.Dept_Code,o.Goods_Code) c ON a.Goods_Code=c.Goods_Code AND a.Dept_Code=c.Dept_Code)
x
LEFT JOIN (SELECT Dept_Code,Goods_Code,sum(Count) AS inStockCount,count(OutInStockFlag) AS 入库次数
FROM Goods_OutInStock WHERE Use_Date BETWEEN
'2011-10-27 00:00:00' AND '2011-10-27 23:59:59' AND OutInStockFlag='入库' GROUP BY Dept_Code,Goods_Code)
y
ON x.Dept_Code=y.Dept_Code AND x.Goods_Code=y.Goods_Code
LEFT JOIN (SELECT Dept_Code,Goods_Code,sum(Count) AS outStockCount,
count(OutInStockFlag) AS 出库次数 FROM Goods_OutInStock WHERE Use_Date BETWEEN '2011-10-27 00:00:00' AND '2011-10-27 23:59:59' AND OutInStockFlag='出库' GROUP BY Dept_Code,Goods_Code)
z
 ON x.Dept_Code=z.Dept_Code AND x.Goods_Code=z.Goods_Code ,
Dept_Stock m, Dept_JDE n, Goods_JDE k WHERE x.Goods_Code=m.Goods_Code AND x.Dept_Code=n.Dept_Code AND x.Goods_Code=k.Goods_Code AND x.Dept_Code='1001'

) as tmp group by tmp.spbm,tmp.spmc,tmp.spgg,tmp.spdw,tmp.spdj