期初库存：月度结转='201208'
期未库存：月度结转='201209'
物资入库
物资出库
要求计算：
期初库存+物资入库-物资出库=期未库存(或者实时库存表)
物资编码 期初库存 物资入库 物资出库 期未库存
001 ? ? ? ?
002 ? ? ? ?
003 ? ? ? ?if exists (select * from sysobjects where id = OBJECT_ID('[实时库存表]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [实时库存表]CREATE TABLE [实时库存表] (
[物资编码] [varchar] (50) NULL,
[数量] [decimal] (18,3) NULL,
[单价] [decimal] (18,2) NULL,
[金额] [decimal] (18,2) NULL)INSERT [实时库存表] ([物资编码],[数量],[单价],[金额]) VALUES ( N'001',99.000,5.00,495.00)
INSERT [实时库存表] ([物资编码],[数量],[单价],[金额]) VALUES ( N'002',26.000,2.00,52.00)
INSERT [实时库存表] ([物资编码],[数量],[单价],[金额]) VALUES ( N'003',60.000,3.00,190.00)
if exists (select * from sysobjects where id = OBJECT_ID('[物资出库]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [物资出库]CREATE TABLE [物资出库] (
[物资编码] [varchar] (50) NULL,
[出库数量] [decimal] (18,3) NULL,
[出库单价] [decimal] (18,2) NULL,
[出库金额] [decimal] (18,2) NULL,
[结转日期] [varchar] (50) NULL)INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'001',12.000,5.00,60.00,N'201209')
INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'002',6.000,2.00,12.00,N'201209')
INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'003',5.000,3.00,15.00,N'201209')
INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'001',12.000,5.00,60.00,N'201209')
INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'002',80.000,2.00,160.00,N'201209')
INSERT [物资出库] ([物资编码],[出库数量],[出库单价],[出库金额],[结转日期]) VALUES ( N'003',90.000,3.00,370.00,N'201209')
if exists (select * from sysobjects where id = OBJECT_ID('[物资入库]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [物资入库]CREATE TABLE [物资入库] (
[物资编码] [varchar] (50) NULL,
[入库数量] [decimal] (18,3) NULL,
[入库单价] [decimal] (18,2) NULL,
[入库金额] [decimal] (18,2) NULL,
[结转日期] [varchar] (50) NULL)INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'001',100.000,5.00,500.00,N'201209')
INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'003',35.000,3.00,105.00,N'201209')
INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'002',27.000,2.00,54.00,N'201209')
INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'001',13.000,5.00,65.00,N'201209')
INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'002',80.000,2.00,160.00,N'201209')
INSERT [物资入库] ([物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( N'003',100.000,3.00,300.00,N'201209')
if exists (select * from sysobjects where id = OBJECT_ID('[月度结转]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [月度结转]CREATE TABLE [月度结转] (
[序列号] [bigint] IDENTITY (1, 1) NOT NULL,
[物资编码] [varchar] (50) NULL,
[入库数量] [decimal] (18,3) NULL,
[入库单价] [decimal] (18,2) NULL,
[入库金额] [decimal] (18,2) NULL,
[结转日期] [varchar] (50) NULL)ALTER TABLE [月度结转] WITH NOCHECK ADD CONSTRAINT [PK_月度结转] PRIMARY KEY NONCLUSTERED ( [序列号] )
SET IDENTITY_INSERT [月度结转] ONINSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 1,N'001',10.000,5.00,50.00,N'201208')
INSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 2,N'002',5.000,2.00,10.00,N'201208')
INSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 3,N'003',20.000,3.00,60.00,N'201208')
INSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 4,N'001',99.000,5.00,495.00,N'201209')
INSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 5,N'002',26.000,2.00,52.00,N'201209')
INSERT [月度结转] ([序列号],[物资编码],[入库数量],[入库单价],[入库金额],[结转日期]) VALUES ( 6,N'003',60.000,3.00,190.00,N'201209')SET IDENTITY_INSERT [月度结转] OFF请把SQL执行就可以生成几张表，
--[月度结转]如是存本期间期初数的话
SELECT tt.结转日期,tt.物资编码,
SUM(tt.QC) AS QC,
SUM(tt.ZJ) AS ZJ,
SUM(tt.JS) AS JS,
SUM(tt.QC) + SUM(tt.ZJ) - SUM(tt.JS) AS QM
FROM (
SELECT t.结转日期,t.物资编码,0 AS QC,ISNULL(t.入库数量,0) AS ZJ,0 AS JS,0 AS QM
FROM [物资入库] AS t
UNION ALL
SELECT t.结转日期,t.物资编码,0 AS QC,0 AS ZJ,ISNULL(t.出库数量,0) AS JS,0 AS QM
FROM [物资出库] AS t
UNION ALL
SELECT t.结转日期,t.物资编码,ISNULL(t.入库数量,0) AS QC,0 AS ZJ,0 AS JS,0 AS QM
FROM [月度结转] AS t
) AS tt
GROUP BY tt.结转日期,tt.物资编码
ORDER BY tt.结转日期,tt.物资编码
-----------------------------------------
结转日期 物资编码 QC ZJ JS QM
-------------------------------------------------- -------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
201208 001 10.000 0.000 0.000 10.000
201208 002 5.000 0.000 0.000 5.000
201208 003 20.000 0.000 0.000 20.000
201209 001 99.000 113.000 24.000 188.000
201209 002 26.000 107.000 86.000 47.000
201209 003 60.000 135.000 95.000 100.000(6 行受影响)--结果显示上月期末与下月期初不等，请检查数据！！！