use preceding in calculation sql

Question

I need to calculate the column E using column B,C,D & previous row of E... I have the sample statement and calculation for reference. Note that prev(E) is the preceding value of E which I need to use in calculation but am unable to.

+---------------------------------------------------------------------------------------------------------------------------------------+  
| TransactionDt | total_allotment(B) | invchange(C) | roomssold_flag(D) | available(E) |       samplestatement            | calculation |   
+---------------------------------------------------------------------------------------------------------------------------------------+   
|    1/1/16     |         5          |      0       |       null        |       5      |  E=case when D=null then B       |  5          |   
|    1/2/16     |         5          |      0       |        1          |       4      |  E=case when C=0 then prev(E)-D  |  E=(5-1)    |   
|    1/3/16     |         5          |      0       |        0          |       4      |  E=case when C=0 then prev(E)-D  |  E=(4-0)    |   
|    1/4/16     |         6          |      1       |        1          |       5      |  E=case when C=1 then B-D        |  E=(6-1)    |   
|    1/5/16     |         6          |      0       |        0          |       5      |  E=case when C=0 then prev(E)-D  |  E=(5-0)    |   
|    1/6/16     |         7          |      1       |        1          |       6      |  E=case when C=1 then B-D        |  E=(7-1)    |   
+---------------------------------------------------------------------------------------------------------------------------------------+  

Answer 1

You can use first_value() function with preceding clause to get privious value:

set dateformat dmy;
declare @t table (TransactionDt smalldatetime, b int, c int, d int, e int);
insert into @t (TransactionDt, b, c, d, e) values
(cast('01.01.2016' as date), 5, 0, null, 5),
(cast('02.01.2016' as date), 5, 0, 1, 4),
(cast('03.01.2016' as date), 5, 0, 0, 4),
(cast('04.01.2016' as date), 6, 1, 1, 5),
(cast('05.01.2016' as date), 6, 0, 0, 5),
(cast('06.01.2016' as date), 7, 1, 1, 6);

select
  t.*
  ,first_value(t.e) over(order by t.TransactionDt asc rows 1 preceding) [prevE]
  ,case t.c
     when 0 then
       first_value(t.e)
       over(order by t.TransactionDt asc rows 1 preceding)
       - t.d
     when 1 then
       t.b - t.d
   end [calculation]
from
  @t t
order by
  t.TransactionDt
;

Tested on MS SQL 2012.

I'm not big fan of Teradata, but this should work:

select
  t.e
  ,sum(t.e) 
  over(order by t.TransactionDt asc rows between 1 preceding and 1 preceding) ePrev
  ,case t.c
     when 0 then
       sum(t.e) 
       over(order by t.TransactionDt asc rows between 1 preceding and 1 preceding)
       - t.d
     when 1 then
       t.b - t.d
   end calculation
from
  (
    select cast('01.01.2016' as date format 'dd.mm.yyyy') TransactionDt, 5 b, 0 c, null d, 5 e from (select 1 x) x
    union all
    select cast('02.01.2016' as date format 'dd.mm.yyyy') TransactionDt, 5 b, 0 c, 1 d, 4 e from (select 1 x) x
    union all
    select cast('03.01.2016' as date format 'dd.mm.yyyy'), 5, 0, 0, 4 from (select 1 x) x
    union all
    select cast('04.01.2016' as date format 'dd.mm.yyyy'), 6, 1, 1, 5 from (select 1 x) x
    union all
    select cast('05.01.2016' as date format 'dd.mm.yyyy'), 6, 0, 0, 5 from (select 1 x) x
    union all
    select cast('06.01.2016' as date format 'dd.mm.yyyy'), 7, 1, 1, 6 from (select 1 x) x
  ) t
order by
  t.TransactionDt
;

Answer 2

When you need to restart the calculation whenever invchange=1 you have to create a group for partitioning using

sum(invchange)
over (order by TransactionDt
      rows unbounded preceding) as grp

invchange seems to be based on a previous row query, so you need to nest it't calculation in a Dervied Table.

Now you it's the total_allotment value minus a Cumulative Sum over roomssold_flag:

select t.*,
   b - sum(coalesce(D,0))
       over (partition by grp
             order by TransactionDt
             rows unbounded preceding)
from
 (
   select TransactionDt,b,c,d,
      sum(c) over (order by TransactionDt rows unbounded preceding) as grp
   from t
 ) as t

Btw, using a 0/1 flag to get dynamic partitioning is similar to RESET WHEN