目前有一个记录登录系统log表,其中包含 UserId, LogTime,两个重要字段
公司现在要通过系统log表来统计该员工没有登录系统的天数(周六周日除外)
非常感谢!
------解决思路----------------------
--创建计算两个日期之间休息天数
CREATE FUNCTION dbo.GetVac(@Start datetime,@End datetime)
RETURNS integer AS
BEGIN
declare
@intNum integer,
@intAllDay integer,
@i integer,
@intWeekDay integer,
@tempDateTime datetime,
@intVac integer
select @intAllDay=DateDiff(dd,@Start,@End)
select @i=0
select @intVac=0
while(@i<@intAllDay)
begin
select @tempDateTime=DateAdd(dd,@i,@Start)
select @intWeekDay=datepart(dw,@tempDateTime)-1
if(@intWeekDay=6) or (@intWeekDay=0)
begin
select @intVac=@intVac+1
end
select @i=@i+1
end
return @intVac
END
------
---->测试数据
IF OBJECT_ID('TEMPDB.DBO.#TEMP')IS NOT NULL
DROP TABLE #TEMP
------------------------------------------------------------------------------------------------------------
CREATE TABLE #TEMP
(userid varchar(10),
logtime date)
insert into #TEMP
select 'AA','2014-12-1'union all
select 'BB','2014-12-1'union all
select 'cc','2014-12-1'union all
select 'AA','2014-12-11'union all
select 'AA','2014-12-11'union all
select 'bb','2014-12-9'
go
select userid,
最后登入日期,
datediff (DAY,最后登入日期,getdate())-dbo.GetVac(最后登入日期,getdate())as 未登入天数
from(
select userid,MAX(logtime)as 最后登入日期 from #TEMP
group by userid)a
--------------------------------------------------
userid 最后登入日期 未登入天数
---------- ---------- -----------
AA 2014-12-11 0
bb 2014-12-09 2
cc 2014-12-01 8
(3 行受影响)
------解决思路----------------------
-- 建个日历表,经常要用到的
DECLARE @old_DATEFIRST int
SET @old_DATEFIRST = @@DATEFIRST
SET DATEFIRST 1
SELECT dt,
DatePart(weekday,dt) weekday,
CASE WHEN DatePart(weekday,dt) <=5 THEN 1 ELSE 0 END workday
INTO Calendar
FROM (
SELECT DateAdd(day,number,'2014-01-01') dt
FROM master..spt_values
WHERE type = 'p'
AND number < 365
) t
SET DATEFIRST @old_DATEFIRST
-- 查询
;WITH daylog AS (
SELECT DISTINCT
UserId,
Convert(datetime,Convert(varchar(10),LogTime,120),120) logday
FROM log
/* WHERE 日期范围 */
)
,unworklog AS (
SELECT c.dt,
u.Id UserId
FROM Calendar c,
[User] u -- 用户表
WHERE c.workday = 1
/* AND 日期范围 */
AND NOT EXISTS (SELECT *
FROM daylog
WHERE UserId = u.id
AND logday = c.dt)
)
SELECT UserId,
Count(*) unworkdays
FROM unworklog
GROUP BY UserId