[SQL] group by with sum and sum till max date
This is a little hard to explain, and I'm not sure if it's possible, but here goes. This is my query: select year, month, (select number from account where account.account_id = view_account_change.account_id) as number, (select name from account where account.account_id = view_account_change.account_id) as account, sum(amount) as amount from view_account_change where view_account_change.change_date >= '2010-01-01' group by year,month, number, account order by year,month, number, account I want to make an exception for the sum so that if the account number is less than 4000, I want a sum of all transactions until the last date of the group by. the query for that would be: Select sum(amount) from view_account_change where change_date > "max date in the group" Is this possible? Thanks, Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] parsing audit table
Hi everyone, I'm a bit lazy, or actually in a bit of a crunch. I added an audit recording a few months ago, but never really used it much, but today I'm seeing a bunch of suspicious activity by one user. Does someone have any function to quickly parse this data? I followed this: http://wiki.postgresql.org/wiki/Audit_trigger - so I'm hoping someone else has something to parse that. original data: "(B04TaEsAAIG5bEEX5xBVPQ,2,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,Y,Y,919.9100,0.,0.,2011-07-05,2011-07-05)" new data: "(B04TaEsAAIG5bEEX5xBVPQ,3,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,N,Y,919.9100,919.9100,0.,,2011-08-04)" those 22 alphanumeric columns are IDs. Thanks for any help, Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] running totals with end of month line
Hi everyone, I would like to create a query that does a running total for each account, but I also want to show a 'phantom' row that gives the end of month with the last day of the month as the transaction date. Here's a sample query: SELECT n.customer_id, n.order_id, n.order_total, COALESCE(SUM(o.order_total),0) As past_order_total FROM orders AS n LEFT JOIN orders AS o ON (o.customer_id = n.customer_id AND n.order_datetime > o.order_datetime) GROUP BY n.customer_id, n.order_datetime, n.order_id, n.order_total ORDER BY n.customer_id, n.order_datetime, n.order_id; --- taken from http://bit.ly/speZzs Is there a way to have that 'phantom' row for each account? I want to result to be ordered by customer_id, account_type. More details: In my situation, I have Customers and Grain types. I want to generate a result that will show Customer, Grain Type, Daily Avg Bal, Charge Rate, discount, Charge. Maybe it's not really possible. I see it a bit hard group it properly, showing only single row per customer per grain. Many thanks, Mark
[SQL] help on a function with exception
Hi, I want to do a check on a column if other columns meet certain conditions. The program I'm working with allows to create additional columns on every 'object' - called extra data, but I have no control over the program. I want to enforce the values on this one extra data to be of type date. My idea was to do a Trigger function and cast to a date and if there's an exception, raise an error. Below is what I've tried, but it just keeps on Raising Exception. Could someone please help me? The date I enter is: 2012-10-10 which works fine if I do a: select '2012-10-10'::date Thanks --Postgres 9.0 CREATE OR REPLACE FUNCTION fnc_check_PO_extra_date() RETURNS trigger AS $BODY$ DECLARE tmp_date date; BEGIN IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN IF (NEW.data_value IS NOT NULL or new.data_value = '') and NEW.extra_id = (select extra_id from extra_data where data_type = 9 and (data_name = 'ETA' or data_name = 'Adjusted ETA')) THEN tmp_date := new.data_value::date; END IF; END IF; EXCEPTION WHEN others THEN RAISE EXCEPTION 'Invalid date on Extra Data!'; return NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE CREATE TRIGGER trg_check_PO_extra_date BEFORE INSERT OR UPDATE ON extra_values FOR EACH ROW EXECUTE PROCEDURE fnc_check_PO_extra_date(); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
