Re: Group BY and Chart of Accounts - Mailing list pgsql-general

Works like a charm.  Thank you very much Justin.

On Thu, Oct 30, 2008 at 3:49 AM, justin <justin@emproshunts.com> wrote:
> There was a number of code mistakes  in my examples as i was just doing it
> off the top of my head,  just went through it and got it all working.
> I had to change the function around as it was double dipping accounts
> just run this and it does work.
> --------------------------------------------------------------
>
> Create table coa (
>   coa_id serial not null,
>   parent_id int not null default 0,
>   doIhaveChildren boolean default false,
>   account_name text null );
>
>
> Create Table general_ledger_transactions(
>   transaction_id serial not null,
>   coa_id integer,
>   accounting_period integer,
>   debit numeric(20,10) ,
>   credit numeric(20,10),
>   transaction_date timestamp);
>
>
> Create table  accounting_periods (
>   accounting_period serial not null,
>   start_date date,
>   end_date date,
>   accounting_period_Open boolean);
>
> Insert into coa values (10, default, True, 'ParentAccount1');
> Insert into coa values (11, 10, True, 'ChildAccount1');
> Insert into coa values (12, 11, false, 'ChildAccount2');
> Insert into coa values (13, default, false, 'ChildAccount3');
>
> Insert into Accounting_Periods values ( 1, '2008-10-01', '2008-10-31', true
> );
> Insert into Accounting_Periods values ( 2, '2008-11-01', '2008-11-30', true
> );
>
> Insert into general_ledger_transactions values(  default, 11,  1, 30.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 20.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 10.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  1, 50.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  1, 1.0, 0.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  1, 0.0,
> 111.0, current_timestamp);
>
>
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 30.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 20.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 10.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 12,  2, 0.0, 50.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 11,  2, 0.0, 1.0,
> current_timestamp);
> Insert into general_ledger_transactions values(  default, 13,  2, 111.0,
> 0.0, current_timestamp);
>
>
>
> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
> integer, PassedPeriodID integer) RETURNS NUMERIC AS
> $FunctionCode$
> DECLARE
>   retval NUMERIC = 0.0 ;
> begin
>     return (SELECT
>       coalesce ( (select Sum(general_ledger_transactions.debit ) from
> general_ledger_transactions where general_ledger_transactions.coa_id =
> coa.coa_id and general_ledger_transactions.accounting_period =
> PassedPeriodID), 0 ) +
>       (CASE WHEN coa.doIhaveChildren THEN
>           GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>       ELSE
>                  0.0
>       END)
>   FROM coa
>      WHERE  coa.parent_id = PassedAccountID);
>  end;
> $FunctionCode$
> LANGUAGE 'plpgsql' VOLATILE ;
>
> select 10, getchildaccountdebits(10,1)
> union
> select 11, getchildaccountdebits(11,1)
> union
> select 12, getchildaccountdebits(12,1);
>
>
> --------------------------------------------------
>
> WaGathoni wrote:
>>
>> Justin was recommending a solution to the Chart of Accounts Problem
>> posted by jamhitz:
>>
>> MQUOTE>
>> One has you chart of Accounts
>>   Create table coa (
>>      coa_id serial not null,
>>      parent_id int not null default 0,
>>      doIhaveChildren boolean default false
>>       account_name text null )
>> primary key(coa_id)
>>
>> Create Table general_ledger_transactions(
>>      transaction_id serial not null
>>      coad_id integer,
>>      accounting_period integer,
>>      debit numeric(20,10) ,
>>      credit numeric(20,10),
>>      transaction_date datestamp)
>> primary key (transaction_id)
>>
>> ...
>>
>> Create table  accounting_periods (
>>   accounting_period serial not null,
>>   start_date date,
>>   end_date date,
>>   accounting_period_Open boolean)
>>
>> </QUOTE>
>>
>> Would someone please assist me.  Why is the following function:...
>>
>>
>> CREATE OR REPLACE  FUNCTION GetChildAccountDebits(PassedAccountID
>> integer, PassedPeriodID integer) RETURNS NUMERIC AS
>> $FunctionCode$
>> DECLARE retval NUMERIC :=0.0;
>> begin
>>        SELECT Sum(gl_transactions.debit) +
>>                CASE WHEN coa.doIhaveChildren THEN
>>                        GetChildAccountDebits(coa.coa_id, PassedPeriodID )
>>                ELSE
>>                   0.0
>>                END
>>        INTO retval
>>        FROM gl_transactions, coa
>>        WHERE gl_transactions.coa_id= coa.coa_id
>>                AND coa.parent_id = PassedAccountID
>>                AND gl_transactions.period_id = PassedPeriodID;
>>
>>        RETURN retval;
>> end;
>> $FunctionCode$
>>  LANGUAGE 'plpgsql' VOLATILE ;
>>
>> ....failing with an error to the effect that that that
>> coa.doaIhaveChildren and coa.coa_id must be included in the GROUP BY
>> clause.... and what is is the recommended course of action.
>>
>> I have limited Internet access, so forgive me when I raise the same
>> question 8 days later.
>>
>> Thanks
>>
>>
>