pgsql/x-dragon-trialbal-by-period.sql

diff --git a/pgsql/x-dragon-trialbal-by-period.sql b/pgsql/x-dragon-trialbal-by-period.sql
index e69de29..d1af804 100644 (file)
--- a/pgsql/x-dragon-trialbal-by-period.sql
+++ b/pgsql/x-dragon-trialbal-by-period.sql
@@ -0,0 +1,43 @@
+CREATE OR REPLACE FUNCTION trialbal_byperiod(i_accnt_id INTEGER, i_date DATE)
+  RETURNS NUMERIC AS
+$BODY$
+DECLARE
+    v_balance NUMERIC;
+BEGIN
+
+    SELECT 
+            COALESCE(SUM( 
+                CASE WHEN accnt_type IN ('A','E') THEN  
+                        (trialbal_ending -  trialbal_beginning)   * -1
+                 ELSE 
+                        trialbal_ending -  trialbal_beginning 
+                 END
+             ), 0.0)
+    INTO
+            v_balance
+    FROM 
+            trialbal
+    
+    LEFT JOIN 
+            accnt
+    ON
+            accnt_id = trialbal_accnt_id
+    LEFT JOIN 
+            period
+    ON
+            period_id = trialbal_period_id
+    
+    WHERE
+            period_start <= i_date
+        AND
+            trialbal_accnt_id = i_accnt_id;
+
+    RETURN v_balance;
+    
+
+END;
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION  trialbal_byperiod(INTEGER, DATE)
+  OWNER TO admin;     
\ No newline at end of file