--从起始日期开始每三天三天的找!
declare @n int --@n为n天旷工天数
declare @a date
set @n=n
set @a=起始日期
while datediff(day,@a,终止日期)>3
begin
select ID
where boBgn=NULL and boEnd=NULL
and WorkDate between @a and DateAdd(day,3,@a)
and a>=2*@n
set @a=DateAdd(day,1,@a)
end
declare @n int --@n为n天旷工天数
declare @a date
set @n=n
set @a=起始日期
while datediff(day,@a,终止日期)>3
begin
select ID
where boBgn=NULL and boEnd=NULL
and WorkDate between @a and DateAdd(day,3,@a)
and a>=2*@n
set @a=DateAdd(day,1,@a)
end
AS
SELECT * FROM Work0301
UNION
SELECT * FROM Work0302
UNION
...
UNION
SELECT * FROM Work0308
-- 先把矿工记录保存在临时表中
DECLARE @w TABLE(ID int, WorkDate datetime, boBgn bit, boEnd bit)INSERT INTO @w
SELECT ID, WorkDate, boBgn, boEnd
FROM v_Work03
WHERE WorkDate BETWEEN '2003-07-01' AND '2003-08-31' AND boBgn = 0 AND boEnd = 0
-- 再做查询
DECLARE @n int
DECLARE @i int
DECLARE @sql varchar(4000)SET @n = 3
SET @i = 1
SET @sql = 'SELECT x.ID FROM @w t0'
WHILE @i < @n
BEGIN
SET @sql = @sql + ' INNER JOIN @w t' + RTRIM(CAST(@i AS char))
+ ' ON DATEDIFF(day, t0.WorkDate, t' + RTRIM(CAST(@i AS char))
+ '.WorkDate) = ' + RTRIM(CAST(@i AS char))
SET @i = @i + 1
END
--PRINT @sql
EXEC(@sql)