上一季度的最后一个值，因为它的日期小于或等于当前行的日期 - SQL

I have the following table data:

我有以下表格数据：

I generated the last two columns with the following:

我用以下内容生成了最后两列：

SELECT PublishDate, QuarterEndDate, Value, FiscalYear, FiscalQuarter,
FIRST_VALUE(Value) OVER(PARTITION BY FiscalYear ORDER BY FiscalQuarter, PublishDate
                          ROWS 1 PRECEDING) as LAST_VAL,
Value - FIRST_VALUE(Value) OVER(PARTITION BY FiscalYear ORDER BY FiscalQuarter,PublishDate
                          ROWS 1 PRECEDING) as QTR_DIFF
FROM tabledata

I am trying to calculate what the differences are between quarters given that the information was published.

我试图计算出信息发布时各个季度之间的差异。

Basically, I want to calculate the current row value minus the last value of the previous fiscal quarter (in the same fiscal year) given that its PublishDate is less than or equal to the current rows PublishDate.

基本上，我想计算当前行值减去上一个会计季度的最后一个值（在同一个会计年度），因为它的PublishDate小于或等于当前行PublishDate。

If it is the first quarter than the first quarter numbers should be retained without any change.

如果是第一季度，则应保留第一季度的数字而不做任何更改。

In the above figure, the highlighted rows show a couple of the problems:

在上图中，突出显示的行显示了几个问题：

1) The zero value for the first quarter even though it should be the values itself (i.e. 19461)

1）第一季度的零值，即使它应该是值本身（即19461）

2) The preceding row is taking the previous row not the previous quarter - its taking the last value as the preceding row's value - not the last value from the quarter given that the publish date is less than or equal to it.

2）前一行是前一行而不是前一个季度 - 它将最后一个值作为前一行的值 - 而不是给定发布日期小于或等于它的季度的最后一个值。

Any help would be greatly appreciated... Thanks!

任何帮助将不胜感激...谢谢！

1 个解决方案

#1

I have not tested this yet but looking at your code you are partitioning by the year only, I wonder if this will work:

我还没有对此进行过测试，但是看看你的代码只是按年份进行分区，我想知道这是否有效：

SELECT PublishDate, QuarterEndDate, Value, FiscalYear, FiscalQuarter,
FIRST_VALUE(Value) OVER(PARTITION BY FiscalYear,FiscalQuarter ORDER BY FiscalQuarter, PublishDate
                          ROWS 1 PRECEDING) as LAST_VAL,
Value - FIRST_VALUE(Value) OVER(PARTITION BY FiscalYear,FiscalQuarter ORDER BY FiscalQuarter,PublishDate
                          ROWS 1 PRECEDING) as QTR_DIFF
FROM tabledata