CODEWITHSUNDEEP
sqlsql-servert-sqlssmscalculation
HackingWiz
HackingWiz

Reputation: 69

SQL calculation with previous row + current row

I want to make a calculation based on the excel file. I succeed to obtain 2 of the first records with LAG (as you can check on the 2nd screenshot). Im out of ideas how to proceed from now and need help. I just need the Calculation column take its previous data. I want to automatically calculate it over all the dates. I also tried to make a LAG for the calculation but manually and the result was +1 row more data instead of NULL. This is a headache.

LAG(Data ingested, 1) OVER ( ORDER BY DATE ASC ) AS LAG

calculation enter image description here

Upvotes: 0

Views: 1488

Answers (2)

SteveC
SteveC

Reputation: 6015

Imo this could be solved using a "gaps and islands" approach. When Reconciliation>0 then create a gap. SUM(GAP) OVER converts the gaps into island groupings. In the outer query the 'sum_over' column (which corresponds to the 'Calculation') is a cumumlative sum partitioned by the island groupings.

with 
gap_cte as (
    select *, case when [Reconciliation]>0 then 1 else 0 end gap
    from CalcTable),
grp_cte as (
    select *, sum(gap) over (order by [Date]) grp
    from gap_cte)
select *, sum([Reconciliation]+
               (case when gap=1 then 0 else Aves end)-
               (case when gap=1 then 0 else Microa end)) 
               over (partition by grp order by [Date]) sum_over
from grp_cte;

[EDIT] The CASE statement could be CROSS APPLY'ed instead

with 
grp_cte as (
    select c.*, v.gap, sum(v.gap) over (order by [Date]) grp
    from #CalcTable c
         cross apply (values (case when [Reconciliation]>0 then 1 else 0 end)) v(gap))
select *, sum([Reconciliation]+
               (case when gap=1 then 0 else Aves end)-
               (case when gap=1 then 0 else Microa end)) 
               over (partition by grp order by [Date]) sum_over
from grp_cte;

Here is a fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You seem to want cumulative sums:

select t.*,
       (sum(reconciliation + aves - microa) over (order by date) -
        first_value(aves - microa) over (order by date)
       ) as calculation
from CalcTable t;

Here is a SQL Fiddle.

EDIT:

Based on your comment, you just need to define a group:

select t.*,
       (sum(reconciliation + aves - microa) over (partition by grp order by date) -
        first_value(aves - microa) over (partition by grp order by date)
       ) as calculation
from (select t.*,
             count(nullif(reconciliation, 0)) over (order by date) as grp
      from CalcTable t
     ) t
order by date;

Upvotes: 1

Related Questions