CODEWITHSUNDEEP
sqlsql-serveraggregate
Steven Chandra
Steven Chandra

Reputation: 39

SQL Aggregate Function from one table

So i have one table with 4 fields = the 1st is date,2nd is first balance, 3rd is mutations, 4th is end balance.

below images has 2 tables, the first tables is the original tables, i have to make queries so it can showed up like the second table.

*the SALDO AWAL is getting yesterday data of SALDO PENUTUPAN

*the SALDO PENUTUPAN is getting data from SALDO AWAL + MUTASI

AND so on until the data looks like the second table

enter image description here

UPDATE TRANSAKSI SET SALDO_AWAL = (SELECT t2.SALDO_PENUTUPAN
               FROM TRANSAKSI t2 
               WHERE t2.TANGGAL = '2015-10-10')where TANGGAL > '2015-10-10'

ABOVE is my sample code, but i think it was did not work well, so i need u guys help, Thank You

Upvotes: 1

Views: 123

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Given tanggal is unique, you can use this:

with cte(tanggal, saldo_awal, mutasi, saldo_penutupan) as (
    select
        tanggal,
        lag(saldo_penutupan, 1, 0) over (order by tanggal) saldo_awal,
        mutasi,
        saldo_penutupan
    from (
        select
            tanggal,
            mutasi,
            sum(mutasi) over (order by tanggal) saldo_penutupan
        from TRANSAKSI
    ) t
)
update t1
set t1.saldo_awal       = t2.saldo_awal,
    t1.saldo_penutupan  = t2.saldo_penutupan
from TRANSAKSI t1 join cte t2
on t1.tanggal = t2.tanggal

Upvotes: 1

Related Questions