34,590
社区成员
发帖
与我相关
我的任务
分享
SQL 问题:
有张请假表A(工号,日期),假日表B(日期,节日名称)
请假N天,就有N条数据但不包含表B节假日的日期,礼拜天(可能有,也可能没有)
怎么计算表A中连续请假5天以上的员工,还包含跨月的情况。
//例如: 周四,周五,周六,周日,下周一 属于连续5天的数据
//例如: 周三,周四,周五,周六,下周一 这种也属于连续5天的数据
--下面例出表A所有情况:
--例1 (当月连续请假6天以上):
卡号 日期
213380 2011-05-05 00:00:00
213380 2011-05-06 00:00:00
213380 2011-05-07 00:00:00
213380 2011-05-08 00:00:00 --有时可能也会有星期天的日期
213380 2011-05-09 00:00:00
213380 2011-05-10 00:00:00
--例2 (当月连续请假5天,2011-04-10 属于星期天):
卡号 日期
201105 2011-04-08 00:00:00
201105 2011-04-09 00:00:00
201105 2011-04-11 00:00:00
201105 2011-04-12 00:00:00
201105 2011-04-13 00:00:00
--例3 (当月连续请假4天,):
卡号 日期
201166 2011-04-08 00:00:00
201166 2011-04-09 00:00:00
201166 2011-04-11 00:00:00
201166 2011-04-12 00:00:00
--例4 (跨月连续请假4天,假日表B中,5月1,5月2,5月3属于假日):
卡号 日期
201177 2011-04-29 00:00:00
201177 2011-04-30 00:00:00
201177 2011-05-04 00:00:00
201177 2011-04-05 00:00:00
--例5 (跨月连续请假5天,假日表B中,5月1,5月2,5月3属于假日):
卡号 日期
201177 2011-04-29 00:00:00
201177 2011-04-30 00:00:00
201177 2011-05-04 00:00:00
201177 2011-05-05 00:00:00
201177 2011-05-06 00:00:00
--表B 数据
日期 节日名称
2011-04-05 00:00:00 清明节
2011-05-01 00:00:00 劳动节
2011-05-02 00:00:00 劳动节
2011-05-03 00:00:00 劳动节
--忘记补充了,是数据库是SQL2000
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([卡号] int,[日期] Datetime)
Insert #A
select 213380,'2011-05-05' union all
select 213380,'2011-05-06' union all
select 213380,'2011-05-07' union all
select 213380,'2011-05-08' union all
select 213380,'2011-05-09' union all
select 213380,'2011-05-10'
Go
--> -->
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([日期] Datetime,[节日名称] nvarchar(3))
Insert #B
select '2011-04-05',N'清明节' union all
select '2011-05-01',N'劳动节' union all
select '2011-05-02',N'劳动节' union all
select '2011-05-03',N'劳动节'
Go
SELECT TOP 365 ID=IDENTITY(INT,0,1) INTO # FROM syscolumns AS a,syscolumns AS b ,syscolumns AS c
DECLARE @Date DATETIME
SET @Date='2011-01-01' --開始日期
SELECT
TOP 365 @Date+ID AS Date,Flag=CAST(null AS TINYINT)
INTO #C
FROM #
WHERE @Date+ID<=GETDATE()
--SELECT @@DATEFIRST 為7時
SELECT
ID=IDENTITY(INT,1,1),c2.卡号,c2.Date, Flag=CASE WHEN a.日期 IS NOT null THEN 1 END
INTO #C2
FROM (SELECT Date,卡号,Flag FROM #C,(SELECT DISTINCT 卡号 FROM #A) AS d) AS c2
LEFT JOIN #B AS b ON c2.Date=b.日期
LEFT JOIN #A AS a ON a.卡号=c2.卡号 AND c2.Date=a.日期
WHERE NOT(a.日期 IS NULL AND (DATEPART(dw,Date)=1 OR b.日期 IS NOT NULL))
ORDER BY c2.Date
SELECT *
FROM #C2 AS b
WHERE EXISTS(
SELECT *
FROM #C2 AS a
WHERE a.卡号=b.卡号 AND ABS(a.ID-b.ID)<=2 AND
EXISTS(SELECT 1 FROM #C2 WHERE 卡号=a.卡号 AND ABS(a.ID-ID)<=2 HAVING SUM(Flag)=5)
)
DROP TABLE #,#C,#C2