给定创建时间和修改时间的 SQL 选择特定日期的记录 [英] SQL to select records for a specific date given created time and modified time

问题描述

我的管理层要求我分析"我们的问题跟踪数据库 - 他们用它来编目我们的内部错误等.我的 SQL 和 DB 技能原始，所以我需要一些帮助.

I've been asked by my management to "analyze" our issue tracking database - they use it to catalog our internal bugs, etc. My SQL and DB skills are primitive so I need some help.

我收到了一张包含 300 万条记录的表格.它占了 25 万个错误.错误的每个修订版都是表中的一行.这就是 25 万个错误最终在 300 万条记录中的原因.

I received a single table of 3 million records. It accounts for 250K bugs. Each revision of a bug is a row in the table. That's how 250K bugs ends up in 3 million records.

数据看起来像这样

BugID  Created      Modified     AssignedTo  Priority  Status
27     mar-31-2003  mar-31-2003  mel         2         Open
27     mar-31-2003  apr-01-2003  mel         1         Open
27     mar-31-2003  apr-10-2003  steve       1         Fixed

因此，我拥有每个错误的完整历史记录，并且可以看到它们每天是如何演变的.

Thus, I have the complete history of every bug and can see how they have evolved every day.

我被要求提供很多东西作为报告.但我被要求做的最基本的问题是让某人能够查看在特定日期存在的错误.

I have a lot of things I've been asked to provide as reports. But the most basic question I have been asked to do is enable someone to look at the bugs as they existed at a specific date.

例如，如果有人询问 2003 年 3 月 1 日的所有错误，那么错误 27 不在结果中，因为它在当天不存在.或者，如果他们在 4 月 7 日询问错误，他们会看到错误 27 并且仍然标记为打开

For example, if someone asked for all the bugs on mar 1 2003, then bug 27 isn't in the result because it doesn't exist on that day. Or if they asked for the bugs on April 7 they'd see bug 27 and that still marked as open

根据我概述的架构，什么 SQL 查询将提供特定日期的记录视图?

Given the schema I outlined, what SQL query will provide a view of the records on a specific date?

我使用的是 Microsoft SQL Server 2008

I am using Microsoft SQL Server 2008

正如我所说，我的 SQL 技能很原始.我能够使用 WHERE 子句过滤掉目标日期之后所做的修改和目标日期之前不存在的错误，但无法找到该日期发生的单个记录.

As I said my SQL skills are primitive. I was able use WHERE clauses to filter out modifications made after the target date and bugs that didn't exist by the target date, but wasn't able to find the single record happened on that date.

推荐答案

WITH
  sequenced_data AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY BugID ORDER BY Modified DESC) AS sequence_id,
    *
  FROM
    yourTable
  WHERE
    Modified <= @datetime_stamp
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

这假设您想查看已修复的错误.如果您想过滤掉很久以前"(例如 30 天)修复的错误，请添加此...

This assumes you want to see the fixed bugs. If you want to filter out bugs that were fixed 'a long time ago' (say, 30 days), add this...

AND (Status <> 'Fixed' OR Modified >= DATEADD(DAY, -30, @datetime_stamp))

这篇关于给定创建时间和修改时间的 SQL 选择特定日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！