Thread: Running Totals and other stuff....
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; charset=3Dwindows-1= 252"> Running Totals and other stuff.... Humpfff...Last night I tried posting this and found that<= BR> dynamic IP's are now prevented from posting to the list... Did I miss the announcement? ************************************ Hi, I keep all of my financial data in Postgresql ( 7.4.2). My "Check" register records deposits, withdrawals (as amount) , d= ate, category and other stuff. The following sorta works... SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >= =3D thedate ) AS total FROM checks x ORDER BY thedate,oid ; The problem is that all transactions on the same date get the total of all<= BR> transactions for that date, so the resulting table is only "sorta"= ; a running total. If I change the rascal to look like SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >=3D oi= d ) AS total FROM checks x ORDER BY thedate,oid ; I get the right results, but this relies on the fact the oids in the check table are currently *sorted* (when the table is sorted by thedate) at least it appears that way via a very brief inspection.... I suspect if I deleted a record and added a record the oids would get out of sequence. Is there a slick way to tell if a column (say the oids column) is in "= sorted" order when the table is sorted by date? Assuming the oids get out of wack with respect to the date, is it possible<= BR> to easily construct a table of the checks sorted by date and then "glu= e on" a column of ascending integers so the running total sql statement will function properly? Jerry
"Levan, Jerry" <Jerry.Levan@EKU.EDU> wrote: [HTML garbage elided] Nothing left. Next time, please post in straight text, leaving the HTML and other "enhancements" out. You're more likely to get help on a mailing list that way. Jim
Just wanted to see if there was a much better way of doing this. We have a web application where user data needs to be separated as much as possible and where access needs to be controlled and restricted on several levels. Most data coming into the system is from anonymous sources so the application itself needs restricted insert/update rights, and our clients need pretty much full access to the data but we still want to funnel all access through predefined views and functions.
The best thing I have come up with so far is to create a user and schema for each client. Client's accessing the database provide thier username and password, in addition to our own pre authentication and our code does all database access through views and functions. Now for new transactions that come in from the anonymous sources, I would create a secondary user for every schema that has limited permissions for inserting new data.
We also have a Kerberos infrastructure and could use that for database authentication.
The number of schema's will be fairly large (in the thousands depending on how many users per database). This plan I think offers the best separation of data and permissions without using completely separate databases. Any thoughts?
Chris
Levan, Jerry wrote: > Humpfff...Last night I tried posting this and found that > dynamic IP's are now prevented from posting to the list... > > Did I miss the announcement? Sounds odd - or do you mean your mailserver has a dynamic IP? > I get the right results, but this relies on the fact the oids in the > check table are currently *sorted* (when the table is sorted by thedate) > at least it appears that way via > a very brief inspection.... I suspect if I deleted a record and added > a record the oids would get out of sequence. > > Is there a slick way to tell if a column (say the oids column) is in "sorted" > order when the table is sorted by date? No, and it isn't recommended that you use the oid column. > Assuming the oids get out of wack with respect to the date, is it possible > to easily construct a table of the checks sorted by date and then "glue on" > a column of ascending integers so the running total sql statement will > function properly? What is your key? Why not just sort by cheque_date, <key>? -- Richard Huxton Archonet Ltd
Take 2. To allow for inserted OID's not messing with the running total,
you need to only cheque OID where the date is equal. So you're SQL is:
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >
thedate OR (x.thedate = thedate AND x.oid >= oid ) AS total
FROM checks x
ORDER BY thedate,oid ;
Whew! I'm happy now :-)
Alan Graham
On Tue, 2004-06-01 at 20:40, Levan, Jerry wrote:
> Humpfff...Last night I tried posting this and found that
> dynamic IP's are now prevented from posting to the list...
>
> Did I miss the announcement?
>
> ************************************
>
> Hi,
>
> I keep all of my financial data in Postgresql ( 7.4.2).
> My "Check" register records deposits, withdrawals (as amount) , date,
> category and other stuff.
>
> The following sorta works...
>
> SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >=
> thedate ) AS total
> FROM checks x
> ORDER BY thedate,oid ;
>
> The problem is that all transactions on the same date get the total of
> all
> transactions for that date, so the resulting table is only "sorta" a
> running total.
>
> If I change the rascal to look like
> SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid )
> AS total
> FROM checks x
> ORDER BY thedate,oid ;
>
> I get the right results, but this relies on the fact the oids in the
> check table are currently *sorted* (when the table is sorted by
> thedate)
> at least it appears that way via
> a very brief inspection.... I suspect if I deleted a record and added
> a record the oids would get out of sequence.
>
> Is there a slick way to tell if a column (say the oids column) is in
> "sorted"
> order when the table is sorted by date?
>
> Assuming the oids get out of wack with respect to the date, is it
> possible
> to easily construct a table of the checks sorted by date and then
> "glue on"
> a column of ascending integers so the running total sql statement will
> function properly?
>
> Jerry
>
--
Alan Graham <alan.graham@infonetsystems.com.au>
Attachment
Jerry LeVan wrote: > Yes, my mail server has a dynamic IP it is running > on my Mac... > > I am retired and the University let me keep my mailbox, > I tried using the Universities Exchange Server to send > the message you responded to below, much to my chagrin, > the server wraps *all* outgoing mail in html. My sympathies, sir. > My check table does not have a key, ( it does not > have a lot of entries ~3200 in four years). Whether 3200, 32 or 0 rows you should have a key... > I generate the data in the table by exporting transactions > from Quicken and parse the QIF file to a csv file and then > import via COPY to the database. > > As I noted just using the date field does not quite work > because there are multiple transactions on the same date. And without a key there's no way to solve the issue, since there's no way to distinguish one row from another. > Is there a way to add an increasing sequence of unique > integers as a temporary column? To cut a long story short - you need a primary key. If your table has no suitable candidates, you will have to add one. (Can you not use cheque-number?) If you add your own, I would recommend adding a column of type SERIAL. This is basically an integer with an attached sequence generator, so if no value is supplied it defaults to the next number supplied by the sequence. You can continue to use COPY to import new data, just don't mention the new column in the list to insert and it will be given the default. You can then order by date, <key> and that will let you calculate your running total. -- Richard Huxton Archonet Ltd
Oh bugger, that doesn't work. I tested it with a primary key (cheque number), that worked, then I tested the AND oid, and managed to totally confuse myself. Apologies Jerry. Adding a cheque number primary key would work tho' Alan Graham On Tue, 2004-06-01 at 20:40, Levan, Jerry wrote: > Humpfff...Last night I tried posting this and found that > dynamic IP's are now prevented from posting to the list... > > Did I miss the announcement? > > ************************************ > > Hi, > > I keep all of my financial data in Postgresql ( 7.4.2). > My "Check" register records deposits, withdrawals (as amount) , date, > category and other stuff. > > The following sorta works... > > SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >= > thedate ) AS total > FROM checks x > ORDER BY thedate,oid ; > > The problem is that all transactions on the same date get the total of > all > transactions for that date, so the resulting table is only "sorta" a > running total. > > If I change the rascal to look like > SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid ) > AS total > FROM checks x > ORDER BY thedate,oid ; > > I get the right results, but this relies on the fact the oids in the > check table are currently *sorted* (when the table is sorted by > thedate) > at least it appears that way via > a very brief inspection.... I suspect if I deleted a record and added > a record the oids would get out of sequence. > > Is there a slick way to tell if a column (say the oids column) is in > "sorted" > order when the table is sorted by date? > > Assuming the oids get out of wack with respect to the date, is it > possible > to easily construct a table of the checks sorted by date and then > "glue on" > a column of ascending integers so the running total sql statement will > function properly? > > Jerry > -- Alan Graham <alan.graham@infonetsystems.com.au>
Attachment
The following is problem domain specific...
It appears you're actually after a primary key (cheque number springs to
mind), and are using OID to approximate this. In the absence of such a
key, I assume you're not interested in the sequence for a given date,
ie, two transactions for one day, for $5 and $10, you don't care if the
total is $5, $15, or $10, $15. If that's the case, then using OID is
fine within a day. So your SQL becomes
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >=
thedate AND x.oid >= oid ) AS total
FROM checks x
ORDER BY thedate,oid ;
This keeps your totals unique, but will, as you say, change the sequence
within a day where cheques are inserted.
Regards
Alan Graham
BTW, troll follows. Please disregard.
Diatribes, rants, political statements are of no interest to most db
users. HTML mail is used all over the place. Deal with it. Keep posts
on topic.
Responses will go to /dev/null
Oh, and I top posted too.. :-0
On Tue, 2004-06-01 at 20:40, Levan, Jerry wrote:
> Humpfff...Last night I tried posting this and found that
> dynamic IP's are now prevented from posting to the list...
>
> Did I miss the announcement?
>
> ************************************
>
> Hi,
>
> I keep all of my financial data in Postgresql ( 7.4.2).
> My "Check" register records deposits, withdrawals (as amount) , date,
> category and other stuff.
>
> The following sorta works...
>
> SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >=
> thedate ) AS total
> FROM checks x
> ORDER BY thedate,oid ;
>
> The problem is that all transactions on the same date get the total of
> all
> transactions for that date, so the resulting table is only "sorta" a
> running total.
>
> If I change the rascal to look like
> SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid )
> AS total
> FROM checks x
> ORDER BY thedate,oid ;
>
> I get the right results, but this relies on the fact the oids in the
> check table are currently *sorted* (when the table is sorted by
> thedate)
> at least it appears that way via
> a very brief inspection.... I suspect if I deleted a record and added
> a record the oids would get out of sequence.
>
> Is there a slick way to tell if a column (say the oids column) is in
> "sorted"
> order when the table is sorted by date?
>
> Assuming the oids get out of wack with respect to the date, is it
> possible
> to easily construct a table of the checks sorted by date and then
> "glue on"
> a column of ascending integers so the running total sql statement will
> function properly?
>
> Jerry
>
--
Alan Graham <alan.graham@infonetsystems.com.au>
Attachment
> Adding a cheque number primary key would work tho' Depending on the specifics of the application, check number may not be a 'unique' field. Automatic bank checks come to mind, on my monthy statments they tend to always have the same check number or none at all. In this case I'd use a serial column. The best long term solution, IMHO, would be to change postgres so that it has a unique system column for each record, like Oracle does. -- Mike Nolan
Mike Nolan wrote: >>Adding a cheque number primary key would work tho' > > > Depending on the specifics of the application, check number may not > be a 'unique' field. Automatic bank checks come to mind, on my > monthy statments they tend to always have the same check number or none > at all. > > In this case I'd use a serial column. > > The best long term solution, IMHO, would be to change postgres so that > it has a unique system column for each record, like Oracle does. Eh? The expense of having a 64-bit column with index and unique constraint on every table, whether I need it or not? [soapbox = on] The problem was a simple one. The original poster wanted to distinguish between rows where there was no key. Without a key this is impossible. The table should not have been created without a primary key defined, and in the absence of adequate information to make a design decision the best that can be offered is an auto-generated sequence. [soapbox = off] -- Richard Huxton Archonet Ltd
> > The best long term solution, IMHO, would be to change postgres so that > > it has a unique system column for each record, like Oracle does. Well incidentally, Oracle doesn't really either. If you use rowid for these things then you're in for some surprises when you make any significant DDL changes. At the very least it means dumping and reloading the data in a new database will destroy all your relationships. But the only reason Oracle even has something like rowid is a quirk of its implementation. Postgres is implemented differently and in that implementation there simply is no such identifier available. -- greg