考情数据调整

维护维护账号(db_datareader)

补考勤

识别号码 1000157

查询考勤

按时间段查询

select  t.sn 打卡机
  , t.*
from kaoqin..CHECKINOUT t
where USERID  = 2859
and  CHECKTIME between '2023-05-29' and '2023-05-30' 
order by checktime desc

按日期查询

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-23'
 order by checktime desc

批量查询

select USERID, convert(varchar(10),CHECKTIME,120) 日期,COUNT(*) 次数 ,
明细  = STUFF( (
    select ',  ' + convert(varchar(10), CHECKTIME,108) 
    from   kaoqin..CHECKINOUT tv 
    WHERE tv.USERID  = t.USERID  
    and convert(varchar(10),tv.CHECKTIME,120) =  convert(varchar(10),t.CHECKTIME,120) 
for xml path('')
)
                  ,1
                  ,1
                  ,'') 

from kaoqin..CHECKINOUT t
WHERE USERID  = 2859 
and CHECKTIME between '2023-06-01 ' and '2023-07-01' 
group by USERID,convert(varchar(10),CHECKTIME,120)
order by 日期 desc,次数  

STUFF 切割字符串

对应表格

插入对应sql

2023-05-23T12:25:23.000

insert into kaoqin..CHECKINOUT
(USERID, CHECKTIME, CHECKTYPE, VERIFYCODE, SENSORID, Memoinfo, WorkCode, sn, UserExtFmt, mask_flag, temperature)
values(2859, CAST(N'2023-05-23T12:25:23.000' AS DateTime),N'I', 1, 1, NULL, N'0', N'CQDT223160859', 1, 0, 0)

删除多余打卡

查看

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-05'
 order by checktime desc
 
 
 
------------- 具体时间
select * from kaoqin..CHECKINOUT t
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-23T12:25:23.000' AS DateTime)

清除数据

delete kaoqin..CHECKINOUT
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)

调整时间

 SELECT * 
 FROM  kaoqin..CHECKINOUT t
 WHERE USERID  = 2859 
 and convert(varchar(10),CHECKTIME,120)  = '2023-05-23'
 order by checktime desc

更新

update kaoqin..CHECKINOUT 
set  CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)
where  USERID  = 2859 
and CHECKTIME =  CAST(N'2023-05-29 12:15:23.000' AS DateTime)

导出

SELECT u.BadgeNumber, c.checktime, c.sensorid, c.VerifyCode,u.Name,c.checktype,c.workcode,c.UserExtFmt  
FROM CheckInOut as c, UserInfo as u
WHERE (c.userid=u.userid) 
AND c.CheckTime  >='2023-06-18'
And c.Checktime <= '2023-06-20'
and sn in ('CQDT223160859','CQDT231460219')

按人查询

SELECT m.MachineAlias ,c.sn,u.Name , c.checktime,  u.BadgeNumber
FROM CheckInOut as c
left join UserInfo as u on c.userid=u.userid 
left join Machines as m on c.sn=m.sn
WHERE  1=1 
AND c.CheckTime  >='2023-05-01'
And c.Checktime <= '2023-06-29'
and u.name in ('刘香花','黄思容','雷帮军') 	

查手机号码

SELECT  
       [NAME]
      ,[NAME_LETTER]
      ,[TELEPHONE]
      ,[STATUS]
 
       
  FROM [Ouqi].[dbo].[T_USER]
  where [TELEPHONE] like '15168%'

考勤重复人员

SELECT 
NAME,COUNT([BADGENUMBER]) CT ,MAX([BADGENUMBER]),MIN([BADGENUMBER])
FROM [kaoqin].[dbo].[USERINFO]
GROUP BY  NAME
ORDER BY CT   DESC

老oa

CALENDAR             11e8-b82d-bbf6c239-9ee2-053d1e776df4  日历id
ID                   11ed-a9b8-191b765e-a8b2-a36392a02a45   +++ 个人流程发送id
DEFAULTAPPLICATION   11e8-b82b-b7056a2c-b266-7907d3bcd381   +++ APPid
DEFAULTDEPARTMENT    11ed-d4e2-6ee7b680-a973-1182f27b94d8   +++ 流程部门id 

oa用户/部门

select * from  [dbo].[T_USER] u
left join [dbo].[T_DEPARTMENT] d
on u.DEFAULTDEPARTMENT = d.id

select u.LOGINNO 用户名 ,u.NAME,d.name as 部门 ,u.TELEPHONE ,u.EMAIL,u.STATUS ,d.LEVELS,d.INDEXCODE ,d.id  from  [dbo].[T_USER] u
left join [dbo].[T_DEPARTMENT] d
on u.DEFAULTDEPARTMENT = d.id
order by d.id

通知信息 主表

select * 
from  [dbo].[MC_NOTIFICATION] 
where SENDER_ID = '11ed-a9b8-191b765e-a8b2-a36392a02a45'
order by CREATE_TIME desc

通知关联-附加信息表

select   * 
from  [dbo].[MC_NOTICE] m 
left join  [dbo].[MC_NOTIFICATION]  ma on ma.MESSAGE_ID = m.id 
where  ma.SENDER_ID = '11ed-a9b8-191b765e-a8b2-a36392a02a45' 

基于通知 查询 流程

select fi.SUMMARY ,  * 
from  [dbo].[MC_NOTICE] m 

left join  [dbo].[MC_NOTIFICATION]  ma on ma.MESSAGE_ID = m.id

left join    [dbo].[T_FLOW_INTERVENTION]  fi     on fi.INITIATORID = ma.SENDER_ID

where  m.id = '11ee-0fd2-8e3e24d3-918c-a79d8c1df92b'

select  * 
from   [dbo].[MC_NOTIFICATION]  m
where  m.MESSAGE_ID = '11ee-0fd2-8e3e24d3-918c-a79d8c1df92b'
 

主流程表

select * 
from T_FLOW_INTERVENTION
where INITIATORID =  '11ed-a9b8-191b765e-a8b2-a36392a02a45'

流程内容表

SELECT * from T_RELATIONHIS 
WHERE flowname LIKE '请假%'

补考勤表

[dbo].[TLK_ATTENDANCEDETAIL] . PARENT 同 [dbo].[T_FLOW_INTERVENTION] id

SELECT * FROM  [dbo].[TLK_ATTENDANCEDETAIL]
WHERE PARENT   = '11ee-0fd2-3e9784a6-918c-a79d8c1df92b' -- 关联流程

SELECT * FROM  [dbo].[TLK_ATTENDANCEDETAIL]
where AUTHOR = '11ed-a9b8-191b765e-a8b2-a36392a02a45'  -- 关联用户

select * 
from  [dbo].[T_ACTORHIS] 
where NODEHIS_ID = '11ed-a9b8-191b765e-a8b2-a36392a02a45'

公出表

select *
from TLK_BUSINESS_PUBLIC_TRIP
where AUTHOR = '11ed-a9b8-191b765e-a8b2-a36392a02a45' --关联用户  id可关联流程

请假发起详情

select * 
from TLK_APPLY_LEAVE
where AUTHOR  AUTHOR = '11ed-a9b8-191b765e-a8b2-a36392a02a45' --关联用户  id可关联流程

分组

select t.author,t.item_leave_r from
(select test.*,row_number() over (partition by author order by author desc) rn 
from TLK_APPLY_LEAVE test) t
where rn=1;