B4J Question How to aggregate self referencing db records easily?

[Mashiane]

Hi there

I need some source code help please. I need to aggregate some self referencing records. Each record in my database has a parent, but this is a flat structure. The genesis records will have a parentid=0, and their children have parentid=genesisid etc etc

For example,

id=1,wbs=1,parentid=0,startdate=2018-03-10,finishdate=2018-03-31,progress=10,budget=20
id=2,wbs=2,parentid=0,startdate=2018-03-10,finishdate=2018-03-31,progress=10,budget=30
id=3,wbs=3,parentid=0,startdate=2018-01-11,finishdate=2018-03-31,progress=10,budget=100
id=4,wbs=3.1,parentid=3,startdate=2018-03-10,finishdate=2018-03-31,progress=2,budget=200
id=5,wbs=2.2,parentid=2,startdate=2018-02-01,finishdate=2018-03-31,progress=1,budget=500
id=6,wbs=5.1,parentid=5,startdate=2018-02-01,finishdate=2018-03-31,progress=0,budget=400

Now what I want to do is

• Sum: sum the budgets of children per parents and put the sum as the budget amount for parents
• Min & Max: find the minimum and maximum dates for the children and put this for parents.
• Average: find the average progress for children and put on for parents

These are just are a few example calculations.

Now, there can be a lot of parents and a lot of children here in the same db table. I need some simple methodology and perhaps source to do this please.

Thanks a lot!

 

[OliverA]

source code help please

1) Are you asking for SQL statements?
2) For Min and Max: Are you looking for the minimum start date and the maximum finish date or do you need minimum and maximum start date and minimum and maximum end date?

 

[Mashiane]

1) Are you asking for SQL statements?
2) For Min and Max: Are you looking for the minimum start date and the maximum finish date or do you need minimum and maximum start date and minimum and maximum end date?

1. I'm not sure if this can be done with SQL statements esp with SQLite I'm using. I could be wrong. Code would do though.
2. For start dates it should be the min date(start date field) and finish date the max date (finish date field) from all children of parent X.

Thanks.

 

[OliverA]

I'm not sure if this can be done with SQL statements esp with SQLite I'm using

Note: I named the table name with the records listed in post#1 somebudget. I'm using SQLite.

Sum: select parentid, sum(budget) from somebudget group by parentid
Min & Max: select parentid, min(startdate), max(startdate) from somebudget group by parentid
Average: select parentid, avg(progress) from somebudget group by parentid

 

[Mashiane]

Ok, this is getting somewhere and thanks for trying to help. I sadly need the missing part, the updates to make the updates work in tree-like fashion.

As an example:

Level 4 children will update Level 3 children and so forth and so forth

1.1.1.1 > 1.1.1

1.1.1 > 1.1

1.1 > 1

 

[OliverA]

You may need to fill me in a little more, since I'm not quite sure what you are trying to get at. I may need more coffee or my brain is not quite working today.

 

[OliverA]

Hold on, does parentid = 0 mean no parent? Where would one store the sum of the children's budget?

 

[Mashiane]

Hold on, does parentid = 0 mean no parent? Where would one store the sum of the children's budget?

yes, parent 0 means no parent, thus, if an item with parent 0 has no children, no calculation is applicable however if there are kids, the sum and average of all those kids should be the result.

 

[Mashiane]

Think of it as a tree. The children will sum / average to their parent upwards

 

[OliverA]

0 has no children, no calculation is applicable

Ok

the sum and average of all those kids should be the result.

Ok, but where is that result stored? If it stored in the "budget" column of the parent, then the parent would have no budget themselves.

 

[OliverA]

The children will sum / average to their parent upwards

So if a great-grandparent has 5 kids, which have 5 kids, which once more have 5 kids, the great-grandparent's budget consists of all 125 members of the family?

 

[Mashiane]

Exactly, but now not only for the great-grandparent, but for the kids too in that fashion. This needs to be some form of a recursive calculation until the great-grandparent(s) are calculated.

So great-grand parents without kids will keep their figures e.g. id=7 above but because ggp id=1 has kids, the kids needs to add their money from level 4 until level 1.

1.1.1.1 will sum up/average to 1.1.1, then 1.1.2 and 1.1.1 will be summed/averaged to 1.1. 1.2.1 summed/averaged to 1.2, then 1.1 and 1.2 summed/averaged to 1. I hope this is clear, forgive me for lack of clarity. Each child gives to the parent and each parent gives to their parent what they got from the children. What the parents have should always be the sum/average of what the kids gave. etc etc.

 

[OliverA]

1.1.1.1 will sum up/average to 1.1.1, then 1.1.2 and 1.1.1 will be summed/averaged to 1.1.

1.1.1.1 sum is 5. Is 1.1.2 and 1.1.1 sum 10 (6+4) or 15 (6+4+5)?

 

[Mashiane]

Let me clarify... We start from the bottom up. If a node has children, we disregard what that node has and get the inheritance from the kids. If the node has no children, they keep what they have.

L4 --- 1.1.1.1 gives to parent 1.1.1 5 bucks, so 1.1.1 has 5 bucks ONLY now from the child.
L3 --- 1.1.2 has 6 bucks and no kids, so 1.1.1 and 1.1.2 must give parent 1.1 (5+6) 11 bucks, so 1.1. now has 11 bucks ONLY
L3 --- 1.2.1 has 8 bucks and gives parent 1.2, that, so 1.2 has 8 bucks
L2 --- 1.2 and 1.1 now must give parent 1 (8+11) = 19 bucks
L1 --- parent 1 has 19 bucks and parent 7 his 7 bucks as he has no kids

 

[keirS]

Summing up children to parents and grandparents is easy to do. You do it by creating triggers in the database.

 

[Mashiane]

Summing up children to parents and grandparents is easy to do. You do it by creating triggers in the database.

Dont you want to provide me an example particular to my case study @keirS please.

 

[keirS]

Create Table:

Pragma recursive_triggers = true;

CREATE TABLE [moneysum] (
  [ID] INTEGER NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT ROLLBACK,
  [PARENTID] INTEGER NOT NULL,
  [DESC] VARCHAR(30) NOT NULL ON CONFLICT ROLLBACK,
  [MONEY] CURRENCY(10, 2) NOT NULL ON CONFLICT ROLLBACK,
  [CHILDMONEY] CURRENCY(10, 2));
CREATE INDEX [PARENTID] ON [moneysum] ([PARENTID]);
CREATE TRIGGER [INSERT_CHILDMONEY]
AFTER INSERT
ON [moneysum]
BEGIN
  UPDATE moneysum SET CHILDMONEY = CHILDMONEY + new.MONEY WHERE ID = new.PARENTID;
END;
CREATE TRIGGER [UPDATE_CHILDMONEY]
AFTER UPDATE
ON [moneysum]
BEGIN
  UPDATE moneysum SET CHILDMONEY = CHILDMONEY - (old.CHILDMONEY + old.money) WHERE ID = old.PARENTID and old.PARENTID > 0;
  UPDATE moneysum SET CHILDMONEY = CHILDMONEY + (new.CHILDMONEY + new.money) WHERE ID = old.PARENTID and old.PARENTID > 0;
END;

Insert:

insert into moneysum (id,parentid,money,desc,childmoney) values(1,0,1,"1",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(2,1,2,"2",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(3,1,3,"3",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(4,2,4,"4",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(5,4,5,"5",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(6,2,6,"6",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(7,0,7,"7",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(8,3,8,"8",0);

Gives:

 

[Mashiane]

Wow, this is cool and thanks and you said it was easy. I have never seen such SQL statements before!!!

We however have 1 last issue to make this work as per business rules. If a parent has money, their money is irrelevant if they got money from the kids. The kids money receives preference and the parent's money falls away.

Thus child's 4 parent is 2, this child has 4 'money' pounds therefore the 4 pounds must be removed from the 15 'childmoney' pounds of the parent making the total childmoney for the parent with id=2, 11 pounds.

Same goes with id=1, the childmoney should be 19 pounds, made up of 11 + 8 pounds or rather 28-2-3-4 (19)

What you have done here is magical, thank you so much. If we can figure out that last part i think implementing this for averages and min & max dates should not be an issue.

 

[keirS]

Create Table:

Pragma recursive_triggers = true;
CREATE TABLE [moneysum] (
  [ID] INTEGER NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT ROLLBACK, 
  [PARENTID] INTEGER NOT NULL, 
  [DESC] VARCHAR(30) NOT NULL ON CONFLICT ROLLBACK, 
  [MONEY] CURRENCY(10, 2) NOT NULL ON CONFLICT ROLLBACK, 
  [CHILDMONEY] CURRENCY(10, 2));
CREATE INDEX [PARENTID] ON [moneysum] ([PARENTID]);
CREATE TRIGGER [INSERT_CHILDMONEY]
AFTER INSERT
ON [moneysum]
BEGIN
  UPDATE moneysum SET CHILDMONEY = CASE WHEN CHILDMONEY = 0 THEN  CHILDMONEY + new.MONEY  ELSE CHILDMONEY END WHERE ID = new.PARENTID;
END; 
CREATE TRIGGER [UPDATE_CHILDMONEY]
AFTER UPDATE
ON [moneysum]
BEGIN 
  UPDATE moneysum SET CHILDMONEY = CASE WHEN CHILDMONEY = 0 THEN (CHILDMONEY - (old.CHILDMONEY + old.money)) + 
  (new.CHILDMONEY + new.money)  ELSE  (CHILDMONEY - old.CHILDMONEY)  + new.CHILDMONEY END  WHERE ID = old.PARENTID and old.PARENTID > 0; 
END;

Update:

insert into moneysum (id,parentid,money,desc,childmoney) values(1,0,1,"1",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(2,1,2,"2",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(3,1,3,"3",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(4,2,4,"4",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(5,4,5,"5",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(6,2,6,"6",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(7,0,7,"7",0);
insert into moneysum (id,parentid,money,desc,childmoney) values(8,3,8,"8",0);

Gives:

 

[Mashiane]

We are almost there, kids 3 & 2 have parent 1, but their total is 17 pounds and not 19 pounds. kid 2 is supposed to be 11 pounds.

Thanks a million for this, I have learned a few important things I didnt know. At some stage I will have to do same in MySQL and MSSQL databases.