财务月度的创建及生成

CREATE TABLE [tbmcd_salary_config_monthly] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [editor] [int] NOT NULL  DEFAULT ('0'),
 [edittime] [varchar] (100)  DEFAULT (''),
 [monthly] [varchar] (100)  DEFAULT (''),
 [monthly_type] [int] NOT NULL  DEFAULT ('0'),
 [left_date] [varchar] (50)  DEFAULT (''),
 [right_date] [varchar] (50)  DEFAULT (''),
 [workdays] [numeric](18, 2) NOT NULL  DEFAULT ('24'),
 [description] [varchar] (200)  DEFAULT (''),
 CONSTRAINT [PK_tbmcd_salary_config_monthly] PRIMARY KEY  CLUSTERED
 (
  [id]
 )  ON [PRIMARY]
) ON [PRIMARY]
GO

truncate table tbmcd_salary_config_monthly

declare @left_date    varchar(10)
declare @right_date   varchar(10)
declare @cnt int

set @left_date = '2008-01-01'
set @right_date = '2008-02-01'
set @cnt = 24

while (@cnt > 0)
begin
  insert into tbmcd_salary_config_monthly (edittime, monthly, monthly_type,left_date, right_date,workdays,description)
  select convert(varchar(19),getdate(),121),substring(@left_date,1,4) + substring(@left_date,6,2) + '-0', 0, @left_date, @right_date, 24,'全月'

 insert into tbmcd_salary_config_monthly (edittime, monthly, monthly_type,left_date, right_date,workdays,description)
 select convert(varchar(19),getdate(),121),
substring(@left_date,1,4) + substring(@left_date,6,2) + '-1',
1,
@left_date,
convert(varchar(10), dateadd(day, 15, @left_date), 121),
12,
'上半月'

 insert into tbmcd_salary_config_monthly (edittime, monthly, monthly_type,left_date, right_date,workdays,description)
 select convert(varchar(19),getdate(),121),
substring(@left_date,1,4) + substring(@left_date,6,2) + '-2',
2,
convert(varchar(10), dateadd(day, 15, @left_date), 121),
@right_date,
12,
'下半月'

  set @cnt = @cnt - 1
  set @left_date =  convert(varchar(10), dateadd(month,1,@left_date), 121)
  set @right_date = convert(varchar(10),dateadd(month,1,@right_date)  ,121)
end

select * from tbmcd_salary_config_monthly