If we like to know the net value of each contract, we just need to do a group by with contract number. But if we like to know the latest status of contract with the net value simple group by won't do it. we need to use the SQL Windowing to achieve that. We not only need sum up each contract but also to rank each row within the contract number by date. Then we only keep the row with latest date or rank 1. Here is an example.
CREATE TABLE "Contract_Transaction" ( "Contract_Number" int2 DEFAULT NULL, "Contract_Status" char(1) DEFAULT NULL, "Contract_Amount" numeric DEFAULT NULL, "Change_Date" date DEFAULT NULL )
insert into Contract_Transaction values (1,'Active',100.00, '2010-4-1'); insert into Contract_Transaction values (1,'Cancel',-40.00, '2010-4-3'); insert into Contract_Transaction values (2,'Active', 50.00, '2010-4-2');
WITH RNK (Contract_Number, Contract_Status, Contract_Amount, RNK) AS ( SELECT Contract_Number, Contract_Status, sum(Contract_Amount) OVER ( PARTITION BY Contract_Number ORDER BY Change_Date ROWS UNBOUNDED PRECEDING ), RANK() OVER (PARTITION BY Contract_Number ORDER BY Change_Date ) FROM Contract_Transaction ) SELECT * FROM TMP WHERE RNK = 1