如何选择时间戳之间状态的记录？ T-SQL [英] How To Select Records in a Status Between Timestamps? T-SQL

问题描述

我有一个T-SQL报价表，并且需要能够计算过去几个月中处于打开状态的报价。

I have a T-SQL Quotes table and need to be able to count how many quotes were in an open status during past months.

我必须使用的是' Add_Date '时间戳和' Update_Date '时间戳。将报价放入 1 的 Closed_Status 中后，该报价将无法更新。因此， Update_Date 实际上成为 Closed_Status 时间戳。

The dates I have to work with are an 'Add_Date' timestamp and an 'Update_Date' timestamp. Once a quote is put into a 'Closed_Status' of '1' it can no longer be updated. Therefore, the 'Update_Date' effectively becomes the Closed_Status timestamp.

我被困住了，因为我不知道如何选择在特定月份内所有的开放报价。

I'm stuck because I can't figure out how to select all open quotes that were open in a particular month.

以下是一些示例记录：

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

在这上面，我尝试过在过滤时进行的情况，但是我似乎无法弄清楚这个难题。理想情况下，我不会对日期进行硬编码，因为它跨越了数年之久，而且我也不想在写完以后再保留它。

I've hit a mental wall on this one, I've tried to do some case when filtering but I just can't seem to figure this puzzle out. Ideally I wouldn't be hard-coding in dates because this spans years and I don't want to maintain this once written.

在此先感谢您的帮助。

推荐答案

每月进行一次。因此，想到了三个选择：

You are doing this by month. So, three options come to mind:

• 使用左联接。


• 递归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;

这确实假设每个月至少有一个未完成的记录。为此目的，这似乎是合理的。

This does assume that each month has at least one open record. That seems reasonable for this purpose.

这篇关于如何选择时间戳之间状态的记录？ T-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！