[鶴嘯九天] 向大家请教下关与考勤的问题

fa_ge 2008-04-22 10:51:52

下面这个是已经写好的统计考勤记录的存储过程。但有一个问题，节假日没有安排考勤排班(有的公司放假也要上班）,所以我要在下面的存储过程中
增加对节假日考勤统计。
Check_Work :考勤表
Kind --种类(所有，部门，员工)
Person_Number --case Kind=所有 then 空 case Kind=部门 then 部门编号 case Kind=员工 then 员工编号
BeginDate --开始考勤日期
EndDate --结束考勤日期
WorkSet_Number --班次编号

Fiesta :节假日
Type --种类(所有，部门，员工)
Person_Number --case Type=所有 then 空 case Type=部门 then 部门编号 case Type=员工 then 员工编号
BeginDate --开始考勤日期
EndDate --结束考勤日期
WorkSet_Number --班次编号

我在前台程序中写好了节假日允许排班,现在就要把它和正常考勤一起处理。就要修改下面的存储过程。
该放在哪里处理会好些，请大家多给出些建议。

...全文

119 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

flairsky 2008-04-22

打赏
举报

都是星星级的人在讨论，我就路过下算了

dobear_0922 2008-04-22

打赏
举报

--得到今天是星期几
SELECT @intSign1=DATEPART(weekday,@CurDate)
SET @intSign1=5--屏蔽星期六，星期天不处理数据。

----------
第二句覆盖了第一句？

fa_ge 2008-04-22

打赏
举报

小熊和老龟也来了，来的正是时候，帮我参考下怎么解决.呵呵

dobear_0922 2008-04-22

打赏
举报

关注一下，学习

dawugui 2008-04-22

打赏
举报

[Quote=引用 7 楼 fa_ge 的回复:]
你把他们统一放在一个考勤表里面
然后用一个字段进行标志区分节假日与正常考勤统一处理不行吗

------------
小虫，你的建议很好.但表结构已经定好了，不好修改。
[/Quote]
你自己增加一个节假日的列表就行了.不用这么麻烦.

fa_ge 2008-04-22

打赏
举报

你把他们统一放在一个考勤表里面
然后用一个字段进行标志区分节假日与正常考勤统一处理不行吗

------------
小虫，你的建议很好.但表结构已经定好了，不好修改。

kk19840210 2008-04-22

打赏
举报

哇好长

kk19840210 2008-04-22

打赏
举报

你把他们统一放在一个考勤表里面
然后用一个字段进行标志区分节假日与正常考勤统一处理不行吗

子陌红尘 2008-04-22

打赏
举报

路过，支持一下

fa_ge 2008-04-22

打赏
举报



--开如处理考勤数据  

--得到应处理的数据  

sign6:   

SELECT @CurRecord=COUNT(*) FROM #Temp2  

IF @CurRecord=0   

 GOTO sign7  

SELECT TOP 1 @CurDate=Date,@intSign1=[id],@BeginDate=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,BeginDate)AS VARCHAR(2))+':'+CAST(DATEPART(n,BeginDate)AS VARCHAR(2))+':00',  

@EndDate=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,EndDate)AS VARCHAR(2))+':'+CAST(DATEPART(n,EndDate)AS VARCHAR(2))+':00',  

@BeginDate1=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,BeginDate1)AS VARCHAR(2))+':'+CAST(DATEPART(n,BeginDate1)AS VARCHAR(2))+':00',  

  

@EndDate1=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,EndDate1)AS VARCHAR(2))+':'+CAST(DATEPART(n,EndDate1)AS VARCHAR(2))+':00.000',  

  

@BeginDate2=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,BeginDate2)AS VARCHAR(2))+':'+CAST(DATEPART(n,BeginDate2)AS VARCHAR(2))+':00',  

@EndDate2=CAST(YEAR(Date)AS VARCHAR(4))+'-'+CAST(MONTH(Date)AS VARCHAR(2))+'-'+CAST(DAY(Date)AS VARCHAR(2))+' '+CAST(DATEPART (hh,EndDate2)AS VARCHAR(2))+':'+CAST(DATEPART(n,EndDate2)AS VARCHAR(2))+':00',  

@Person_Number=Person_Number,@NumMidst_Rest=ISNULL(Midst_Rest,0) FROM #Temp2  

  

SET @BeginDate3=NULL  

SET @EndDate3=NULL  

SET @Sign=''  

SET @Sign1=''  

SET @Sign2=''  

SET @Sign3=''  

SET @Sign4=''  

SET @Qty1=0  

SET @Qty2=0  

SET @Qty3=0  

  

  

--*****得到上班的打卡时间*****  

SELECT @BeginDate3=open_time FROM ROPEN WHERE DATEDIFF(n,@BeginDate,open_time)<=0 AND DATEDIFF(n,@BeginDate1,open_time)>=0 AND gh=@Person_Number  

ORDER BY Open_Time desc  

--如果没有正常上班,处理其它数据  

IF @BeginDate3 IS NULL  

BEGIN  

 --查看是否是补签的  

 SELECT @BeginDate3=@BeginDate FROM Mend WHERE Person_Number=@Person_Number AND @BeginDate BETWEEN BeginDate AND EndDate  

 IF @BeginDate3 IS NULL   

 BEGIN  

  --获取是否请假  

  SELECT @BeginDate3=@BeginDate FROM Leave WHERE Person_Number=@Person_Number AND @BeginDate BETWEEN BeginDate AND EndDate  

  IF @BeginDate3 IS NULL     

  BEGIN  

   --查看是否外出  

   SELECT @BeginDate3=@BeginDate FROM Evection WHERE Person_Number=@Person_Number AND @BeginDate BETWEEN BeginDate AND EndDate  

   IF @BeginDate3 IS NULL     

   BEGIN  

    --获取是否是迟到打卡  

    SELECT @BeginDate3=open_time FROM ROPEN WHERE DATEDIFF(n,@BeginDate,open_time)>0 AND DATEDIFF(n,@EndDate1,open_time)<=0 AND gh=@Person_Number  

    ORDER BY Open_Time DESC  

    IF @BeginDate3 IS NULL  

     SET @Sign='未打'  

    ELSE   

     SET @Sign='迟到'  

   END  

   ELSE  

    SET @Sign='外出'  

  END  

  ELSE  

   SET @Sign='请假'  

     

 END  

 ELSE  

  SET @Sign='补签'  

END  

ELSE  

 SET @Sign='正常'  

  

--*****得到下班的打卡时间******  

SELECT @EndDate3=open_time FROM ROPEN WHERE DATEDIFF(n,@EndDate,open_time)>=0 AND DATEDIFF(n,@EndDate2,open_time)<=0 AND gh=@Person_Number  

--如果没有正常下班,处理其它数据  

IF @EndDate3 IS NULL  

BEGIN  

 --查看是否是补签的  

 SELECT @EndDate3=@EndDate FROM Mend WHERE Person_Number=@Person_Number AND @EndDate BETWEEN BeginDate AND EndDate  

 IF @EndDate3 IS NULL  

 BEGIN   

  --获取是否请假  

  SELECT @EndDate3=@EndDate FROM Leave WHERE Person_Number=@Person_Number AND @EndDate BETWEEN BeginDate AND EndDate  

  IF @EndDate3 IS NULL  

  BEGIN  

   --查看是否外出  

   SELECT @EndDate3=@EndDate FROM Evection WHERE Person_Number=@Person_Number AND @EndDate BETWEEN BeginDate AND EndDate  

   IF @EndDate3 IS NULL   

   BEGIN  

    --获取卡是否早退。  

    SELECT @EndDate3=open_time FROM ROPEN WHERE DATEDIFF(n,@EndDate,open_time)<0 AND DATEDIFF(n,@BeginDate2,open_time)>=0 AND gh=@Person_Number  

    ORDER BY Open_Time   

    IF @EndDate3 IS NULL  

     SET @Sign1='未打'  

    ELSE   

     SET @Sign1='早退'  

   END  

   ELSE  

    SET @Sign1='外出'  

  END  

  ELSE  

   SET @Sign1='请假'  

 END  

 ELSE  

  SET @Sign1='补签'  

END  

ELSE  

 SET @Sign1='正常'  

SET @Sign2='正常'  

IF @Sign='早退' OR  @Sign1='早退'  

 SET @Sign2='早退'  

IF @Sign='迟到' OR @Sign1='迟到'  

 SET @Sign2='迟到'  

IF @Sign='未打' OR  @Sign1='未打'  

 SET @Sign2='旷工'  

--得到请假时间,当请假的时候是全天时，要减去中场休息时间。  

SET @Qty4=0  

SELECT @Qty4=CAST(SUM(CASE WHEN BeginDate<=@BeginDate AND EndDate>=@EndDate THEN DATEDIFF(n,@BeginDate,@EndDate)-ISNULL(@NumMidst_Rest,0) ELSE  

DATEDIFF(n,CASE WHEN BeginDate<@BeginDate THEN @BeginDate ELSE BeginDate END,CASE WHEN EndDate>@EndDate THEN @EndDate ELSE EndDate END)END)  

as numeric(18,2)) FROM Leave   

WHERE ((BeginDate BETWEEN @BeginDate AND @EndDate)OR (EndDate BETWEEN @BeginDate AND @EndDate)  

OR(DATEDIFF(N,BeginDate,@BeginDate)>=0 AND DATEDIFF(N,EndDate,@EndDate)<=0)) AND Person_Number=@Person_Number  

--将统计出的数据插入到统计表当中。  

INSERT INTO Count(Person_Number,BeginDate,EndDate,BeginDate3,EndDate3,Sign,Date,Sign1,Sign2,Midst_Rest,Qty4)  

VALUES(@Person_Number,@BeginDate,@EndDate,@BeginDate3,@EndDate3,@Sign2,@CurDate,@Sign,@Sign1,@NumMidst_Rest,@Qty4)  

DELETE FROM #Temp2 WHERE [id]=@intSign1  

GOTO sign6  

sign7:  

  

DROP TABLE #Temp2

fa_ge 2008-04-22

打赏
举报

sign1:  

 SELECT @CurRecord=COUNT(*) FROM #Temp1  

 IF @CurRecord=0--当数据处理完成,往下跳  

  GOTO sign2  

 SET @NumMidst_Rest=0  

 SELECT TOP 1 @BeginDate=BeginDate,@EndDate=EndDate,@intSign=[id],@Sat_Or_Work=Sat_Or_Work,@Friday_Or_Work=Friday_Or_Work,  

 @BeginDate2=BeginDate1,@EndDate2=EndDate1,@BeginDate3=BeginDate2,@EndDate3=EndDate2,@NumMidst_Rest=ISNULL(Midst_Rest,0) FROM #Temp1  

 --取得起始时间，结束时间  

 SET @BeginDate=CAST(YEAR(@CurDate) as varchar(4))+'-'+CAST(MONTH(@CurDate)as varchar(2))+'-'+CAST(DAY(@CurDate)as varchar(2))+' '+  

  CAST(DATEPART(HOUR,@BeginDate)as varchar(2))+':'+CAST(DATEPART(MINUTE,@BeginDate)as varchar(2))  

 SET @EndDate=CAST(YEAR(@CurDate) as varchar(4))+'-'+CAST(MONTH(@CurDate)as varchar(2))+'-'+CAST(DAY(@CurDate)as varchar(2))+' '+  

  CAST(DATEPART(HOUR,@EndDate)as varchar(2))+':'+CAST(DATEPART(MINUTE,@EndDate)as varchar(2))  

 --得到特殊上班时间  

 SET @BeginDate1=null  

 SET @EndDate1=null  

 SELECT @BeginDate1=BeginDate,@EndDate1=EndDate FROM Especial_Work WHERE DATEDIFF(DAY,@BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,@EndDate,@CurDate)<=0  

  

 IF DATEDIFF(n,@BeginDate,@BeginDate1)<=0 AND DATEDIFF(n,@EndDate,@EndDate1)>=0--就是在特殊时间内,直接插入班次  

 BEGIN  

  INSERT #Temp2(Person_Number,BeginDate,EndDate,Date,BeginDate1,EndDate1,BeginDate2,EndDate2)  

  VALUES(@Person_Number,@BeginDate,@EndDate,@CurDate,@BeginDate2,@EndDate2,@BeginDate3,@EndDate3)  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

  

 --得到节假日时间  

 SET @BeginDate1=null  

 SET @EndDate1=null  

 SELECT @BeginDate1=BeginDate,@EndDate1=EndDate FROM Fiesta WHERE DATEDIFF(DAY,BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,EndDate,@CurDate)<=0  

 IF DATEDIFF(n,@BeginDate,@BeginDate1)<=0 AND DATEDIFF(n,@EndDate,@EndDate1)>=0--当是节假日时,不上班!  

 BEGIN  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

  

 --得到今天是星期几  

 SELECT  @intSign1=DATEPART(weekday,@CurDate)  

 SET @intSign1=5--屏蔽星期六，星期天不处理数据。  

  

 --正常上班,不是星期六或星期天,不需要判断  

 IF @intSign1<7 AND @intSign1<>1  

 BEGIN  

  INSERT #Temp2(Person_Number,BeginDate,EndDate,Date,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

  VALUES(@Person_Number,@BeginDate,@EndDate,@CurDate,@BeginDate2,@EndDate2,@BeginDate3,@EndDate3,@NumMidst_Rest)  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

  

  

 IF @intSign1=7 AND @Sat_Or_Work=1--当星期六也要上班时,插入数据  

 BEGIN  

  INSERT #Temp2(Person_Number,BeginDate,EndDate,Date,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

  VALUES(@Person_Number,@BeginDate,@EndDate,@CurDate,@BeginDate2,@EndDate2,@BeginDate3,@EndDate3,@NumMidst_Rest)  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

 ELSE   

 IF @intSign1=7  

 BEGIN  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

  

 IF @intSign1=1 AND @Friday_Or_Work=1--当星期天也要上班时,插入数据  

 BEGIN  

  INSERT #Temp2(Person_Number,BeginDate,EndDate,Date,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

  VALUES(@Person_Number,@BeginDate,@EndDate,@CurDate,@BeginDate2,@EndDate2,@BeginDate3,@EndDate3,@NumMidst_Rest)  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

 ELSE   

 IF @intSign1=1  

 BEGIN  

  DELETE FROM #Temp1 WHERE [id]=@intSign  

  GOTO sign1  

 END  

  

sign2:  

 SET @CurDate=DATEADD(DAY,1,@CurDate)  

 GOTO sign3  

sign4:  

 DELETE FROM #Temp WHERE Person_Number=@Person_Number  

 SELECT @CurRecord=COUNT(*)FROM #Temp  

 IF @CurRecord>0  

  GOTO sign5  

DROP TABLE #Temp  

DROP TABLE #Temp1

fa_ge 2008-04-22

打赏
举报



CREATE PROCEDURE Count_Report(@byvDept varchar(15),@byvPerson_Number varchar(15),@byvStartDate datetime,@byvEndDate datetime)  

AS  

DECLARE @CurRecord numeric(10)  

DECLARE @intSign numeric(10)  

DECLARE @intSign1 numeric(10)  

DECLARE @Person_Number varchar(15)--存放工号  

DECLARE @Dept_No varchar(15)--部门号  

DECLARE @CurDate datetime--存放当前日期  

DECLARE @BeginDate datetime  

DECLARE @EndDate datetime  

DECLARE @BeginDate1 datetime  

DECLARE @EndDate1 datetime  

DECLARE @BeginDate2 datetime  

DECLARE @EndDate2 datetime  

DECLARE @BeginDate3 datetime  

DECLARE @EndDate3 datetime  

DECLARE @BeginDate4 datetime  

DECLARE @EndDate4 datetime  

DECLARE @Sat_Or_Work bit  

DECLARE @Friday_Or_Work bit  

DECLARE @Sign varchar(10)  

DECLARE @Sign1 varchar(10)  

DECLARE @Sign2 varchar(10)  

DECLARE @Sign3 varchar(10)  

DECLARE @Sign4 varchar(15)  

DECLARE @Qty1 numeric(18,4)  

DECLARE @Qty2 numeric(18,4)  

DECLARE @Qty3 numeric(18,4)  

DECLARE @Qty4 numeric(18,4)--请假时间  

DECLARE @NumMidst_Rest numeric(18,4)--中间休息时间  

--删除以前处理过的数据  

DELETE Count FROM EMPLOYEE  

WHERE EMPLOYEE.gh=Count.Person_Number  

AND EMPLOYEE.bumen_bh LIKE '%'+@byvDept+'%' AND Count.Person_Number LIKE '%'+@byvPerson_Number+'%'  

AND DATEDIFF(DAY,Date,@byvStartDate)<=0 AND DATEDIFF(DAY,Date,@byvEndDate)>=0  

  

  

--用于存放员工列表  

CREATE TABLE #Temp (  

 [Person_Number] [varchar](15) NOT NULL,  

 [Dept_No] [varchar](15) NOT NULL  

) ON [PRIMARY]  

  

--存放应上班列表, 没有经过处理的  

CREATE TABLE #Temp1 (  

 id numeric(10, 0) NOT NULL IDENTITY (1, 1),  

 [Person_Number] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,  

 [BeginDate] [datetime] NOT NULL ,  

 [EndDate] [datetime] NOT NULL ,  

 [BeginDate1] [datetime] NULL ,--上班开始考勤时间  

 [EndDate1] [datetime] NULL ,--上班结束考勤时间  

 [BeginDate2] [datetime] NULL ,--下班开始考勤时间  

 [EndDate2] [datetime] NULL ,--下班结束考勤时间  

 [Sat_Or_Work] [bit] NULL,  

 [Friday_Or_Work] [bit] NULL,  

 [Midst_Rest] NUMERIC(18,4)--中间休息时间  

) ON [PRIMARY]  

  

--存放应上班列表  

CREATE TABLE #Temp2 (  

 id numeric(10, 0) NOT NULL IDENTITY (1, 1),  

 [Person_Number] [varchar] (15) COLLATE Chinese_PRC_CI_AS NOT NULL ,  

 [BeginDate] [datetime] NOT NULL ,--上班时间  

 [EndDate] [datetime] NOT NULL ,--下班时间  

 [BeginDate1] [datetime] NULL ,--上班开始考勤时间  

 [EndDate1] [datetime] NULL ,--上班结束考勤时间  

 [BeginDate2] [datetime] NULL ,--下班开始考勤时间  

 [EndDate2] [datetime] NULL ,--下班结束考勤时间  

 [Date][datetime]NOT NULL,  

 [Midst_Rest] NUMERIC(18,4)--中间休息时间  

) ON [PRIMARY]  

--插入员工列表  

INSERT INTO #Temp(Person_Number,Dept_No)  

SELECT gh,bumen_bh FROM EMPLOYEE WHERE ISNULL(Curstate,'')<>'03'  

AND bumen_bh LIKE '%'+@byvDept+'%' AND gh LIKE '%'+@byvPerson_Number+'%'  

--得到员工工号,部门号  

sign5:  

SELECT TOP 1 @Person_Number=Person_Number,@Dept_No=Dept_No FROM #Temp  

SET @CurDate=@byvStartDate  

sign3:  

IF DATEDIFF(DAY,@byvEndDate,@CurDate)>0  

 GOTO sign4  

TRUNCATE TABLE #Temp1  

--看排班中是否有该员工的单  

SELECT @intSign=COUNT(*) FROM Check_Work WHERE DATEDIFF(DAY,BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,EndDate,@CurDate)<=0  

AND (Kind='所有' OR(Kind='部门' AND Person_Number=@Dept_No)OR(Kind='员工' AND Person_Number=@Person_Number))  

  

IF @intSign<>0--当有排考勤时,直接插入到应上班列表中  

BEGIN  

 --插入员工安排的班次  

 INSERT #Temp1(Person_Number,BeginDate,EndDate,Sat_Or_Work,Friday_Or_Work,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

 SELECT @Person_Number,B.BeginDate,B.EndDate,B.Sat_Or_Work,Friday_Or_Work,B.BeginDate1,B.EndDate1,B.BeginDate2,B.EndDate2,Midst_Rest   

 FROM Check_Work A  

 LEFT JOIN WorkSet B  

 ON A.WorkSet_Number=B.Number  

 WHERE DATEDIFF(DAY,A.BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,A.EndDate,@CurDate)<=0  

 AND ((Kind='员工' AND Person_Number=@Person_Number))  

 --插入部门安排的班次  

 INSERT #Temp1(Person_Number,BeginDate,EndDate,Sat_Or_Work,Friday_Or_Work,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

 SELECT @Person_Number,B.BeginDate,B.EndDate,B.Sat_Or_Work,Friday_Or_Work,B.BeginDate1,B.EndDate1,B.BeginDate2,B.EndDate2,Midst_Rest   

 FROM Check_Work A  

 LEFT JOIN WorkSet B  

 ON A.WorkSet_Number=B.Number  

 WHERE DATEDIFF(DAY,A.BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,A.EndDate,@CurDate)<=0  

 AND (Kind='部门' AND Person_Number=@Dept_No)  

 AND @Person_Number NOT IN(SELECT Person_Number FROM #Temp1)  

   

 --插入所有人安排的班次  

 INSERT #Temp1(Person_Number,BeginDate,EndDate,Sat_Or_Work,Friday_Or_Work,BeginDate1,EndDate1,BeginDate2,EndDate2,Midst_Rest)  

 SELECT @Person_Number,B.BeginDate,B.EndDate,B.Sat_Or_Work,Friday_Or_Work,B.BeginDate1,B.EndDate1,B.BeginDate2,B.EndDate2,Midst_Rest   

 FROM Check_Work A  

 LEFT JOIN WorkSet B  

 ON A.WorkSet_Number=B.Number  

 WHERE DATEDIFF(DAY,A.BeginDate,@CurDate)>=0 AND DATEDIFF(DAY,A.EndDate,@CurDate)<=0  

 AND (Kind='所有')  

 AND @Person_Number NOT IN(SELECT Person_Number FROM #Temp1)  

END /*ELSE --当没有排,则插入默认的班次  

 INSERT #Temp1(Person_Number,BeginDate,EndDate,Sat_Or_Work,Friday_Or_Work,BeginDate1,EndDate1,BeginDate2,EndDate2)  

 SELECT @Person_Number,BeginDate,EndDate,Sat_Or_Work,Friday_Or_Work,BeginDate1,EndDate1,BeginDate2,EndDate2 FROM WorkSet  

 WHERE [Default]=1*/