比较列上当前月和上个月的行，SQL Server 2012

问题描述

我需要一些指导和帮助来解决一个我不太确定如何在SQL Server 2012中解决的问题.我认为LAG和LEAD函数可能有用，但我不确定.

I need some guidance and help with a question I am not entirely sure how to solve in SQL Server 2012. I think LAG and LEAD functions could be useful but I am not sure.

这是我现在的数据:

=========================================
YearMonth   LocationCode    Active
=========================================
201405      123              0
201406      123              2
201409      211              1
201410      211              0
201411      214              0
201412      214              3

我们有一个YearMonth列，显示每个locationCode的状态，以及一个Active int表示每个LocationCode

We have a YearMonth column that shows how the status looked like for each locationCode and an Active int that represents a quality for each LocationCode

目标:

我的目标是比较当前YearMonth(我们将其称为201406)和先前的Yearmonth(我们将其称为201405)的LocationCode:

My objective is to compare the LocationCode for for the current YearMonth (let's call it 201406) and the previous Yearmonth (let's call it 201405):

一个例子:

=========================================
YearMonth   LocationCode    Active
=========================================
201405      123              0
201406      123              2

基本上，我要弄清楚的是如何比较当前月份的行(201406)和上个月的行(201405)在名为Active的列上.

Basically what I am trying to figure out is how to compare the current month's row (201406) to the previous month's row (201405) on the column called Active.

如果当前月份的行Active列为非零，而上个月的活动"列为零，那么我们得出的结论是当前月份的行为新建"(1)，否则为(0).

If the current month's row Active column is a non-zero and the previous month's Active was a zero, then we conclude the current month's row to be "New" (1) else (0).

下面提供了一个示例:

==================================================
YearMonth   LocationCode    Active   New
===================================================
201405      123              0        0
201406      123              2        1
201409      211              1        0
201410      211              0        0
201411      214              0        0
201412      214              3        1

我该如何解决这个问题?

How can I solve this problem?

推荐答案

我认为您可以使用这样的查询:

I think you can use a query like this:

SELECT *,
    CASE
        WHEN Active <> 0 AND
             ISNULL(LAG(Active) OVER (PARTITION BY LocationCode ORDER BY YearMonth), 0) = 0 THEN 1
        ELSE 0
    END As New
FROM yourTable;

[SQL Fiddle演示]

这篇关于比较列上当前月和上个月的行，SQL Server 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！