22,210
社区成员
发帖
与我相关
我的任务
分享
/****** 对象: Table [dbo].[报溢单_D] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[报溢单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_报溢单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[报溢单_M] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[报溢单_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
[所属科目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_报溢单_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[成本调价单_D] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[成本调价单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[原单价] [decimal](18, 2) NULL,
[调整后价] [decimal](18, 2) NULL,
CONSTRAINT [PK_成本调价单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[成本调价单_M] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[成本调价单_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
[所属科目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_成本调价单_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[科目表] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[科目表](
[编号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[科目名称] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL,
[收支标志] [int] NULL,
CONSTRAINT [PK_科目表] PRIMARY KEY CLUSTERED
(
[编号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[商品报损_M] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品报损_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
[所属科目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_商品报损_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[商品报损单_D] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品报损单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_商品报损单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[销售成本_M] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[销售成本_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
[所属科目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_销售成本_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[销售成本单_D] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[销售成本单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_销售成本单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[自定义科目_M] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[自定义科目_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
[所属科目] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_自定义科目_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[自定义科目_D] 脚本日期: 11/04/2010 23:13:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[自定义科目_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[金额] [decimal](18, 2) NULL,
CONSTRAINT [PK_自定义科目_D] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
delete 科目表
delete 报溢单_M
delete 报溢单_D
delete 成本调价单_M
delete 成本调价单_D
delete 销售成本_M
delete 销售成本单_D
delete 商品报损_M
delete 商品报损单_D
delete 自定义科目_M
delete 自定义科目_D
insert into 科目表 values('001','报溢收入',1,0)
insert into 科目表 values('002','成本调价收入',1,0)
insert into 科目表 values('003','销售成本',-1,0)
insert into 科目表 values('004','商品报损',-1,0)
insert into 科目表 values('005','利息收入',1,1)
insert into 科目表 values('006','工资支出',-1,1)
insert into 报溢单_M values('by001','2010-10-10','001')
insert into 报溢单_D values('by001','101',10,2)
insert into 报溢单_D values('by001','102',5,6)
insert into 成本调价单_M values('ct001','2010-10-10','002')
insert into 成本调价单_D values('ct001','105',100,2,1.8)
insert into 成本调价单_D values('ct001','109',20,6,5)
insert into 销售成本_M values('xs001','2010-10-10','003')
insert into 销售成本单_D values('xs001','110',5,3)
insert into 销售成本单_D values('xs001','115',2,8)
insert into 商品报损_M values('bs001','2010-10-12','004')
insert into 商品报损单_D values('bs001','110',8,2)
insert into 商品报损单_D values('bs001','115',9,6)
insert into 自定义科目_M values('A100','2010-10-20','005')
insert into 自定义科目_M values('A101','2010-10-21','005')
insert into 自定义科目_M values('B201','2010-10-22','006')
insert into 自定义科目_D values('A100',50)
insert into 自定义科目_D values('A101',60)
insert into 自定义科目_D values('B201',200)
declare @begintime datetime,@endtime datetime
set @begintime='2010-10-10'
set @endtime='2010-10-20'
select 编号,科目名称,
case 科目名称
when '报溢收入' then (select ltrim(sum(数量*单价)) 报溢收入 from 报溢单_M inner join 报溢单_D on 报溢单_M.单号=报溢单_D.单号 where 日期 between @begintime and @endtime )
when '成本调价收入' then (select ltrim(sum(原单价-调整后价)) 成本调价收入 from 成本调价单_M inner join 成本调价单_D on 成本调价单_M.单号=成本调价单_D.单号 where 日期 between @begintime and @endtime)
when '销售成本' then (select ltrim(sum(数量*单价)) 销售成本收入 from 销售成本_M inner join 销售成本单_D on 销售成本_M.单号=销售成本单_D.单号 where 日期 between @begintime and @endtime)
when '商品报损' then (select ltrim(sum(数量*单价)) 商品报损收入 from 商品报损_M inner join 商品报损单_D on 商品报损_M.单号=商品报损单_D.单号 where 日期 between @begintime and @endtime) else '' end 本期发生额
from 科目表
GO
编号 科目名称 本期发生额
-------------------- ------------------------------------------------------------ -----------------------------------------
001 报溢收入 50.00
002 成本调价收入 1.20
003 销售成本 31.00
004 商品报损 74.00
005 获赠收入
(5 行受影响)
/****** 对象: Table [dbo].[科目表] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[科目表](
[编号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[科目名称] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL,
[收支标志] [int] NULL,
CONSTRAINT [PK_科目表] PRIMARY KEY CLUSTERED
(
[编号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[报溢单_M] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[报溢单_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
CONSTRAINT [PK_报溢单_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[报溢单_D] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[报溢单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_报溢单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[成本调价单_M] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[成本调价单_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
CONSTRAINT [PK_成本调价单_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[成本调价单_D] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[成本调价单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[原单价] [decimal](18, 2) NULL,
[调整后价] [decimal](18, 2) NULL,
CONSTRAINT [PK_成本调价单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[销售成本_M] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[销售成本_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
CONSTRAINT [PK_销售成本_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[销售成本单_D] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[销售成本单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_销售成本单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[商品报损_M] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品报损_M](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[日期] [datetime] NULL,
CONSTRAINT [PK_商品报损_M] PRIMARY KEY CLUSTERED
(
[单号] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** 对象: Table [dbo].[商品报损单_D] 脚本日期: 11/04/2010 13:49:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[商品报损单_D](
[单号] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[商品编码] [varchar](20) COLLATE Chinese_PRC_CI_AS NOT NULL,
[数量] [int] NULL,
[单价] [decimal](18, 2) NULL,
CONSTRAINT [PK_商品报损单_D] PRIMARY KEY CLUSTERED
(
[单号] ASC,
[商品编码] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
delete 科目表
delete 报溢单_M
delete 报溢单_D
delete 成本调价单_M
delete 成本调价单_D
delete 销售成本_M
delete 销售成本单_D
delete 商品报损_M
delete 商品报损单_D
insert into 科目表 values('001','报溢收入',1)
insert into 科目表 values('002','成本调价收入',1)
insert into 科目表 values('003','销售成本',-1)
insert into 科目表 values('004','商品报损',-1)
insert into 科目表 values('005','获赠收入',1)
insert into 报溢单_M values('by001','2010-10-10 10:15:20')
insert into 报溢单_D values('by001','101',10,2)
insert into 报溢单_D values('by001','102',5,6)
insert into 成本调价单_M values('ct001','2010-10-10 20:01:15')
insert into 成本调价单_D values('ct001','105',100,2,1.8)
insert into 成本调价单_D values('ct001','109',20,6,5)
insert into 销售成本_M values('xs001','2010-10-10 08:10:25')
insert into 销售成本单_D values('xs001','110',5,3)
insert into 销售成本单_D values('xs001','115',2,8)
insert into 商品报损_M values('bs001','2010-10-12 12:10:11')
insert into 商品报损单_D values('bs001','110',10,2)
insert into 商品报损单_D values('bs001','115',9,6)