员工节工作日，节假日，班次的处理...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/*--调用示例
--查询工作日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
--查询休息日
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
--查询全部日期
SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
--*/
Create FUNCTION dbo.f_getdate(
@begin_date Datetime, --要查询的开始日期
@end_date Datetime, --要查询的结束日期
@bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
)RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(10))
AS
BEGIN
DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
INSERT INTO @tb(a) SELECT TOP 366 0
FROM sysobjects a ,sysobjects b
IF @bz=0
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE IF @bz=1
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
SET @begin_date=DATEADD(Day,366,@begin_date)
END
ELSE
WHILE @begin_date<=@end_date
BEGIN
INSERT INTO @re(Date,Weekday)
SELECT Date,DATENAME(Weekday,Date)
FROM(
SELECT Date=DATEADD(Day,ID,@begin_date)
FROM @tb
)a WHERE Date<=@end_date
SET @begin_date=DATEADD(Day,366,@begin_date)
END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
----Create Table----
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_WorkDay]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TB_WorkDay]
GO
CREATE TABLE [dbo].[TB_WorkDay] (
[WorkDay_ID] [int] IDENTITY (1, 1) NOT NULL ,
[User_ID] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[User_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Work_Year] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Work_Date] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Week_Day] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Flag] [int] NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TB_User]
GO
CREATE TABLE [dbo].[TB_User] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL ,
[User_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Shift] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TB_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TB_Holiday]
GO
CREATE TABLE [dbo].[TB_Holiday] (
[HYear] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[HDate] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
---Store Procedure---
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create Procedure Pr_GenerateWrokDate
AS
Declare @F_d as datetime
Declare @E_d as datetime
Declare @L_date as datetime
Declare @WeekDay as varchar(20)
Declare @User_ID varchar(10)
Declare @User_Name varchar(10)
Declare @Shift varchar(10)
Declare @Flag int
set @F_d = convert(varchar(10),dateadd(dd,-day(getdate())+1,getdate()),120)
set @E_d = convert(varchar(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),120)
Declare @A_Shift_Day Table
(id int identity(1,1),
Date datetime,
Weekday nvarchar(10)
)
insert into @A_Shift_Day
SELECT Date,Weekday FROM dbo.f_getdate('2009-07-01','2009-07-31',NUll)
where weekday='Saturday'
order by date
delete from @A_Shift_Day where id in ('2','4')
--select * from @A_Shift_Day
Declare Cur_User Cursor for
select User_ID, User_Name,Shift From Tb_User
OPEN Cur_User
FETCH NEXT FROM Cur_User
INTO @User_ID,
@User_Name,
@Shift
WHILE @@FETCH_STATUS = 0
BEGIN
Declare Cur_MonthDay Cursor for
SELECT Date,Weekday FROM dbo.f_getdate(@F_d,@E_d,NULL)
OPEN Cur_MonthDay
FETCH NEXT FROM Cur_MonthDay
INTO @L_date,
@WeekDay
WHILE @@FETCH_STATUS = 0
BEGIN
Begin
if @Shift='A'
if @WeekDay='Saturday' or @WeekDay='Sunday'
begin
Declare @l_x int
select @l_x=count(*) from @A_Shift_Day
where Date = @L_date
print @WeekDay +'---'+ convert(varchar(10),@l_x)
if @WeekDay='Saturday' and @l_x > 0
begin
if @l_x > 0
Set @flag = 1
else
Set @flag = 0
end
if @WeekDay='Sunday'
Set @flag = 1
end
else
Set @flag = 0
if @Shift='B'
if @WeekDay='Sunday'
Set @flag = 1
else
Set @flag = 0
End
Begin
Declare @l_xx int
Select @l_xx=Count(*) from TB_Holiday
where Hyear=year(getdate())
and HDate=Convert(varchar(10),@L_date,120)
if @l_xx > 0
Set @flag = 1
End
insert into TB_WorkDay(User_ID, User_Name,Work_Year, Work_Date, Week_Day, Flag)
Values
(
@user_id,
@User_Name,
year(getdate()),
Convert(varchar(10),@L_date,120),
@WeekDay,
@flag
)
FETCH NEXT FROM Cur_MonthDay
INTO @L_date,
@WeekDay
End
CLOSE Cur_MonthDay
DEALLOCATE Cur_MonthDay
FETCH NEXT FROM Cur_User
INTO @User_ID,
@User_Name,
@Shift
End
CLOSE Cur_User
DEALLOCATE Cur_User
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Procedure Pr_GetMonthDay
as
Declare @F_D as varchar(10)
Declare @E_D as varchar(10)
Declare @User_Name as varchar(20)
Declare @Work_Year as varchar(10)
Declare @Work_Date as Varchar(10)
Declare @Week_Day as varchar(20)
Declare @Flag as varchar(1)
Declare @Col_Data as varchar(1000)
set @F_d = convert(varchar(10),dateadd(dd,-day(getdate())+1,getdate()),120)
set @E_d = convert(varchar(10),dateadd(dd,-day(getdate()),dateadd(m,1,getdate())),120)
Begin
If right(@E_D,2) = '31'
Begin
Declare @TB_M31 Table
(
User_Name varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20),
D7 varchar(20),
D8 varchar(20),
D9 varchar(20),
D10 varchar(20),
D11 varchar(20),
D12 varchar(20),
D13 varchar(20),
D14 varchar(20),
D15 varchar(20),
D16 varchar(20),
D17 varchar(20),
D18 varchar(20),
D19 varchar(20),
D20 varchar(20),
D21 varchar(20),
D22 varchar(20),
D23 varchar(20),
D24 varchar(20),
D25 varchar(20),
D26 varchar(20),
D27 varchar(20),
D28 varchar(20),
D29 varchar(20),
D30 varchar(20),
D31 varchar(20)
)
End
If right(@E_D,2) = '30'
Begin
Declare @TB_M30 Table
(
User_Name varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20),
D7 varchar(20),
D8 varchar(20),
D9 varchar(20),
D10 varchar(20),
D11 varchar(20),
D12 varchar(20),
D13 varchar(20),
D14 varchar(20),
D15 varchar(20),
D16 varchar(20),
D17 varchar(20),
D18 varchar(20),
D19 varchar(20),
D20 varchar(20),
D21 varchar(20),
D22 varchar(20),
D23 varchar(20),
D24 varchar(20),
D25 varchar(20),
D26 varchar(20),
D27 varchar(20),
D28 varchar(20),
D29 varchar(20),
D30 varchar(20)
)
End
If right(@E_D,2)='29'
Begin
Declare @TB_M29 Table
(
User_Name varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20),
D7 varchar(20),
D8 varchar(20),
D9 varchar(20),
D10 varchar(20),
D11 varchar(20),
D12 varchar(20),
D13 varchar(20),
D14 varchar(20),
D15 varchar(20),
D16 varchar(20),
D17 varchar(20),
D18 varchar(20),
D19 varchar(20),
D20 varchar(20),
D21 varchar(20),
D22 varchar(20),
D23 varchar(20),
D24 varchar(20),
D25 varchar(20),
D26 varchar(20),
D27 varchar(20),
D28 varchar(20),
D29 varchar(20)
)
End
If right(@E_D,2) = '28'
Begin
Declare @TB_M28 Table
(
User_Name varchar(20),
D1 varchar(20),
D2 varchar(20),
D3 varchar(20),
D4 varchar(20),
D5 varchar(20),
D6 varchar(20),
D7 varchar(20),
D8 varchar(20),
D9 varchar(20),
D10 varchar(20),
D11 varchar(20),
D12 varchar(20),
D13 varchar(20),
D14 varchar(20),
D15 varchar(20),
D16 varchar(20),
D17 varchar(20),
D18 varchar(20),
D19 varchar(20),
D20 varchar(20),
D21 varchar(20),
D22 varchar(20),
D23 varchar(20),
D24 varchar(20),
D25 varchar(20),
D26 varchar(20),
D27 varchar(20),
D28 varchar(20)
)
End
---process day colomn---
Declare Cur_UDay cursor for
Select User_Name,Work_Year,Work_Date,Week_day,Flag
from TB_WorkDay
Open Cur_Uday
Fetch Next From Cur_Uday
into @User_Name,
@Work_Year,
@Work_Date,
@Week_day,
@Flag
While @@Fetch_Status=0
Begin
---Insert---
If @Work_Date =@F_D
if right(@E_D,2)='31'
Insert into @TB_M31(User_Name,D1)values(@user_Name,right(@Work_Date,2)+'/'+@Week_Day)
if right(@E_D,2)='30'
Insert into @TB_M30(User_Name,D1)values(@user_Name,right(@Work_Date,2)+'/'+@Week_Day)
if right(@E_D,2)='29'
Insert into @TB_M29(User_Name,D1)values(@user_Name,right(@Work_Date,2)+'/'+@Week_Day)
if right(@E_D,2)='28'
Insert into @TB_M28(User_Name,D1)values(@user_Name,right(@Work_Date,2)+'/'+@Week_Day)
---Update---
Else
print @Work_Date
if right(@E_D,2)='31'
Begin
If right(@Work_Date,2)='01'
Update @TB_M31 Set D1 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='02'
Update @TB_M31 Set D2 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='03'
Update @TB_M31 Set D3 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='04'
Update @TB_M31 Set D4 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='05'
Update @TB_M31 Set D5 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='06'
Update @TB_M31 Set D6 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='07'
Update @TB_M31 Set D7 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='08'
Update @TB_M31 Set D8 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='09'
Update @TB_M31 Set D9 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='10'
Update @TB_M31 Set D10 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='11'
Update @TB_M31 Set D11 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='12'
Update @TB_M31 Set D12 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='13'
Update @TB_M31 Set D13 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='14'
Update @TB_M31 Set D14 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='15'
Update @TB_M31 Set D15 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='16'
Update @TB_M31 Set D16 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='17'
Update @TB_M31 Set D17 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='18'
Update @TB_M31 Set D18 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='19'
Update @TB_M31 Set D19 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='20'
Update @TB_M31 Set D20 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='21'
Update @TB_M31 Set D21 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='22'
Update @TB_M31 Set D22 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='23'
Update @TB_M31 Set D23 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='24'
Update @TB_M31 Set D24 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='25'
Update @TB_M31 Set D25 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='26'
Update @TB_M31 Set D26 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='27'
Update @TB_M31 Set D27 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='28'
Update @TB_M31 Set D28 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='29'
Update @TB_M31 Set D29 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='30'
Update @TB_M31 Set D30 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='31'
Update @TB_M31 Set D31 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
End
if right(@E_D,2)='30'
Begin
If right(@Work_Date,2)='01'
Update @TB_M30 Set D1 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='02'
Update @TB_M30 Set D2 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='03'
Update @TB_M30 Set D3 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='04'
Update @TB_M30 Set D4 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='05'
Update @TB_M30 Set D5 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='06'
Update @TB_M30 Set D6 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='07'
Update @TB_M30 Set D7 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='08'
Update @TB_M30 Set D8 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='09'
Update @TB_M30 Set D9 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='10'
Update @TB_M30 Set D10 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='11'
Update @TB_M30 Set D11 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='12'
Update @TB_M30 Set D12 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='13'
Update @TB_M30 Set D13 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='14'
Update @TB_M30 Set D14 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='15'
Update @TB_M30 Set D15 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='16'
Update @TB_M30 Set D16 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='17'
Update @TB_M30 Set D17 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='18'
Update @TB_M30 Set D18 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='19'
Update @TB_M30 Set D19 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='20'
Update @TB_M30 Set D20 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='21'
Update @TB_M30 Set D21 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='22'
Update @TB_M30 Set D22 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='23'
Update @TB_M30 Set D23 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='24'
Update @TB_M30 Set D24 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='25'
Update @TB_M30 Set D25 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='26'
Update @TB_M30 Set D26 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='27'
Update @TB_M30 Set D27 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='28'
Update @TB_M30 Set D28 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='29'
Update @TB_M30 Set D29 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='30'
Update @TB_M30 Set D30 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
End
if right(@E_D,2)='29'
Begin
If right(@Work_Date,2)='01'
Update @TB_M29 Set D1 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='02'
Update @TB_M29 Set D2 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='03'
Update @TB_M29 Set D3 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='04'
Update @TB_M29 Set D4 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='05'
Update @TB_M29 Set D5 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='06'
Update @TB_M29 Set D6 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='07'
Update @TB_M29 Set D7 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='08'
Update @TB_M29 Set D8 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='09'
Update @TB_M29 Set D9 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='10'
Update @TB_M29 Set D10 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='11'
Update @TB_M29 Set D11 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='12'
Update @TB_M29 Set D12 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='13'
Update @TB_M29 Set D13 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='14'
Update @TB_M29 Set D14 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='15'
Update @TB_M29 Set D15 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='16'
Update @TB_M29 Set D16 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='17'
Update @TB_M29 Set D17 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='18'
Update @TB_M29 Set D18 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='19'
Update @TB_M29 Set D19 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='20'
Update @TB_M29 Set D20 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='21'
Update @TB_M29 Set D21 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='22'
Update @TB_M29 Set D22 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='23'
Update @TB_M29 Set D23 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='24'
Update @TB_M29 Set D24 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='25'
Update @TB_M29 Set D25 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='26'
Update @TB_M29 Set D26 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='27'
Update @TB_M29 Set D27 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='28'
Update @TB_M29 Set D28 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='29'
Update @TB_M29 Set D29 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
End
if right(@E_D,2)='28'
Begin If right(@Work_Date,2)='01'
Update @TB_M28 Set D1 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='02'
Update @TB_M28 Set D2 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='03'
Update @TB_M28 Set D3 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='04'
Update @TB_M28 Set D4 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='05'
Update @TB_M28 Set D5 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='06'
Update @TB_M28 Set D6 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='07'
Update @TB_M28 Set D7 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='08'
Update @TB_M28 Set D8 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='09'
Update @TB_M28 Set D9 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='10'
Update @TB_M28 Set D10 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='11'
Update @TB_M28 Set D11 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='12'
Update @TB_M28 Set D12 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='13'
Update @TB_M28 Set D13 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='14'
Update @TB_M28 Set D14 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='15'
Update @TB_M28 Set D15 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='16'
Update @TB_M28 Set D16 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='17'
Update @TB_M28 Set D17 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='18'
Update @TB_M28 Set D18 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='19'
Update @TB_M28 Set D19 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='20'
Update @TB_M28 Set D20 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='21'
Update @TB_M28 Set D21 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='22'
Update @TB_M28 Set D22 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='23'
Update @TB_M28 Set D23 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='24'
Update @TB_M28 Set D24 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='25'
Update @TB_M28 Set D25 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='26'
Update @TB_M28 Set D26 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='27'
Update @TB_M28 Set D27 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
If right(@Work_Date,2)='28'
Update @TB_M28 Set D28 = right(@Work_Date,2)+'/'+@Week_Day+'/'+@Flag where User_Name=@User_Name
End
Fetch Next From Cur_Uday
into @User_Name,
@Work_Year,
@Work_Date,
@Week_day,
@Flag
End
End
Select * from @TB_M31
Close Cur_UDay
Deallocate Cur_UDay
-----Test------
truncate table tb_workday
DECLARE @RC int
-- Set parameter values
EXEC @RC = [test].[dbo].[Pr_GenerateWrokDate]
select * from tb_workday
DECLARE @RC int
-- Set parameter values
EXEC @RC = [test].[dbo].[Pr_GetMonthDay]
