I have a query that grabs the first person from a department who punched in first, and the person from a department that punched in last. Essentially this shows me who opened a location, and closed a location. Below is my query I am using the "OVER" function, however the over function does not work for a dataset in VB.net Are there any other options to replace the over function with?
我有一个查询,它从第一个打卡的部门获取第一个打卡的人,从最后一个打卡的部门获取第一个打卡的人。从本质上讲,这显示了我打开了一个位置,关闭了一个位置。下面是我使用“OVER”函数的查询,但是,在VB.net中,对于一个数据集来说,OVER函数并不起作用,是否还有其他的选项可以替换这个OVER函数?
SELECT * FROM (
SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
RANK() OVER ( ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER (ORDER BY dtTimeOut DESC) rk2 --latest record gets 1
FROM TimeClock INNER JOIN
Employees ON TimeClock.lEmployeeID = Employees.lEmployeeID
WHERE (dtTimeIn > dateadd(day, datediff(day, 0, getdate())-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, getdate()), 0)) AND
(sDept IN ('1', '2', '3'))
) A
WHERE rk2=1
3 个解决方案
#1
2
You can try with this:
你可以试试这个:
SELECT tc.dtTimeIn
, tc.dtTimeOut
, e.sfirstname
FROM TimeClock tc
JOIN Employees e ON tc.lEmployeeID = e.lEmployeeID
JOIN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0) _date
, MIN(tc.dtTimeIn) dtTimeIn
, MAX(tc.dtTimeOut) dtTimeOut
FROM TimeClock tc
WHERE e.sDept IN ('1', '2', '3')
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0)
) t ON t._date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0) = t._date
AND (t.dtTimeIn = tc.dtTimeIn OR t.dtTimeOut = tc.dtTimeOut)
WHERE e.sDept IN ('1', '2', '3')
#2
2
You can replace it with a correlated subquery. Here is an example for rk1
:
您可以用相关子查询替换它。这里有一个rk1的例子:
(select count(distinct lEmployeeId)
from TimeClock tc
where tc.lEmployeeId = timeclock.lemployeeId and
tc.dtTimeIn <= timeclock.dtTimeIn
) as rk1
Rank can produce multiple records all labelled with 1 (when there are ties). If you really mean row_number()
, then you would use count(*)
in the above query instead of count(distinct)
.
Rank可以生成多个标有1的记录(当有连接时)。如果您真正的意思是row_number(),那么您将在上面的查询中使用count(*),而不是计数(不同)。
#3
1
your query just return who closed a location...
您的查询只返回谁关闭了一个位置……
to return who open and who closed you need to change your where clause to
要返回打开的和关闭的,您需要将where子句改为
where rk2 = 1 or rk1 = 1
and if you will get from many sDept at same time the order should be partitioned for sDept like this
如果您同时从多个sDept中获得,那么就应该像这样对sDept进行排序
RANK() OVER ( partition by sDept ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER ( partition by sDept ORDER BY dtTimeOut DESC) rk2 --latest record get
see...
看到……
setting an ambient to run your query
设置环境以运行查询
declare @TimeClock table ( lEmployeeID int, dtTimeIn datetime, dtTimeOut datetime)
declare @Employees table ( lEmployeeID int, sfirstname varchar(max),sDept varchar(max))
declare @getDate date
set @getDate ='02/12/2013'
insert @Employees
values (1,'Ana','1')
,(2,'Pedro','1')
,(3,'Alfred','2')
insert @TimeClock
values (1 ,'02/12/2013 08:30','02/11/2013 11:30')
,(2 ,'02/12/2013 08:00','02/11/2013 11:00')
,(3 ,'02/12/2013 08:15','02/11/2013 11:15')
your query...
您的查询…
SELECT * FROM (
SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
RANK() OVER (partition by sDept ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER (partition by sDept ORDER BY dtTimeOut DESC) rk2 --latest record gets 1
FROM @TimeClock timeclock
INNER JOIN @Employees Employees
ON TimeClock.lEmployeeID = Employees.lEmployeeID
WHERE (dtTimeIn > dateadd(day, datediff(day, 0, @getDate)-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, @getDate), 0)) AND
(sDept IN ('1', '2', '3'))
) A
WHERE rk2=1
returns...
返回……
dtTimeIn dtTimeOut sfirstname rk1 rk2
2013-02-12 08:30:00.000 2013-02-11 11:30:00.000 Ana 3 1
see.. just who closed is in resultset
看到. .合上的就是resultset
changing the where clause...
where子句改变……
SELECT * FROM (
SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
RANK() OVER ( ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER (ORDER BY dtTimeOut DESC) rk2 --latest record gets 1
FROM @TimeClock timeclock
INNER JOIN @Employees Employees
ON TimeClock.lEmployeeID = Employees.lEmployeeID
WHERE (dtTimeIn > dateadd(day, datediff(day, 0, @getDate)-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, @getDate), 0)) AND
(sDept IN ('1', '2', '3'))
) A
WHERE rk2=1
or rk1 = 1
return both.. who open and who closed...
返回两个. .谁打开谁关闭……
dtTimeIn dtTimeOut sfirstname rk1 rk2
2013-02-12 08:30:00.000 2013-02-11 11:30:00.000 Ana 3 1
2013-02-12 08:00:00.000 2013-02-11 11:00:00.000 Pedro 1 3
2013-02-12 08:15:00.000 2013-02-11 11:15:00.000 Alfred 1 1
Pedro did opened and Ana closed the sDept '1' and Alfred did opened and closed sDept '2'
佩德罗打开了sDept '1, Ana关闭了sDept '1阿尔弗雷德打开了sDept '2关闭了
#1
2
You can try with this:
你可以试试这个:
SELECT tc.dtTimeIn
, tc.dtTimeOut
, e.sfirstname
FROM TimeClock tc
JOIN Employees e ON tc.lEmployeeID = e.lEmployeeID
JOIN (
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0) _date
, MIN(tc.dtTimeIn) dtTimeIn
, MAX(tc.dtTimeOut) dtTimeOut
FROM TimeClock tc
WHERE e.sDept IN ('1', '2', '3')
GROUP BY
DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0)
) t ON t._date = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND DATEADD(DAY, DATEDIFF(DAY, 0, tc.dtTimeIn), 0) = t._date
AND (t.dtTimeIn = tc.dtTimeIn OR t.dtTimeOut = tc.dtTimeOut)
WHERE e.sDept IN ('1', '2', '3')
#2
2
You can replace it with a correlated subquery. Here is an example for rk1
:
您可以用相关子查询替换它。这里有一个rk1的例子:
(select count(distinct lEmployeeId)
from TimeClock tc
where tc.lEmployeeId = timeclock.lemployeeId and
tc.dtTimeIn <= timeclock.dtTimeIn
) as rk1
Rank can produce multiple records all labelled with 1 (when there are ties). If you really mean row_number()
, then you would use count(*)
in the above query instead of count(distinct)
.
Rank可以生成多个标有1的记录(当有连接时)。如果您真正的意思是row_number(),那么您将在上面的查询中使用count(*),而不是计数(不同)。
#3
1
your query just return who closed a location...
您的查询只返回谁关闭了一个位置……
to return who open and who closed you need to change your where clause to
要返回打开的和关闭的,您需要将where子句改为
where rk2 = 1 or rk1 = 1
and if you will get from many sDept at same time the order should be partitioned for sDept like this
如果您同时从多个sDept中获得,那么就应该像这样对sDept进行排序
RANK() OVER ( partition by sDept ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER ( partition by sDept ORDER BY dtTimeOut DESC) rk2 --latest record get
see...
看到……
setting an ambient to run your query
设置环境以运行查询
declare @TimeClock table ( lEmployeeID int, dtTimeIn datetime, dtTimeOut datetime)
declare @Employees table ( lEmployeeID int, sfirstname varchar(max),sDept varchar(max))
declare @getDate date
set @getDate ='02/12/2013'
insert @Employees
values (1,'Ana','1')
,(2,'Pedro','1')
,(3,'Alfred','2')
insert @TimeClock
values (1 ,'02/12/2013 08:30','02/11/2013 11:30')
,(2 ,'02/12/2013 08:00','02/11/2013 11:00')
,(3 ,'02/12/2013 08:15','02/11/2013 11:15')
your query...
您的查询…
SELECT * FROM (
SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
RANK() OVER (partition by sDept ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER (partition by sDept ORDER BY dtTimeOut DESC) rk2 --latest record gets 1
FROM @TimeClock timeclock
INNER JOIN @Employees Employees
ON TimeClock.lEmployeeID = Employees.lEmployeeID
WHERE (dtTimeIn > dateadd(day, datediff(day, 0, @getDate)-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, @getDate), 0)) AND
(sDept IN ('1', '2', '3'))
) A
WHERE rk2=1
returns...
返回……
dtTimeIn dtTimeOut sfirstname rk1 rk2
2013-02-12 08:30:00.000 2013-02-11 11:30:00.000 Ana 3 1
see.. just who closed is in resultset
看到. .合上的就是resultset
changing the where clause...
where子句改变……
SELECT * FROM (
SELECT timeclock.dtTimeIn, timeclock.dtTimeOut, employees.sfirstname,
RANK() OVER ( ORDER BY dtTimeIn) rk1, --earliest record gets 1
RANK() OVER (ORDER BY dtTimeOut DESC) rk2 --latest record gets 1
FROM @TimeClock timeclock
INNER JOIN @Employees Employees
ON TimeClock.lEmployeeID = Employees.lEmployeeID
WHERE (dtTimeIn > dateadd(day, datediff(day, 0, @getDate)-1, 0)) AND (dtTimeOut < dateadd(day, datediff(day, 0, @getDate), 0)) AND
(sDept IN ('1', '2', '3'))
) A
WHERE rk2=1
or rk1 = 1
return both.. who open and who closed...
返回两个. .谁打开谁关闭……
dtTimeIn dtTimeOut sfirstname rk1 rk2
2013-02-12 08:30:00.000 2013-02-11 11:30:00.000 Ana 3 1
2013-02-12 08:00:00.000 2013-02-11 11:00:00.000 Pedro 1 3
2013-02-12 08:15:00.000 2013-02-11 11:15:00.000 Alfred 1 1
Pedro did opened and Ana closed the sDept '1' and Alfred did opened and closed sDept '2'
佩德罗打开了sDept '1, Ana关闭了sDept '1阿尔弗雷德打开了sDept '2关闭了