sql - 如何选择处于时间戳之间状态的记录？数据库

Question

我有一个 T-SQL 报价表，需要能够计算过去几个月中有多少报价处于未结状态。

我必须使用的日期是“Add_Date”时间戳和“Update_Date”时间戳。一旦将报价放入“1”的“Closed_Status”，它就无法再更新。因此，“Update_Date”实际上变成了 Closed_Status 时间戳。

我被卡住了，因为我无法弄清楚如何选择在特定月份开放的所有开放报价。

下面是一些示例记录:

Quote_No   Add_Date   Update_Date  Open_Status  Closed_Status
001        01-01-2016  NULL         1            0
002        01-01-2016  3-1-2016     0            1
003        01-01-2016  4-1-2016     0            1

期望的结果是:

Year  Month  Open_Quote_Count
2016  01     3
2016  02     3
2016  03     2
2016  04     1

我在这个问题上遇到了障碍，我尝试做一些 case when 过滤，但我似乎无法解决这个难题。理想情况下，我不会在日期中进行硬编码，因为这跨越了数年，而且我不想在编写后维护它。

预先感谢您的帮助。

Answer 1

您按月执行此操作。因此，我想到了三个选项:

• 使用 left join 的所有月份的列表。
• 递归 CTE。
• 一个数字表。

让我展示最后一个:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master..spt_values
     )
select format(dateadd(month, n.n, q.add_date), 'yyyy-MM') as yyyymm,
       count(*) as Open_Quote_Count
from quotes q join
     n
     on (closed_status = 1 and dateadd(month, n.n, q.add_date) <= q.update_date) or
        (closed_status = 0 and dateadd(month, n.n, q.add_date) <= getdate()) 
group by format(dateadd(month, n.n, q.add_date), 'yyyy-MM')
order by yyyymm;

这确实假设每个月至少有一个打开的记录。这对于这个目的来说似乎是合理的。