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 = '2017-01-01'
Set @ right_date = '2017-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, 'full month'
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 (days, 15, @ left_date), 121 ),
12,
'Last half month'
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 (days, 15, @ left_date), 121 ),
@ Right_date,
12,
'Next half month'
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