Hello,
I am trying to create a view that will contain a generated sequence
(unique ID), and am running into problems doing so.
For some context, I am trying to create a report that provides a list of
client charges and payments and a "running balance" after each
transaction. Because we often have multiple charges and/or payments on
the same day, we can't use the transaction date to calculate this
balance. Instead, I want to calculate our running balance by assigning
a transaction ID to each transaction a d then having the query sum up
transaction amounts for all transactions with an equal or lower ID.
I can use generate_series to produce a set of IDs, but can't get it to
join properly to the rest of my query. For example, if I had 10 rows in
my query, I would get a series of 1 to 10, but would then get 100 rows
(10x10) in my result. Ultimately the results of this query are going to
be used as a view, so I'd like to avoid creating a temp table, sequence,
etc. Does anyone know how to use generate_series in this manner, or know
of some other way I can go about this? Thanks in advance!
To recap with an example, the query below works fine, but how do I add a
series to it?
SELECT * FROM (
SELECT
client_id,
effective_date AS transaction_date,
amount AS charge_amount,
0 AS payment_amount
FROM charge
UNION
SELECT
client_id,
payment_date AS transaction_date,
0 as charge_amount,
amount AS payment_amount
FROM payment
) AS tmp
ORDER BY
transaction_date,
charge_amount<>0 /* order charges before payments */
Thanks,
Sarah Dougherty
