英文:
How do I select a specific date OR the latest date if the specific date doesn't exist?
问题
我有一个员工分配的表格，其中包含了START_DATE和STOP_DATE。员工一次只有一个分配任务，所以一个任务结束后，另一个任务会随之开始。目前仍在进行的任务的STOP_DATE表示为最小日期时间，'1753-01-01'。类似这样：
| 员工 | START_DATE | STOP_DATE |
|---|---|---|
| 51 | 2023-07-05 | 2023-07-06 |
| 51 | 2023-07-07 | 2023-07-10 |
| 51 | 2023-07-11 | 1753-01-01 |
| 32 | 2023-07-04 | 2023-07-06 |
| 32 | 2023-07-06 | 2023-07-07 |
| 32 | 2023-07-11 | 2023-07-12 |
我试图筛选出表格中显示所有最新的分配任务。换句话说，我试图获取所有当前正在进行的员工分配任务（STOP_DATE '1753-01-01'），或者如果他们没有任何当前正在进行的分配任务，那么获取STOP_DATE最晚的分配任务：
| 员工 | STOP_DATE |
|---|---|
| 51 | 1753-01-01 |
| 32 | 2023-07-12 |
我可以使用ROW_NUMBER/PARTITION BY来隔离最新的停止日期，但是如果员工在某处有一个STOP_DATE为1753-01-01，我如何忽略这个员工的最新停止日期正在困扰着我。
英文:
I have a table of employee assignment START_DATEs and STOP_DATEs. Employees only have one assignment at a time, so after one assignment stops another begins eventually. Assignments that are still in progress have a STOP_DATE represented as the minimum datetime, '1753-01-01'. Something like this:
| EMPLOYEE | START_DATE | STOP_DATE |
|---|---|---|
| 51 | 2023-07-05 | 2023-07-06 |
| 51 | 2023-07-07 | 2023-07-10 |
| 51 | 2023-07-11 | 1753-01-01 |
| 32 | 2023-07-04 | 2023-07-06 |
| 32 | 2023-07-06 | 2023-07-07 |
| 32 | 2023-07-11 | 2023-07-12 |
I'm trying to filter the table to show me all the latest assignments. In other words, I'm trying to get all employee assignments that are currently in progress (STOP_DATE '1753-01-01'), or, if they don't have any assignments that are currently in progress, to get the assignment with the latest STOP_DATE:
| EMPLOYEE | STOP_DATE |
|---|---|
| 51 | 1753-01-01 |
| 32 | 2023-07-12 |
I can use a ROW_NUMBER/PARTITION BY to isolate the latest stop date, but figuring out how to ignore an employee's latest stop date if they have a stop date of 1753-01-01 somewhere is giving me trouble.
答案1
得分: 3
我认为只需一个简单的 ROW_NUMBER() 就可以了。
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY START_DATE DESC) AS rn
FROM SomeTable
)t
WHERE rn = 1
英文:
I think just a simple ROW_NUMBER() should do it.
SELECT *
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY EMPLOYEE ORDER BY START_DATE DESC) AS rn
FROM SomeTable
)t
WHERE rn = 1
答案2
得分: 0
窗口函数来拯救！
SELECT 员工, 开始日期, 停止日期
FROM (
SELECT *, MAX(停止日期) OVER (PARTITION BY 员工) AS 最大停止日期, MIN(停止日期) OVER (PARTITION BY 员工) AS 最小停止日期
FROM @Table
) a
WHERE 停止日期 = '1753-01-01'
OR (停止日期 = 最大停止日期 AND 最小停止日期 <> '1753-01-01')
在这里，我们使用MIN和MAX窗口函数来查找每个员工的第一个和最后一个停止日期。由于我们无法在WHERE子句中引用它们，所以我们将它们用作子查询，并在外部进行过滤。
英文:
Windowed functions to the rescue!
SELECT Employee, StartDate, StopDate
FROM (
SELECT *, MAX(StopDate) OVER (PARTITION BY Employee) AS mxStopDate, MIN(StopDate) OVER (PARTITION BY Employee) AS mnStopDate
FROM @Table
) a
WHERE StopDate = '1753-01-01'
OR (StopDate = mxStopDate AND mnStopDate <> '1753-01-01')
Here we're using MIN and MAX windowed functions to find the first and last stop dates for each employee. Since we can't reference them in the where clause, we're using them as a subquery, and filtering on the outer one.
答案3
得分: 0
以下是翻译好的内容：
"在1753年显示一个日期来表示某事正在进行中有点没有意义 - 只需使用NULL会更好，因为它还没有停止日期。
根据您的描述，按照以下方式进行聚合会更有意义，只需指示它实际上正在进行中：
select Employee,
Max(Iif(Stop_date = '17530101', 'In Progress', Convert(Varchar(10), Stop_Date, 120)))
from Assignments
group by employee;
```"
<details>
<summary>英文:</summary>
It's a bit meaningless to show a date in 1753 as indicating something is in progress - just a NULL here would be preferable since it doesn't have a stop date yet.
Based on your description it would make more sense to just aggregate as follows and just indicate it's *actually* in progress:
select Employee,
Max(Iif(Stop_date = '17530101', 'In Progress', Convert(Varchar(10), Stop_Date, 120)))
from Assignments
group by employee;
</details>
# 答案4
**得分**: 0
这应该是安全的，而且速度足够快，假设你在`(Employee, StopDate)`上有一个索引：
``` lang-sql
declare @t table (
Employee int,
StartDate date,
StopDate date
);
-- 示例数据
insert into @t (Employee, StartDate, StopDate)
values
(51, '2023-07-05', '2023-07-06'),
(51, '2023-07-07', '2023-07-10'),
(51, '2023-07-11', '1753-01-01'),
(32, '2023-07-04', '2023-07-06'),
(32, '2023-07-06', '2023-07-07'),
(32, '2023-07-11', '2023-07-12');
-- 查询
select sq.Employee, sq.StopDate
from (
select t.Employee, t.StopDate,
row_number() over(
partition by t.Employee
order by case t.StopDate
when '17530101' then 1
else 2
end, t.StopDate desc
) as [RN]
from @t t
) sq
where sq.RN = 1;
英文:
This should be safe and sufficiently fast, assuming you have an index on (Employee, StopDate):
declare @t table (
Employee int,
StartDate date,
StopDate date
);
-- Sample data
insert into @t (Employee, StartDate, StopDate)
values
(51, '2023-07-05', '2023-07-06'),
(51, '2023-07-07', '2023-07-10'),
(51, '2023-07-11', '1753-01-01'),
(32, '2023-07-04', '2023-07-06'),
(32, '2023-07-06', '2023-07-07'),
(32, '2023-07-11', '2023-07-12');
-- The query
select sq.Employee, sq.StopDate
from (
select t.Employee, t.StopDate,
row_number() over(
partition by t.Employee
order by case t.StopDate
when '17530101' then 1
else 2
end, t.StopDate desc
) as [RN]
from @t t
) sq
where sq.RN = 1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论