In general, when I have to handle Ledger type data (which this problem is), I tend to hold data in 3 tables
- Master Ledger ( Product ID, Name, etc)
- Master Ledger Balances(Product ID, Fiscal_Year, Opening Balance, Net_Transaction_P1, Net_Transaction_P2, ... etc)
- Master Ledger Transactions(Product_ID, (Fiscal_Year), Date, Amount......)
I use Triggers and Stored Procedures to maintain consistency. This allows you to quickly navigate across the population of your data and drill down to the detailed transaction when required.
Careful manipulation of the Master Ledger Balances table lets you retrieve multiple different kinds of information at a single pass, ie This Year To Date Actual, Last Year To Date Actual and Budget This Year To Date. I usually create functions/SPs to do this even more rapidly.
If you have many bulk updates, it can be better to drop all indices prior to updating and then rebuilding them.
Robin St.Clair
On 18/11/2013 10:04, Hengky Liwandouw wrote:
Thanks a lot Ken,
I will try it soon.
But when the table becomes huge (how big 'huge' in postgres ?), how to optimize such command ?
I have index on all important field like date, productid, supplierid, customerid and so on
Optimization is really an important thing as i plan to keep all transaction data as long as possible.
On Nov 18, 2013, at 5:37 PM, Ken Tanzer wrote:
If the tables aren't huge, you're not concerned about optimization, and you just want to get your numbers, I think something like this would do the trick. I haven't actually tried it 'cause I didn't have easy access to your tables:
SELECT
a.product_id,
a.product_name,
b.initial_stock_sum,
c.in_out_sum,
c.in_sum,
c.out_sum
FROM
a
LEFT JOIN
(SELECT
product_id,
SUM(initial_stock) AS initial_stock_sum
FROM b
GROUP BY product_id
) b USING (product_id)
LEFT JOIN
(SELECT
product_id,
sum(CASE WHEN date < 'BEGINNING DATE' THEN in-out ELSE 0 END) AS in_out_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN in ELSE 0 END) AS in_sum,
sum(CASE WHEN date BETWEEN 'BEGINNING DATE' AND 'ENDING DATE' THEN out ELSE 0 END) AS out_sum
FROM c
GROUP BY product_id
) c USING (product_id)
WHERE a.supplier_id='XXX';
Cheers,
Ken