ValidFrom和ValidTo列-僅根據日期之間的值顯示最新行

把我的頭發扯下來，并不能 100% 確定這是可能的，但是在 SQL 中一切皆有可能，所以一定是這樣嗎？！

目的是能夠歷史地跟蹤每個月末有多少活躍帳戶。

我有兩個表：

• star_UserAccounts

  • 每小時保存帳戶資訊。
  • 如果資料相同，則什么都不會發生。
  • 如果資料發生更改，現有行將被標記為舊行并插入新行。這使一個不錯的小歷史記錄可以查看正在發生的更改。

• 每月最后一天

  • 保存我想要資料的日期（僅作為示例）

在“star_UserAccounts”表中，我有（升序）：

現在 - 我想回傳 2019 年 10 月 31 日有效的詳細資訊。當天有兩次更新，因此應回傳當天的最新行版本。

在將日期硬編碼到我的選擇腳本中時，我可以獲得該帳戶的單個行版本，但是當我嘗試一次將其應用于多個不同的日期時，它會選擇 2019 年 10 月 31 日的兩行，但我只希望它在需要的日子里獲取最新的行。

我如何確保為每個推入的日期拉取最新的行？

這是一個可以玩的 SQL Fiddle：http ://sqlfiddle.com/#!18/ed372/12

I have kept in the syntax error which is within the subquery, as this sort of shows what I am trying to achieve as I need to pass the date into the sub query that collects the MAX record for the date.

Appreciate any assistance. Such a hard one to explain in an easy way, apologies if it's very confusing.

Thanks.

uj5u.com熱心網友回復：

你把它復雜化了。您可以使用簡單的行編號來執行此操作

SELECT
    LastDayOfMonth,
    COUNT(*) ActiveAtDate
FROM (
    SELECT
      ld.LastDayOfMonth,
      ua.UserId,
      rn = ROW_NUMBER() OVER (PARTITION BY ld.LastDayOfMonth, ua.UserId ORDER BY ua.ValidToDate DESC)
    FROM dbo.star_UserAccounts ua
    INNER JOIN LastDayOfMonth ld
      ON ld.LastDayOfMonth >= ua.InsertedDate
      AND DATEADD(day, 1, ld.LastDayOfMonth) < ua.ValidToDate
    WHERE ua.UserId = 'JoeBloggs'
) latestRowForDate
WHERE rn = 1
GROUP BY LastDayOfMonth;

SQL小提琴

uj5u.com熱心網友回復：

更新

重新閱讀您的帖子后，我意識到在回答之前我沒有完全理解您的問題。我會留下我的原始答案，因為它可能會有所幫助。

您的情況可能與使用COUNT DISTINCT一樣簡單，如下所示：

SELECT
    LastDayOfMonth,
    COUNT ( DISTINCT UserId ) AS ActiveAtDate
FROM @star_UserAccounts AS ua
CROSS APPLY (

    SELECT LastDayOfMonth FROM @LastDayOfMonth AS ld
        WHERE ld.LastDayOfMonth BETWEEN CAST( ua.InsertedDate AS date ) AND CAST( ua.ValidToDate AS date )
        
) AS x
GROUP BY
    LastDayOfMonth
ORDER BY
    LastDayOfMonth;

退貨

 ---------------- -------------- 
| LastDayOfMonth | ActiveAtDate |
 ---------------- -------------- 
| 2019-07-31     |            1 |
| 2019-08-31     |            1 |
| 2019-09-30     |            1 |
| 2019-10-31     |            1 |
| 2019-11-30     |            1 |
| 2019-12-31     |            1 |
 ---------------- -------------- 

CROSS APPLY在將結果集限制為在 LastDayOfMonth 表中的時間段內具有活動的用戶方面，使用仍然具有相同的效果。

下面的原始答案

嘗試類似以下內容：

DECLARE @star_UserAccounts table (
    [UserId] nvarchar (20) COLLATE Latin1_General_CI_AS NOT NULL,
    [InsertedDate] datetime NULL,
    [ValidToDate] datetime NULL,
    [RowVersion] int NOT NULL
);

INSERT INTO @star_UserAccounts ( [UserId], [InsertedDate], [ValidToDate], [RowVersion] )
VALUES
    ('JoeBloggs', '2020-01-09 11:08:28.970', '2020-01-16 11:08:24.547', 4 ), 
    ('JoeBloggs', '2020-01-09 10:08:28.013', '2020-01-09 11:08:28.813', 3 ), 
    ('JoeBloggs', '2019-10-31 16:08:28.027', '2020-01-09 10:08:27.840', 2 ), 
    ('JoeBloggs', '2019-07-19 13:43:09.083', '2019-10-31 16:08:27.633', 1 );

DECLARE @LastDayOFMonth table (
    LastDayOfMonth date
);

INSERT INTO @LastDayOFMonth ([LastDayOfMonth])
VALUES
    ('2019-07-31 00:00:00' ), 
    ('2019-08-31 00:00:00' ), 
    ('2019-09-30 00:00:00' ), 
    ('2019-10-31 00:00:00' ), 
    ('2019-11-30 00:00:00' ), 
    ('2019-12-31 00:00:00' );

;WITH current_activity AS (
    SELECT
        UserID, MAX( RowVersion ) AS CurrentVersion
    FROM @star_UserAccounts
    GROUP BY
        UserID
)
SELECT
    ua.*
FROM current_activity
INNER JOIN @star_UserAccounts AS ua
    ON current_activity.UserID = ua.UserID
    AND current_activity.CurrentVersion = ua.[RowVersion]
WHERE EXISTS (
    
    SELECT * FROM @star_UserAccounts AS u
    CROSS APPLY (
        SELECT * FROM @LastDayOfMonth AS ld
            WHERE CAST( ld.LastDayOfMonth AS date ) BETWEEN CAST( u.InsertedDate AS date ) AND CAST( u.ValidToDate AS date )
    ) AS d
    WHERE
        u.UserId = current_activity.UserId

)
ORDER BY
    current_activity.UserId;

退貨

 ----------- ------------------------- ------------------------- ------------ 
|  UserId   |      InsertedDate       |       ValidToDate       | RowVersion |
 ----------- ------------------------- ------------------------- ------------ 
| JoeBloggs | 2020-01-09 11:08:28.970 | 2020-01-16 11:08:24.547 |          4 |
 ----------- ------------------------- ------------------------- ------------ 

The CTE gets the most current UserId's RowVersion and then looks to see if the user had any activity during the dates listed in LastDayOfMonth.

Using CROSS APPLY restricts the results to users with any activity that includes the LastDayOfMonth.

標籤：sql sql-server tsql data-warehouse

上一篇：Oracle( )聯接轉換為SQLServer

下一篇：在SQL宣告陳述句中使用字串的一部分