需要SQL SELECT查询员工的月出勤报告
我有3个表 1.日历日期 2.员工详细 3.冲床计时 以下表中我需要生成报表来回回月出勤需要SQL SELECT查询员工的月出勤报告
date
2017-06-01
2017-06-02
2017-06-03
2017-06-04
2017-06-05
2017-06-06
2017-06-07
2017-06-08
2017-06-09
2017-06-10
2017-06-11
2017-06-12
2017-06-13
2017-06-14
2017-06-15
pk EmployeeName
3 IMMAM
4 SRIMAN
5 AJAY
6 Vinay
7 RAGHU
3.冲床计时
EmployeePk PunchTime
4 2017-06-10 17:49:34.000
4 2017-06-10 18:51:35.000
7 2017-06-10 19:18:37.000
6 2017-06-10 19:18:52.000
5 2017-06-10 19:19:05.000
6 2017-06-12 10:59:34.000
5 2017-06-12 10:59:44.000
5 2017-06-12 16:04:24.000
6 2017-06-12 16:06:48.000
5 2017-06-12 16:08:58.000
5 2017-06-12 16:14:33.000
5 2017-06-13 10:44:06.000
6 2017-06-13 10:44:23.000
4 2017-06-13 10:44:36.000
7 2017-06-13 10:51:22.000
5 2017-06-13 17:45:59.000
6 2017-06-13 17:46:14.000
7 2017-06-13 17:46:26.000
4 2017-06-13 17:47:21.000
5 2017-06-14 10:48:39.000
6 2017-06-14 10:49:04.000
7 2017-06-14 10:49:16.000
4 2017-06-14 10:49:23.000
6 2017-06-14 17:22:34.000
7 2017-06-14 18:23:08.000
4 2017-06-14 18:23:25.000
5 2017-06-14 18:23:32.000
5 2017-06-15 10:44:48.000
4 2017-06-15 10:45:13.000
6 2017-06-15 10:45:32.000
7 2017-06-15 11:03:55.000
5 2017-06-15 11:26:53.000
7 2017-06-15 11:26:56.000
5 2017-06-15 11:29:16.000
5 2017-06-15 11:29:20.000
我需要的报告类似
Calender Date Employee Punchtime IsPresent
10-06-2017 Imama Null No
10-06-2017 SRIMAN 2017-06-10 17:49:34.000 Yes
10-06-2017 AJAY 2017-06-10 19:19:05.000 Yes
10-06-2017 Vinay 2017-06-10 19:18:52.000 Yes
10-06-2017 RAGHU 2017-06-10 19:18:37.000 Yes
11-06-2017 Imama Null NO
11-06-2017 SRIMAN Null NO
11-06-2017 AJAY Null NO
11-06-2017 Vinay Null NO
11-06-2017 RAGHU Null NO
12-06-2017 Imama Null No
12-06-2017 SRIMAN Null No
12-06-2017 AJAY 2017-06-12 10:59:44.000 Yes
12-06-2017 Vinay 2017-06-12 10:59:34.000 Yes
12-06-2017 RAGHU 2017-06-12 10:59:44.000 Yes
而且洙
我开始与像这样
declare
@month int, @year int, @lastDay int,
@starttime datetime,
@endtime datetime,
@companyPk int,
@employeePk int
set @starttime ='2017-06-01'
set @endtime = '2017-06-30'
set @companyPk =2
set @employeePk =0
select x.calenderdate,y.Pk,y.EmployeeName,y.Ispresent
from
(
(select convert(date, CalendarDate) as calenderdate from ECMSCalendar
where convert(date, CalendarDate) >= @starttime and convert(date, CalendarDate) <[email protected]
) as x
left join
(
select a.Pk,a.EmployeeName,(case when b.IsPresent is null then 'Absent' else b.IsPresent end) as Ispresent from
(
(select emp.Pk,emp.EmployeeName from EmployeeDetails emp where emp.Companypk = @companyPk) as a
left join
(SELECT ed.pk as Pk,ed.EmployeeName as StaffName ,
(case when (DATEDIFF(MINUTE,min(bio.PunchTime),(case when max(bio.PunchTime) = min(bio.PunchTime) then min(bio.PunchTime) else max(bio.PunchTime) end))) < sd.shiftname then 'Absent' else 'Present' end) as IsPresent
--, CONVERT(nvarchar(2), bio.PunchTime) as date
--,count(CONVERT(nvarchar(2), bio.punchtime, 103))as day
from BioMetricPunchDetails bio
left join EmployeeDetails ed on ed.Pk = bio.EmployeePk
left join EmployeeShiftDetails esd on esd.EmployeePk = ed.pk
left join ShiftDetails sd on sd.pk =esd.shiftpk
where --convert (date,bio.punchtime) >= @starttime and convert (date,bio.punchtime) <= @endtime
convert (date,(select min(bpd.PunchTime) from BioMetricPunchDetails bpd where ([email protected] or @employeePk=0))) >= @starttime and convert (date,(select max(bpd.PunchTime) from BioMetricPunchDetails bpd where ([email protected] or @employeePk=0))) <= @endtime
and
ed.Companypk= @companyPk and ([email protected] or @employeePk=0)
--and [email protected]
group by ed.pk, ed.EmployeeName,sd.shiftname) as b
on a.Pk = b.Pk)
) as y
on x.calenderdate = y.Ispresent
)
order by x.calenderdate
请解决该任务谢谢
尝试这样的事情
with
alldates as (
SELECT convert(date,thedate) alldate
FROM dbo.ExplodeDates('2017-06-10' , '2017-06-10') as d
),
empshift as (
select ed.Pk,ed.EmployeeName,sd.ShiftName,sd.Intime,sd.OutTime from EmployeeDetails ed left outer join
EmployeeShiftDetails esd on (ed.Pk = esd.EmployeePk)
left outer join ShiftDetails sd on (sd.Pk = esd.ShiftPk)
)
,biometric1 as (
select min(punchtime) intime,max(punchtime) outtime,convert(date,punchtime) dates
, empshift.EmployeeName,empshift.ShiftName,Intime ShiftInTime,OutTime ShiftOutTime
from BioMetricPunchDetails bpd inner join empshift on (empshift.Pk =bpd.EmployeePk)
where CONVERT(date,punchtime) between '2017-06-10' and '2017-06-0'
group by convert(date,punchtime), empshift.EmployeeName,empshift.ShiftName,Intime,OutTime
),
fnl as (
select convert(date,intime) dates,intime,
case when (intime = outtime) then NULL else outtime end as outtime
,ShiftName,DATEDIFF(MINUTE,intime,outtime) duration,ShiftInTime,convert(time,intime) InTimeHrMin
,datediff(MINUTE,ShiftInTime, convert(time,intime)) lateby
from biometric1
),
fnl1 as (select * from alldates left outer join fnl
on (alldates.alldate = fnl.dates))
select ed.EmployeeName,fnl1.* from EmployeeDetails ed cross join fnl1
谢谢,我会尽力回复它很全用 –
报告都不错。他们进入整洁的小电子邮件或PowerPoint 演示文稿,没有人真正关注,但他们肯定会让我们觉得我们很有成效。
1)您是否试图跟踪每月 会议中缺少哪些缺省值?
- 你的会议出了什么问题?他们有没有收获?他们是否真的提供了外出或IM(如Skype,微软团队,Slack)不能提供的团队之间的凝聚力?
2)您是否试图查看谁正忙着来参加会议?
- 打孔至少表明他们在那里......但他们参与?增值?
- 当然,还有其他指标可以用来发现员工的职业道德。
让您的报告回答的问题,一个简单的查询不能。避免报道会填满空间的诱惑。
但是,您的业务记录会议的轨道,一定要以表格形式获取日期。
CREATE TABLE #Calendar_Date (DATES Date)
INSERT INTO #Calendar_Date
VALUES ('2017-06-01'),('2017-06-02'),('2017-06-03'),('2017-06-04'),('2017-06-05'),('2017-06-06'),('2017-06-07'),('2017-06-08'),('2017-06-09'),('2017-06-10'),('2017-06-11'),('2017-06-12'),('2017-06-13'),('2017-06-14'),('2017-06-15')
CREATE TABLE #Employee_DIM (ID INT UNIQUE
, Employee_Name NVARCHAR(100))
INSERT INTO #Employee_DIM (ID, Employee_Name)
VALUES (3, 'IMMAM'), (4, 'SRIMAN'), (5, 'AJAY'), (6, 'Vinay'), (7, 'RAGHU')
CREATE TABLE #Punch_Timings (Employee_PK INT
, Punch_Time DATETIME)
INSERT INTO #Punch_Timings (Employee_PK, Punch_Time)
VALUES (4,'2017-06-10 17:49:34.000'),(4,'2017-06-10 18:51:35.000'),(7,'2017-06-10 19:18:37.000')
,(6,'2017-06-10 19:18:52.000'),(5,'2017-06-10 19:19:05.000'),(6,'2017-06-12 10:59:34.000')
,(5,'2017-06-12 10:59:44.000'),(5,'2017-06-12 16:04:24.000'),(6,'2017-06-12 16:06:48.000')
,(5,'2017-06-12 16:08:58.000'),(5,'2017-06-12 16:14:33.000'),(5,'2017-06-13 10:44:06.000')
,(6,'2017-06-13 10:44:23.000'),(4,'2017-06-13 10:44:36.000'),(7,'2017-06-13 10:51:22.000')
,(5,'2017-06-13 17:45:59.000'),(6,'2017-06-13 17:46:14.000'),(7,'2017-06-13 17:46:26.000')
,(4,'2017-06-13 17:47:21.000'),(5,'2017-06-14 10:48:39.000'),(6,'2017-06-14 10:49:04.000')
,(7,'2017-06-14 10:49:16.000'),(4,'2017-06-14 10:49:23.000'),(6,'2017-06-14 17:22:34.000')
,(7,'2017-06-14 18:23:08.000'),(4,'2017-06-14 18:23:25.000'),(5,'2017-06-14 18:23:32.000')
,(4,'2017-06-15 10:45:13.000'),(6,'2017-06-15 10:45:32.000'),(7,'2017-06-15 11:03:55.000')
,(5,'2017-06-15 11:26:53.000'),(7,'2017-06-15 11:26:56.000'),(5,'2017-06-15 11:29:16.000')
,(5,'2017-06-15 11:29:20.000'),(4, '2017-06-10 19:31:35.000'),(5, '2017-06-10 19:45:05.000')
,(6, '2017-06-10 19:38:52.000'),(7, '2017-06-10 19:13:37.000')
CREATE TABLE #MEETING_TIMES (Meeting_ID INT
, Meeting_Start DATETIME
, Meeting_End DATETIME)
INSERT INTO #MEETING_TIMES (Meeting_ID, Meeting_Start, Meeting_End)
VALUES (304,'2017-06-10 19:00:00.000', '2017-06-10 19:30:00.000')
, (311, '2017-06-14 18:00:00.000', '2017-06-14 18:30:00.000')
一旦你有你的尺寸,并以有序的方式事实,逻辑应该是这样非常简单以下几点:
;WITH Employee_Time AS (
SELECT MIN(Punch_Time) AS ASSUME_Punch_IN
, MAX(PUNCH_Time) AS ASSUME_Punch_OUT
, DATEPART(DAY, Punch_Time) AS DAY
, Employee_PK
FROM #Punch_Timings PT
GROUP BY Employee_PK, DATEPART(DAY, Punch_Time)
)
SELECT B.Meeting_ID
, A.Meeting_Start
, Employee_ID
, CASE WHEN A.Meeting_Start IS NULL THEN 'NO' ELSE 'YES' END AS FLAG_ATTEND
FROM (SELECT ET.Employee_PK, ET.ASSUME_Punch_IN
, ET.ASSUME_Punch_OUT, ET.DAY, MT.Meeting_Start, MT.Meeting_End
FROM Employee_Time ET
INNER JOIN #MEETING_TIMES MT ON (( ET.ASSUME_Punch_IN >= MT.Meeting_Start
AND ET.ASSUME_Punch_IN < MT.Meeting_End)
OR ( ET.ASSUME_Punch_IN <= Meeting_Start
AND ET.ASSUME_Punch_OUT > Meeting_Start)
)
AND ET.DAY = DATEPART(DAY, Meeting_Start)
) A
RiGHT OUTER JOIN (
SELECT ID AS Employee_ID, Meeting_Start, Meeting_ID
FROM #MEETING_TIMES B
FULL JOIN #Employee_DIM ED ON 1=1
) B ON B.Employee_ID = A.Employee_PK
AND DATEPART(DAY, B.Meeting_Start) = A.DAY
这就是说,我会从这个这样找到其他信息作为紧迫性和你的同事的项目性质。在数据中找不到问题并让您的报告回答这个问题。
顺便说一下,第一个问题的答案是3. :) –
用于匹配会议与Punch_Times的逻辑是'IF PUNCH_IN> =会议开始 \t AND IF PUNCH_IN 会议开始 –
太棒了!你应该从'SELECT'开始。 –
看看'LEFT JOIN'的功能。 – ZLK
似乎太复杂了...格式化您的查询也会有很大帮助。 –