T6 月统计报表的实现

ccty

--建立视图sixtb,第一级是代码，名称等，第二级是月统计表达式，第三级关联现存量,sql 3级嵌套查询,在用友软件里面 select * from sixtb即可 code by cty 2012.5
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sixtb]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[sixtb]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.sixtb
AS
SELECT b.cInvCode AS 存货编码, b.cInvName AS 存货名称, b.cInvStd AS 规格型号,
      b.cComUnitName AS 单位, b.cInvCCode AS 类别, b.cInvCName AS 类别名称,
      CAST(b.[1] AS numeric(18, 2)) AS [1月出], CAST(b.[2] AS numeric(18, 2)) AS [2月出],
      CAST(b.[3] AS numeric(18, 2)) AS [3月出], CAST(b.[4] AS numeric(18, 2)) AS [4月出],
      CAST(b.[5] AS numeric(18, 2)) AS [5月出], CAST(b.[6] AS numeric(18, 2)) AS [6月出],
      CAST(b.[7] AS numeric(18, 2)) AS [7月出], CAST(b.[8] AS numeric(18, 2)) AS [8月出],
      CAST(b.[9] AS numeric(18, 2)) AS [9月出], CAST(b.[10] AS numeric(18, 2)) AS [10月出],
      CAST(b.[11] AS numeric(18, 2)) AS [11月出], CAST(b.[12] AS numeric(18, 2))
      AS [12月出], CAST(SUM(c.iQuantity) AS numeric(18, 2)) AS 现存量
FROM (SELECT a.cInvCode, a.cInvName, a.cInvStd, a.cComUnitName, a.cInvCCode,
              a.cInvCName, SUM(CASE WHEN a.mon = 1 THEN a.isum ELSE 0 END) AS '1',
              SUM(CASE WHEN a.mon = 2 THEN a.isum ELSE 0 END) AS '2',
              SUM(CASE WHEN a.mon = 3 THEN a.isum ELSE 0 END) AS '3',
              SUM(CASE WHEN a.mon = 4 THEN a.isum ELSE 0 END) AS '4',
              SUM(CASE WHEN a.mon = 5 THEN a.isum ELSE 0 END) AS '5',
              SUM(CASE WHEN a.mon = 6 THEN a.isum ELSE 0 END) AS '6',
              SUM(CASE WHEN a.mon = 7 THEN a.isum ELSE 0 END) AS '7',
              SUM(CASE WHEN a.mon = 8 THEN a.isum ELSE 0 END) AS '8',
              SUM(CASE WHEN a.mon = 9 THEN a.isum ELSE 0 END) AS '9',
              SUM(CASE WHEN a.mon = 10 THEN a.isum ELSE 0 END) AS '10',
              SUM(CASE WHEN a.mon = 11 THEN a.isum ELSE 0 END) AS '11',
              SUM(CASE WHEN a.mon = 12 THEN a.isum ELSE 0 END) AS '12'
        FROM (SELECT dbo.Inventory.cInvCode, dbo.Inventory.cInvName,
                      dbo.Inventory.cInvStd, dbo.RdRecords.iQuantity AS isum,
                      MONTH(dbo.RdRecord.dDate) AS mon, dbo.RdRecord.bRdFlag,
                      dbo.ComputationUnit.cComUnitName, dbo.Inventory.cInvCCode,
                      dbo.InventoryClass.cInvCName
                FROM dbo.ComputationUnit INNER JOIN
                      dbo.Inventory ON
                      dbo.ComputationUnit.cComunitCode = dbo.Inventory.cComUnitCode INNER
                       JOIN
                      dbo.InventoryClass ON
                      dbo.Inventory.cInvCCode = dbo.InventoryClass.cInvCCode RIGHT OUTER
                       JOIN
                      dbo.RdRecords INNER JOIN
                      dbo.RdRecord ON dbo.RdRecords.ID = dbo.RdRecord.ID ON
                      dbo.Inventory.cInvCode = dbo.RdRecords.cInvCode
                WHERE (dbo.RdRecord.bRdFlag = 0)) a
        GROUP BY a.cInvCode, a.cInvName, a.cInvStd, a.cComUnitName, a.cInvCCode,
              a.cInvCName) b LEFT OUTER JOIN
      dbo.CurrentStock c ON b.cInvCode = c.cInvCode
GROUP BY b.cInvCode, b.cInvName, b.cInvStd, b.cComUnitName, b.cInvCCode,
      b.cInvCName, b.[1], b.[2], b.[3], b.[4], b.[5], b.[6], b.[7], b.[8], b.[9], b.[10], b.[11], b.[12]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

桂花酒酿丸子。

做個標記 [T6 月统计报表的实现 ]

ls00901

这个是专业人士的？还是我们做帐可以设置的？