神了

最新推荐文章于 2023-07-09 12:20:11 发布

tearsmo

最新推荐文章于 2023-07-09 12:20:11 发布

阅读量2.7k

点赞数

分类专栏： sql server 2005 学习记录

本文链接：https://blog.csdn.net/tearsmo/article/details/41042385

版权

sql server 2005 学习记录专栏收录该内容

57 篇文章 0 订阅

订阅专栏

select * into #08User_Duty_Schedule4 From User_Duty_Schedule14fly where RealStartTime is not null and RealEndTime is not NULL
and dayid between '20141001' and '20141031'

    -- 根據工號，對日期進行編號
    SELECT ROW_NUMBER() OVER (PARTITION BY USERID ORDER BY DAYID) AS NUM,DAYID,USERID INTO #CLASS2
    FROM #08User_Duty_Schedule4 WHERE CAST(DAYID AS DATETIME) between '20141001' and '20141031'

select * from #CLASS2
select USERID,DAYID-NUM,dayid,num from #CLASS2

--獲得每人每月連續出勤18天以上人數
SELECT USERID,MIN(DAYID) MINDAYID,MAX(DAYID) MAXDAYID,DATEDIFF(DAY,MIN(DAYID),MAX(DAYID))+1 RANGEDAYID into #aa2
FROM #CLASS2
GROUP BY USERID,DAYID-NUM HAVING DATEDIFF(DAY,MIN(DAYID),MAX(DAYID))+1 >=18

select a.UserID,b.UserCName,b.DeptID,c.DEPTCName,a.MINDAYID,a.MAXDAYID,a.RANGEDAYID From #aa2 a inner join User_infor b on a.userid=b.empid inner join Dept_Dept c on b.DeptID=c.deptid