SQL DATEDIFF 非连续日期

发布于 2024-08-12 08:23:32 字数 602 浏览 5 评论 0原文

我有一个如下所示的表：

Code    Mark Date   Notional
Beta    5/1/2008    $1,102,451.00
Beta    5/2/2008    $1,102,451.00
Beta    5/5/2008    $1,102,451.00
Beta    5/6/2008    $1,102,451.00

我需要创建一个表，其中一列中包含所有标记日期，并且在另一列中排序时每个相邻标记日期之间的差异。这是我写的 SQL：

SELECT 
    Current.[Mark Date],
    Prev.[Mark Date],
    (DATEDIFF("d", Prev.[Mark Date], Current.[Mark Date])/365)

FROM Notional as Prev, Notional as Current
WHERE (Prev.[Mark Date] = (Current.[Mark Date]-1));

但是，此 SQL 不会在周末 5/5/2008 -> 加入例如 2008 年 5 月 2 日或长周末。我将如何让表在非连续日期上自连接？感谢您的帮助！

原文

I've got a table that looks like this:

Code    Mark Date   Notional
Beta    5/1/2008    $1,102,451.00
Beta    5/2/2008    $1,102,451.00
Beta    5/5/2008    $1,102,451.00
Beta    5/6/2008    $1,102,451.00

I need to create a table that has all of the Mark Dates in one column and the difference between each adjacent Mark Date when sorted in another column. This is the SQL I've written:

SELECT 
    Current.[Mark Date],
    Prev.[Mark Date],
    (DATEDIFF("d", Prev.[Mark Date], Current.[Mark Date])/365)

FROM Notional as Prev, Notional as Current
WHERE (Prev.[Mark Date] = (Current.[Mark Date]-1));

However, this SQL will not join over the weekend 5/5/2008 -> 5/2/2008 for example or on long weekends. How would I go about getting the table to self join over non contiguous dates? Thanks for all the help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧时浪漫 2024-08-19 08:23:32

执行此操作的唯一方法是在支持行号的 SQL 中按日期排序的选择上使用 ROW_NUMBER（和 ROW_NUMBER+1），或者使用按日期顺序填充的自动增量键创建临时表。

除了诉诸非连接解决方案（即循环）之外，没有其他方法。

回复收藏 0 原文

〃温暖了心ぐ 2024-08-19 08:23:32

在按日期选择并加入该订单时，您可以尝试使用 ROW_NUMBER。

编辑。完成连接。

您可以做的是将表自身连接到大于该行的日期，然后分组并选择最小值。

像这样的东西

DECLARE @Table TABLE(
        DateVal DATETIME
)

INSERT INTO @Table SELECT '01 May 2009'
INSERT INTO @Table SELECT '02 May 2009'
INSERT INTO @Table SELECT '05 May 2009'
INSERT INTO @Table SELECT '06 May 2009'

SELECT  t.DateVal,
        MIN(tNext.DateVal) NextDate
FROM    @Table t LEFT JOIN
        @Table tNext ON t.DateVal < tNext.DateVal
GROUP BY t.DateVal

我知道这是 Sql Server 代码，但可以轻松更改为 MS Access。

这应该返回以下内容：

StartDate                  EndDate
2009-05-01 00:00:00.000 2009-05-02 00:00:00.000
2009-05-02 00:00:00.000 2009-05-05 00:00:00.000
2009-05-05 00:00:00.000 2009-05-06 00:00:00.000
2009-05-06 00:00:00.000 NULL

You can try to use ROW_NUMBER when selecting and join on that order by the date.

EDIT. Done with joins.

What you can do is to join the table to itself, on dates bigger than that row, and then group by and select the min.

Something like this

DECLARE @Table TABLE(
        DateVal DATETIME
)

INSERT INTO @Table SELECT '01 May 2009'
INSERT INTO @Table SELECT '02 May 2009'
INSERT INTO @Table SELECT '05 May 2009'
INSERT INTO @Table SELECT '06 May 2009'

SELECT  t.DateVal,
        MIN(tNext.DateVal) NextDate
FROM    @Table t LEFT JOIN
        @Table tNext ON t.DateVal < tNext.DateVal
GROUP BY t.DateVal

I know this is Sql Server code, but can easily be changed to MS Access.

This should return the folowing:

StartDate                  EndDate
2009-05-01 00:00:00.000 2009-05-02 00:00:00.000
2009-05-02 00:00:00.000 2009-05-05 00:00:00.000
2009-05-05 00:00:00.000 2009-05-06 00:00:00.000
2009-05-06 00:00:00.000 NULL

回复收藏 0 原文

~没有更多了~