Re: Group BY and Chart of Accounts

Works like a charm. Thank you very much Justin.

On Thu, Oct 30, 2008 at 3:49 AM, justin <justin(at)emproshunts(dot)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
>>
>>
>