选择当前日期，如果它不存在则寻找未来的其他人从过去获取最高值

I have some data with multipe dates across some rows in a table. what i want to do is get a date thats currently active if there is no active date then i want to take a future one, if that one also doesnt exist then il take one from the past.

我有一些数据，表格中的某些行有多个日期。我想要做的是获取一个当前活动的日期，如果没有活动日期然后我想要采取未来的一个，如果那个也不存在那么我从过去拿一个。

table: date_from datetime, date_to datetime, userid varchar
2016-01-01          2016-03-25         Bob
2016-03-26          2016-05-01         Bob
2016-05-02          2016-04-25         Bob

2016-01-01          2016-03-25         Larry
2016-05-02          2016-04-25         Larry

2016-01-01          2016-03-25         Todd

For Bob i want to get the date_from value 2016-03-26

对于Bob我想获得date_from值2016-03-26

While for Larry i want to get 2016-05-02

而对于拉里我想得到2016-05-02

And Todd 2016-01-01

和托德2016-01-01

Here is my sql so far (this also gets the most recent date_from where the date_to from the last row datediff is greater than a variable)

这是我的sql到目前为止（这也是最近的date_from，其中date_to来自最后一行的datediff大于一个变量）

    insert into table1 (date_from, resource_id) 
select  date_from, resource_id 
    from 
    (select t.*, row_number() over 
    (partition by resource_id order by 
    (case when datediff(day, prev_date_to, date_from) > $days 
    then 1 else 2 end), date_from ASC ) as seqnum 
        from 
            (select t.*, lag(date_to) over 
            (partition by resource_id order by date_from) as prev_date_to 
            from table2 t  where user = '$user' and date_from <= getdate()
        ) t
    ) t where seqnum = 1

I know how to check for the current one and if it doesnt exist get a past one or i can make it get a future one, but i dont understand how to make it check for future dates then go backwards if there are none

我知道如何检查当前的一个，如果它不存在得到过去的一个或我可以让它得到一个未来的，但我不明白如何使它检查未来的日期然后如果没有没有

1 个解决方案

#1

I think you are looking for something like this?

我想你在找这样的东西？

DECLARE @table TABLE (
    date_from DATE,
    date_to DATE,
    userid VARCHAR(50));
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Bob';
INSERT INTO @table SELECT '2016-03-26', '2016-05-01', 'Bob';
INSERT INTO @table SELECT '2016-05-02', '2016-04-25', 'Bob';
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Larry';
INSERT INTO @table SELECT '2016-05-02', '2016-04-25', 'Larry';
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Todd';

WITH Active AS (
    SELECT * FROM @table WHERE GETDATE() BETWEEN date_from AND date_to),
Latest AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY date_from DESC) AS row_id FROM @table)
SELECT
    l.userid,
    ISNULL(a.date_from, l.date_from) AS date_from
FROM
    Latest l
    LEFT JOIN Active a ON a.userid = l.userid
WHERE
    l.row_id = 1;

Results are:

结果是：

userid  date_from
Bob     2016-03-26
Larry   2016-05-02
Todd    2016-01-01

How does it work? I am just breaking down the problem into two steps, one to find any current records and one to find the latest record for each user. The logic is then:

它是如何工作的？我只是将问题分解为两个步骤，一个用于查找当前记录，另一个用于查找每个用户的最新记录。那么逻辑就是：

if there is a current record for this user then return the start date of this record;
如果该用户有当前记录，则返回该记录的开始日期;
if there isn't a current record for this user then return the latest start date for the user. This will either be in the future or the past. Because we take the latest date we will prioritise future over past.
如果该用户没有当前记录，则返回该用户的最新开始日期。这将是未来或过去。因为我们采取最新的日期，我们将优先考虑过去的未来。

This won't work if a user has multiple current records, and I am not sure I fully grasped your meaning of a "current record".

如果用户有多个当前记录，这将无效，我不确定我是否完全掌握了“当前记录”的含义。

Obviously, if you don't like common table expressions, you could convert this to work with sub queries (as in your example).

显然，如果您不喜欢公用表表达式，则可以将其转换为使用子查询（如示例所示）。

Anyway, it might give you a starting point?

无论如何，它可能会给你一个起点？

EDIT

编辑

Okay, I just noticed your logic is that you want (in priority order):

好的，我刚注意到你的逻辑就是你想要的（按优先顺序）：

a current record;
目前的记录;
the earliest future record;
最早的未来记录;
the latest historical record.
最新的历史记录。

This means a few tweaks, so here goes:

这意味着一些调整，所以这里：

DECLARE @table TABLE (
    date_from DATE,
    date_to DATE,
    userid VARCHAR(50));
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Bob';
INSERT INTO @table SELECT '2016-03-26', '2016-05-01', 'Bob';
INSERT INTO @table SELECT '2016-05-02', '2016-04-25', 'Bob';
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Larry';
INSERT INTO @table SELECT '2016-05-02', '2016-04-25', 'Larry';
INSERT INTO @table SELECT '2016-01-01', '2016-03-25', 'Todd';

WITH Active AS (
    SELECT * FROM @table WHERE GETDATE() BETWEEN date_from AND date_to),
LatestPast AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY date_from DESC) AS row_id FROM @table WHERE date_to < GETDATE()),
EarliestFuture AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY date_from ASC) AS row_id FROM @table WHERE date_from > GETDATE()),
Users AS (
    SELECT DISTINCT userid FROM @table)
SELECT
    u.userid,
    COALESCE(a.date_from, f.date_from, p.date_from) AS date_from
FROM
    Users u
    LEFT JOIN LatestPast p ON p.userid = u.userid AND p.row_id = 1
    LEFT JOIN EarliestFuture f ON f.userid = u.userid AND f.row_id = 1
    LEFT JOIN Active a ON a.userid = u.userid;

#1