Search Postgresql Archives

Using generate_series to create a unique ID in a query?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
begin:vcard
fn:Sarah Dougherty
n:Dougherty;Sarah
org:Downtown Emergency Service Center;Information Services
email;internet:sdougherty@xxxxxxxx
title:Data/Reports Specialist
x-mozilla-html:TRUE
version:2.1
end:vcard

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux