神了

来源：互联网发布：小鸡模拟器mac版编辑：程序博客网时间：2024/06/09 13:39

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

0 0