前几个月状态的T-SQL记录计数 [英] T-SQL Count of Records in Status for Previous Months

问题描述

我有一个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后，该报价将无法更新。因此， 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 'Won' or 'Loss' columns with a value of '1' in that column it can no longer be updated. Therefore, the 'Update_Date' effectively becomes the Closed_Status timestamp.

以下是一些示例记录：

Quote_No   Add_Date   Update_Date  Open_Quote  Win  Loss
001        01-01-2016  NULL         1           0    0
002        01-01-2016  3-1-2016     0           1    0
003        01-01-2016  4-1-2016     0           0    1

这里是所有数据的链接：
https://drive.google.com/open?id=0B4xdnV0LFZI1T3IxQ2ZKRDhNd1k

Here's a link to all the data here: https://drive.google.com/open?id=0B4xdnV0LFZI1T3IxQ2ZKRDhNd1k

今年早些时候我问了这个问题，并一直使用以下代码：

I asked this question previously this year and have been using the following code:

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;

问题是此代码返回的是累积值。因此，一月很好，但是二月才是一月+二月，三月是一月+二月+三月，依此类推。等等。我花了一段时间才发现这一点，现在返回的数字还差得远，我想纠正它们。

The problem is this code is returning a cumulative value. So January was fine, but then Feb is really Jan + Feb, and March is Jan+Feb+March, etc. etc. It took me a while to discover this and the numbers returned now way, way off and I'm trying to correct them.

从完整的数据集中看，这段代码的结果是：

From the full data set the results of this code are:

Year-Month  Open_Quote_Count
2017-01      153
2017-02      265
2017-03      375
2017-04      446
2017-05      496
2017-06      560
2017-07      609

所需结果将是多少报价在该特定月份处于开放状态，而不是累积状态：

The desired result would be how many quotes were in an open status during that particular month, not the cumulative :

Year-Month  Open_Quote_Count
2017-01     153
2017-02     112
2017-03     110
2017-04     71

预先感谢您的帮助！

推荐答案

除非我丢失了某些内容，否则LAG（）将是一个很好的选择适合这里

Unless I am missing something, LAG() would be a good fit here

示例

Declare @YourTable Table ([Year-Month] varchar(50),[Open_Quote_Count] int)
Insert Into @YourTable Values 
 ('2017-01',153)
,('2017-02',265)
,('2017-03',375)
,('2017-04',446)
,('2017-05',496)
,('2017-06',560)
,('2017-07',609)

Select *
      ,NewValue = [Open_Quote_Count] - lag([Open_Quote_Count],1,0) over (Order by [Year-Month]) 
 From @YourTable   --<< Replace with your initial query

返回

Year-Month  Open_Quote_Count    NewValue
2017-01     153                 153
2017-02     265                 112
2017-03     375                 110
2017-04     446                 71
2017-05     496                 50
2017-06     560                 64
2017-07     609                 49

这篇关于前几个月状态的T-SQL记录计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！