显示员工A/c到EndDate的最新记录 [英] Show Last record of Employee a/c to EndDate

问题描述

i have some  tables

name employeeshifts
Shifts 1= A,2=B,3=C

columns are
sno     Shifts      EmployeeID      Startdate                   EndDate                     ChangedDate
1       1               1           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         2012-12-13 00:00:00.000

2       2               1           2012-12-13 00:00:00.000 2012-12-18 00:00:00.000         null

3       3               1           2012-12-19 00:00:00.000 2012-12-21 00:00:00.000         null

4       1               2           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

5       2               4           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

6       3               5           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

7       1               9           2012-12-16 00:00:00.000 2012-12-18 00:00:00.000         2012-12-16 00:00:00.000

8       2               9           2012-07-07 00:00:00.000 2012-08-17 00:00:00.000         null

there is employee table as well

what i need to show
last record of employee where employee has performed or is performing duty

suppose
employee 1 has performed his last duty a/c to above table
2012-12-21 00:00:00.000 in C shifts
Employee 9 will perform  will be performing duty
2012-08-17 00:00:00.000 in B shifts

Just like this i need to show last record where employee performs duty

thanks

推荐答案

在

order by EndDate  desc

并根据需要添加 group by employeeid

问候
Sebatian

and add group by employeeid if needed

Regards
Sebatian

您需要查询雇员id结束日期或根据班次进行分组，

如果您需要雇员编号结束日期

选择*从employeeshifts组按employeeid顺序按endate desc

如果与员工轮班

从employeeshifts按班次选择*，按endate desc按雇员顺序选择

u need the query employeeid enddate or grouping based on shifts,

If u need employeeid enddate

select * from employeeshifts group by employeeid order by endate desc

if it is on shifts with employee

select * from employeeshifts group by shifts,employeeid order by endate desc

如果要获取该雇员已履行或正在履行职责的所有雇员的最新记录，可以使用以下查询.

If you want to get the latest record of all employees where employee has performed or is performing duty you can use the below query.

SELECT sno
, EmployeeID
, CASE WHEN Shifts = 1 THEN 'A'
       WHEN Shifts = 2 THEN 'B'
       WHEN Shifts = 3 THEN 'C' END AS Shifts
, StartDate
, EndDate
, ChangedDate
 FROM employeeshifts
WHERE sno IN
(
    SELECT MAX(sno) AS sno FROM employeeshifts
    GROUP BY EmployeeID
)

这篇关于显示员工A/c到EndDate的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！