以天为单位的多列日期差异返回负数

Question

我有一个我已经创建的视图，我想要显示交易日期的天增量。下面是my table + SQL语句的一些行：

SELECT
  FirstAppearenceDate 
 ,TranDate
 ,CountryName
 ,AvgTran
 ,RunningTotal
 ,ISNULL(RunningTotal - LAG(RunningTotal, 1) 
  OVER (PARTITION BY CountryName ORDER BY CountryName, TranDate),0) AS MoneyDelta

 ,ISNULL(DATEDIFF(day,LAG(CONVERT(DATETIME, TranDate, 103), 1) OVER 
  (ORDER BY CountryName, TranDate),
  CONVERT(DATETIME, TranDate, 103)),0) AS DaysDelta

 FROM LTV_Preprocessing_2
 ORDER BY CountryName, TranDate

结果是：

前30行

FirstAppearenceDate| TranDate| CountryName| AvgTran| RunningTotal| MoneyDelta   | DaysDelta
20150510    20150510    Afghanistan 50.0000000000   50.0000000000   0.0000000000    0
20150510    20150514    Afghanistan 22.0000000000   72.0000000000   22.0000000000   4
20150510    20150603    Afghanistan 40.0000000000   112.0000000000  40.0000000000   20
20150609    20150609    Afghanistan 25.0000000000   137.0000000000  25.0000000000   6
20150718    20150718    Afghanistan 27.5500000000   164.5500000000  27.5500000000   39
20150718    20150727    Afghanistan 135.6500000000  300.2000000000  135.6500000000  9
20151026    20151026    Afghanistan 10.0000000000   310.2000000000  10.0000000000   91
20151214    20151214    Afghanistan 20.0000000000   330.2000000000  20.0000000000   49
20150510    20160102    Afghanistan 10.0000000000   340.2000000000  10.0000000000   19
20150718    20160122    Afghanistan 18.5000000000   358.7000000000  18.5000000000   20
20150510    20160616    Afghanistan 47.0000000000   405.7000000000  47.0000000000   146
20140806    20140806    Aland Islands   10.0000000000   10.0000000000   0.0000000000    -680
20140808    20140808    Aland Islands   20.0000000000   30.0000000000   20.0000000000   2
20140816    20140816    Aland Islands   27.7100000000   57.7100000000   27.7100000000   8
20140806    20140819    Aland Islands   10.0000000000   67.7100000000   10.0000000000   3
20140820    20140820    Aland Islands   90.3300000000   158.0400000000  90.3300000000   1
20140820    20140823    Aland Islands   15.0800000000   173.1200000000  15.0800000000   3
20140826    20140826    Aland Islands   37.9000000000   211.0200000000  37.9000000000   3
20140912    20140912    Aland Islands   3.0800000000    214.1000000000  3.0800000000    17
20140919    20140919    Aland Islands   20.0000000000   234.1000000000  20.0000000000   7
20140820    20140820    Albania 79.0400000000   79.0400000000   0.0000000000    -30
20140821    20140821    Albania 22.0000000000   101.0400000000  22.0000000000   1
20140821    20140822    Albania 20.0000000000   121.0400000000  20.0000000000   1
20140821    20140824    Albania 50.0000000000   171.0400000000  50.0000000000   2
20140821    20140827    Albania 40.0000000000   211.0400000000  40.0000000000   3
20140821    20140828    Albania 48.0000000000   259.0400000000  48.0000000000   1
20140831    20140831    Albania 20.0000000000   279.0400000000  20.0000000000   3
20140821    20140901    Albania 80.0000000000   359.0400000000  80.0000000000   1
20140821    20140902    Albania 119.0000000000  478.0400000000  119.0000000000  1
20140821    20140910    Albania 30.0000000000   508.0400000000  30.0000000000   8

看看负面效果，我希望我的days delta表现得与money delta完全一样-对于每个国家，它都从0开始。在这里，你可以在Day Delta列中看到阿富汗的结束和奥兰群岛的开始之间的差异。

提前感谢！

Answer 1

包括按天数的CountryName分区增量应可获得所需的结果

   SELECT
      FirstAppearenceDate 
     ,TranDate
     ,CountryName
     ,AvgTran
     ,RunningTotal
     ,ISNULL(RunningTotal - LAG(RunningTotal, 1) 
      OVER (PARTITION BY CountryName ORDER BY CountryName, TranDate),0) AS MoneyDelta


 ,ISNULL(DATEDIFF(day,LAG(CONVERT(DATETIME, TranDate, 103), 1) OVER 
  (PARTITION BY CountryName  ORDER BY CountryName, TranDate),
  CONVERT(DATETIME, TranDate, 103)),0) AS DaysDelta

 FROM LTV_Preprocessing_2
 ORDER BY CountryName, TranDate