OK so I can manage to get some decent output using a query on the following (simplified data model):
I can run the following query:
SET @runtot:=0;
SELECT `MonthTot`.`Acc` AS `Account`,
`MonthTot`.`Curr` AS `Curr`,
`MonthTot`.`TXNYEAR` AS `Year`,
`MonthTot`.`TXNMTH` AS `Month`,
CONCAT(`MonthTot`.`TXNYEAR`, '/', `MonthTot`.`TXNMTH`) AS `Period`,
`MonthTot`.`DEBITS` AS `Money Out`,
`MonthTot`.`CREDITS` AS `Money In`,
@runtot:=@runtot+(`MonthTot`.`CREDITS`-`MonthTot`.`DEBITS`) AS `Balance`
FROM
(SELECT
`CONTACT_BANK`.`AccountNickname` AS `Acc`,
`REF_GENERAL_CURRENCY`.`ISO3` AS `Curr`,
YEAR(`FCB`.`DateTransaction`) AS `TXNYEAR`,
MONTH(`FCB`.`DateTransaction`) AS `TXNMTH`,
SUM(`FCB`.`AmtDebit`) AS `DEBITS`,
SUM(`FCB`.`AmtCredit`) AS `CREDITS`
FROM `REF_GENERAL_CURRENCY`
INNER JOIN `CONTACT_BANK` ON `REF_GENERAL_CURRENCY`.`ID` = `CONTACT_BANK`.`ps_Currency_RID`
INNER JOIN `CONTACT_BANK_STATEMENT` ON `CONTACT_BANK`.`ID` = `CONTACT_BANK_STATEMENT`.`ob_Bank_RID`
INNER JOIN `FINANCE_CASHBOOK` `FCB` ON `FCB`.`ob_Statement_RID` = `CONTACT_BANK_STATEMENT`.`ID`
WHERE `CONTACT_BANK`.`ID`=22712
GROUP BY `TXNYEAR` ASC, `TXNMTH` ASC, `ACC`, `CURR`
) AS `MonthTot`;
Which gives me this output, which I am very happy with and has the benefit of not having expensive SUM calculations being made (I need to repeat this for limited dates, and then for weekly and monthly - which would be very costly to do in AIM)
Account, Curr, Year, Month, Period, Money Out, Money In, Balance
"EURO Account", EUR, 2018, 1, 2018/1, 20, 500, 480
"EURO Account", EUR, 2018, 3, 2018/3, 120, 200, 560
"EURO Account", EUR, 2018, 5, 2018/5 50, 0, 510
"EURO Account", EUR, 2018, 6, 2018/6, 0, 100, 610
"EURO Account", EUR, 2018, 7, 2018/7, 0, 400, 1010
"EURO Account", EUR, 2018, 8, 2018/8, 250, 0, 760
Now my challenge arises.
I have to rewrite this query to work as an SQL view (can't have variables in a view) - and this creates an issue in using the View and an AIM BO that it has to refer to itself for (external objects require a connection string - so I need to create a connection string / external object in my Dev environment, another in my Test environment and then another in my production environment - I am using on BSV as Dev/Test and migrate to another BSV as my production).
This is cumbersome.
Alternative - I use a Stored Procedure instead. The limit that I have come up against with this approach is, how to return multiple results via SQL which doesn't have an array datatype.
Does anyone have any suggestions on the best approach to take in AIM?
