用自定义报表做的库存留存情况表

cghcgh

--------直接在自定义报表上运行就可以了一张库存留存情况表T6U6..U8还没试过呵呵呵俺们给的是不要钱的

SELECT ISNULL(WSIN.业务类型1, '期初库存') + ' ' + ISNULL(WSOUT.业务类型2, '')
      AS 业务类型, WS.cWhName AS 调出仓库名称, WS.cDefine23 AS表体自定义项2,
      WS.cInvCode AS 存货编码, WSIN.单价1 AS 单价, ISNULL(WSOUT.数量2, 0)
      AS 发出数量, ISNULL(WSOUT.数量2 * WSIN.单价1, 0) AS 发出金额,
      ISNULL(WSIN.数量1, 0) - ISNULL(WSOUT.数量2, 0) AS 数量余额,
      (ISNULL(WSIN.数量1, 0) - ISNULL(WSOUT.数量2, 0)) * WSIN.单价1 AS 金额余额,
      WSIN.dDate AS 转入日期, WSOUT.dDate AS 转出日期, WSIN.数量1 AS 收入数量,
      dbo.Inventory.cInvName AS [存货编码]dbo.Inventory.cInvStd AS 规格型号,
      dbo.Warehouse.cWhCode AS 仓库编码, WSIN.业务类型1, WSOUT.业务类型2
FROM (SELECT DISTINCT A.cWhCode, B.cDefine23, B.cInvCode, C.cWhName
        FROM rdrecord A INNER JOIN
              rdrecords B ON A.id = B.id AND B.cDefine23 IS NOT NULL AND
              A.bpufirst = 0 INNER JOIN
              warehouse C ON A.cWhCode = C.cWhCode) WS INNER JOIN
      dbo.Inventory ON
      WS.cInvCode COLLATE Chinese_PRC_CI_AS = dbo.Inventory.cInvCode INNER JOIN
      dbo.Warehouse ON
      WS.cWhCode COLLATE Chinese_PRC_CI_AS = dbo.Warehouse.cWhCode LEFT OUTER
       JOIN
          (SELECT A.cBusType AS 业务类型1, A.cWhCode AS cWhCode1,
               B.cDefine23 AS 表体自定义项1, B.cInvCode AS 存货编码1,
               SUM(B.iQuantity) AS 数量1, AVG(B.iUnitCost) AS 单价1, A.dDate
         FROM dbo.RdRecord A INNER JOIN
               dbo.RdRecords B ON A.ID = B.ID AND A.bRdFlag = 1 AND
               B.cDefine23 IS NOT NULL AND A.bpufirst = 0
         GROUP BY A.cBusType, A.cWhCode, B.cDefine23, B.cInvCode, A.dDate) WSIN ON
      WS.cWhCode = WSIN.cWhCode1 AND WS.cDefine23 = WSIN.表体自定义项1 AND
      WS.cInvCode = WSIN.存货编码图号1 LEFT OUTER JOIN
          (SELECT A.cBusType AS 业务类型2, A.cWhCode AS cWhCode2,
               B.cDefine23 AS 表体自定义项2, B.cInvCode AS 存货编码2,
               SUM(B.iQuantity) AS 数量2, A.dDate
         FROM dbo.RdRecord A INNER JOIN
               dbo.RdRecords B ON A.ID = B.ID AND A.bRdFlag = 0 AND
               B.cDefine23 IS NOT NULL AND A.bpufirst = 0
         GROUP BY A.cBusType, A.cWhCode, B.cDefine23, B.cInvCode, A.dDate)
      WSOUT ON WS.cWhCode = WSOUT.cWhCode2 AND
      WS.cDefine23 = WSOUT.表体自定义项2 AND
      WS.cInvCode = WSOUT.存货编码2
WHERE (ISNULL(WSIN.数量1, 0) - ISNULL(WSOUT.数量2, 0)--------- > 0)

wozengcong

自定义报表？？？？？？
.shl文件？

cghcgh

在自定义报表上的SQL运行一下就行了,稍加改动就行

jxncgmh

该兄弟好人啊