仅按日期缓慢更改维度

Question

我有一个数据库表，使用缓慢变化维度的数据仓库的概念来跟踪旧版本。因此，我使用日志触发器机制实现了它。

我的桌子是这样的：

CREATE TABLE "T_MyTable" (
    "Id" INT NOT NULL DEFAULT NULL,
    "Description" NVARCHAR(255) NULL DEFAULT NULL )

我创建了一个 hystory 表

CREATE TABLE "T_MyTableHistory" (
    "Id" INT NOT NULL DEFAULT NULL,
    "Description" NVARCHAR(255) NULL DEFAULT NULL,
    StartDate DATETIME,
    EndDate   DATETIME )

然后，使用这样的触发器，我得到了历史记录：

CREATE TRIGGER TableTrigger ON T_MyTable FOR DELETE, INSERT, UPDATE AS
DECLARE @NOW DATETIME
SET @NOW = CURRENT_TIMESTAMP
UPDATE T_MyTableHistory
   SET EndDate = @now
  FROM T_MyTableHistory, DELETED
 WHERE T_MyTableHistory.Id = DELETED.Id
   AND T_MyTableHistory.EndDate IS NULL
INSERT INTO T_MyTableHistory (Id, Description, StartDate, EndDate)
SELECT Id, Description, @NOW, NULL
  FROM INSERTED

而且，要查询历史记录表，我使用

SELECT  Id, Description
    FROM T_MyTableHistory
    WHERE @DATE >= StartDate
    AND (@DATE < EndDate OR EndDate IS NULL)

现在，我的问题是：我的客户实际上只会按日期查询历史记录表(即没有一天中的时间(，所以我需要获取该日期的记录版本。我想到了两个选择：

1. 更改触发器(如何？(以每个日期仅记录一条"历史记录"。

2. 保持触发器不变，记录数据库中的所有更改(包括日期和时间(，但随后查询历史记录表以获取特定日期的最新版本(如何？

我的感觉是第二个选项更容易实现，否则触发器可能会变得复杂(插入或更新，取决于当前日期历史记录的存在(。

我需要一些帮助来选择正确的方向，我想在所选选项中提供一个所需的 SQL 查询示例。

Answer 1

我同意你的第二种意见。 最好将日期与时间一起保存。根据日期使用情况筛选数据时 CONVERT()函数来确保仅比较 DATE。此外，当客户端输入单个日期时，如果记录具有相同的开始日期和结束日期它们不会在您的过滤器中，因此请使用日期>= 开始日期和日期 <= 结束日期不是 (>= ，<(

DECLARE @Date AS DATETIME
SET @Date = '2013-07-30'
SELECT TOP 1 Id, Description
    FROM T_MyTableHistory
    WHERE CONVERT(VARCHAR(20), @DATE, 103) 
       >= CONVERT(VARCHAR(20), StartDate, 103)
    AND (CONVERT(VARCHAR(20), @DATE, 103) 
       < CONVERT(VARCHAR(20), EndDate, 103) OR EndDate IS NULL)
    ORDER BY StartDate DESC

Answer 2

最后，我想出了这个查询：

SELECT Id, Description
    FROM T_MyTableHistory
    WHERE ( DateAdd(day, datediff(day,0, @MyDate), 0) >= StartDate ) AND 
    (( DateAdd(day, datediff(day,0, @MyDate), 0) < EndDate ) OR ( EndDate IS NULL ))

这应该比 varchar<->datetime 转换更快，并且它也应该与区域设置无关。顺便说一下，此查询不需要 TOP 1 和 ORDER BY 子句，因为函数

DateAdd(day, datediff(day,0, @MyDate)

自动返回所选日期，时间为"午夜"(例如20141215 00：00：00(，因此具有相同日期的记录会自动从结果中删除。

引用：

如何仅从 SQL Server 日期时间数据类型返回日期部分

在 SQL Server 中删除日期时间部分的最佳方法