SELECT a.*,b.Descriptions ,b.Unit,s.Name AS Name
FROM (SELECT ISNULL(a.StoreID, b.StoreID) AS StoreID,
ISNULL(a.PartsID, b.PartsID) AS PartsID,
ISNULL(a.Rev, b.Rev) AS Rev,
ISNULL(a.上月结存,0) AS 上月结存,
ISNULL(a.本月入库, 0) AS 本月入库,
ISNULL(b.本月出库, 0) AS 本月出库
FROM (SELECT StoreID, PartsID, Rev,(SELECT SUM(Qty + QtyL) FROM 月结存表
WHERE StoreID = a.StoreID AND PartsID = a.PartsID AND Rev=a.Rev AND month(ReportDate) =11) AS 上月结存,
本月入库 = (case In_Out when 'I' then SUM(LogQty + LogQtyL) else 0 end),
本月出库 = case In_Out when 'O' then SUM(LogQty + LogQtyL) else 0 end) FROM 日志表 a
WHERE month(StoreLogDate) =12
GROUP BY StoreID, PartsID, Rev) a join 产品表 b
ON a.PartsID=b.PartsID INNER JOIN Inger_Config.dbo.f_getchildid(得到根树的子项) c
ON a.StoreID = c.id join Inger_Config.dbo.Accounts_ConfigTree s
ON a.StoreID = s.id
FROM (SELECT ISNULL(a.StoreID, b.StoreID) AS StoreID,
ISNULL(a.PartsID, b.PartsID) AS PartsID,
ISNULL(a.Rev, b.Rev) AS Rev,
ISNULL(a.上月结存,0) AS 上月结存,
ISNULL(a.本月入库, 0) AS 本月入库,
ISNULL(b.本月出库, 0) AS 本月出库
FROM (SELECT StoreID, PartsID, Rev,(SELECT SUM(Qty + QtyL) FROM 月结存表
WHERE StoreID = a.StoreID AND PartsID = a.PartsID AND Rev=a.Rev AND month(ReportDate) =11) AS 上月结存,
本月入库 = (case In_Out when 'I' then SUM(LogQty + LogQtyL) else 0 end),
本月出库 = case In_Out when 'O' then SUM(LogQty + LogQtyL) else 0 end) FROM 日志表 a
WHERE month(StoreLogDate) =12
GROUP BY StoreID, PartsID, Rev) a join 产品表 b
ON a.PartsID=b.PartsID INNER JOIN Inger_Config.dbo.f_getchildid(得到根树的子项) c
ON a.StoreID = c.id join Inger_Config.dbo.Accounts_ConfigTree s
ON a.StoreID = s.id
FROM
(SELECT ISNULL(a.StoreID, b.StoreID) AS StoreID,
ISNULL(a.PartsID, b.PartsID) AS PartsID,
ISNULL(a.Rev, b.Rev) AS Rev,
ISNULL(a.上月结存,0) AS 上月结存,
ISNULL(a.本月入库, 0) AS 本月入库,
ISNULL(b.本月出库, 0) AS 本月出库
FROM
(SELECT StoreID, PartsID, Rev,
(SELECT SUM(Qty + QtyL) FROM 月结存表
WHERE StoreID = a.StoreID AND PartsID = a.PartsID
AND Rev=a.Rev AND month(ReportDate) =11
) AS 上月结存,
本月入库 = SUM(case when In_Out = '1' then LogQty + LogQtyL
else -LogQty-LogQtyL end)
FROM 日志表 a
WHERE month(StoreLogDate) =12
GROUP BY StoreID, PartsID, Rev
) a
)a
join 产品表 b ON a.PartsID=b.PartsID
INNER JOIN Inger_Config.dbo.f_getchildid(得到根树的子项) c ON a.StoreID = c.id
join Inger_Config.dbo.Accounts_ConfigTree s ON a.StoreID = s.id
wzh1215(四脚蛇)的代码,出入会发生重复!
txlicenhe(马可) 这种方式应该不行吧邹建大哥这是表结构,其中用到大哥的树结构就没有列出了,只是包含一个字段
月结表
CREATE TABLE [StoreReport] (
[ReportDate] [smalldatetime] NOT NULL CONSTRAINT [DF_StoreReport_ReportDate] DEFAULT (getdate()),--结存时间
[StoreID] [int] NOT NULL ,--对应大哥的树结构的id
[PartsID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--产品编号
[Rev] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--产品的细小差别
[Qty] [decimal](10, 2) NULL CONSTRAINT [DF_StoreReport_Qty] DEFAULT (0),--数量
[QtyL] [decimal](10, 2) NULL CONSTRAINT [DF_StoreReport_QtyL] DEFAULT (0),--数量另一边(成双)
[Price] [money] NULL CONSTRAINT [DF_StoreReport_Price] DEFAULT (0),
CONSTRAINT [PK_StoreReport] PRIMARY KEY CLUSTERED
(
[ReportDate],
[StoreID],
[PartsID],
[Rev]
) ON [PRIMARY]
) ON [PRIMARY]
GO日志表
CREATE TABLE [StoreLog] (
[StoreType] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,--日志类别
[StoreNO] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--单号
[Line] [smallint] NOT NULL ,--行号
[StoreLogDate] [smalldatetime] NULL ,--日志时间
[DepID] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,--进出相关部门
[StoreID] [int] NULL ,--对应大哥的树结构的id
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,--产品编号
[Rev] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_StoreLog_Rev] DEFAULT ('*'),--别号
[LogQty] [decimal](10, 2) NULL ,--数量
[LogQtyL] [decimal](10, 2) NULL ,--数量另一边(成双)
[Price] [money] NULL CONSTRAINT [DF_StoreLog_Price] DEFAULT (0),
[in_out] [bit] NULL CONSTRAINT [DF_StoreLog_in_out] DEFAULT (1),--进还是出(1代表进,0代表出)
[go_over] [bit] NULL CONSTRAINT [DF_StoreLog_go_over] DEFAULT (0),--是否结存
CONSTRAINT [PK_StoreLog] PRIMARY KEY CLUSTERED
(
[StoreType],
[StoreNO],
[Line]
) ON [PRIMARY]
) ON [PRIMARY]
GO
产品表
CREATE TABLE [BCParts] (
[PartsID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,--产品编号
[Descriptions] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BCParts_BCname] DEFAULT (''),--产品描述
[Unit] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,--单位
CONSTRAINT [PK_BCParts] PRIMARY KEY CLUSTERED
(
[PartsID] DESC
) ON [PRIMARY]
) ON [PRIMARY]
GO
FROM (
select StoreID=ISNULL(a.StoreID,b.StoreID)
,PartsID=ISNULL(a.PartsID,b.PartsID)
,Rev=ISNULL(a.Rev,b.Rev)
,上月结存=isnull(上月结存,0)
,本月入库=isnull(本月入库,0)
,本月出库=isnull(本月出库,0)
from(
SELECT StoreID,PartsID,Rev,上月结存=SUM(Qty + QtyL)
FROM 月结存表
where month(ReportDate)=11
group by StoreID,PartsID,Rev
) a full join(
SELECT StoreID,PartsID,Rev
,本月入库=SUM(case In_Out when '1' then LogQty+LogQtyL else 0 end)
,本月出库=SUM(case In_Out when '0' then LogQty+LogQtyL else 0 end)
FROM 日志表 a
WHERE month(StoreLogDate)=12
GROUP BY StoreID, PartsID, Rev
) b on a.StoreID=b.StoreID AND a.PartsID=b.PartsID AND a.Rev=b.Rev
) a
join 产品表 b ON a.PartsID=b.PartsID
JOIN Inger_Config.dbo.f_getchildid(得到根树的子项) c ON a.StoreID = c.id
join Inger_Config.dbo.Accounts_ConfigTree s ON a.StoreID = s.id