Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Runsum and Questions on resetting |
Mon, Jun 16 2008 1:56 AM | Permanent Link |
"Adam H." | Hi,
I'm trying to perform a query that will give me a running sum of a list of records. (Think about a bank account, with the sums added and subtracted, with me wanting to create a balance field at the end). I've found I can do this via Runsum. However, in my query I would like to have multiple bank accounts showing, with the RunSum resetting whenever the account field changes. ie, something like: Select Account, DateTime, Amount, Runsum(Amount) as Balance From Transactions Group by Account, Order by Account, Datetime. ..... but this doesn't seem to be working with what I've tried. Is it possible to reset the runsum counter, or is this not possible to do with SQL, forcing me to perform a seperate query per account? Thanks & Regards Adam. |
Mon, Jun 16 2008 5:50 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< .... but this doesn't seem to be working with what I've tried. >> Get rid of the DateTime column in the ORDER BY. It doesn't make any sense being there and is most likely screwing up the RUNSUM() calculation. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Jun 16 2008 6:17 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Get rid of the DateTime column in the ORDER BY. It doesn't make any sense >being there and is most likely screwing up the RUNSUM() calculation. From Adam's post <<Think about a bank account, with the sums added and subtracted, with me wanting to create a balance field at the end>> DateTime does make sense for a bank statement. Roy Lambert |
Mon, Jun 16 2008 9:30 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< DateTime does make sense for a bank statement. >> Yes, but not when grouping by account and wanting a balance for the account. -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Jun 16 2008 11:57 AM | Permanent Link |
"Robert" | "Adam H." <ahairsub4@pleaseREMOVEme.jvxp.com> wrote in message news:8C2FABE3-B9ED-45A4-AFD0-C2D978A15A55@news.elevatesoft.com... > Hi, > > I'm trying to perform a query that will give me a running sum of a list of > records. (Think about a bank account, with the sums added and subtracted, > with me wanting to create a balance field at the end). > > I've found I can do this via Runsum. > Hmmm, I don't think so. runsum will sum the groups. You want (if I understand correctly) a running total on individual records, restarting as you get a break in account number. This SQL adapted from a script I have might do the trick for you. select bankID, trdate, tramount, cast(0 as decimal) rd into memory\temp from rsum order by bankid, trdate; optimize table memory\temp nobackup; alter table memory\temp add a autoinc; update memory\temp set rd = tramount where a = 1; update memory\temp t1 set rd = if((t2.bankid = t1.bankid) or (t2.bankid = null) then t2.rd + t1.tramount else t1.tramount) from memory\temp t1 join memory\temp t2 on t2.a + 1 = t1.a; select * from memory\temp; If what you want is a total by day instead of individual records, change the first select to group by date, bankID, the rest should work the same. If the tables are large, add an index on field a before the updates. Robert |
Tue, Jun 17 2008 7:03 PM | Permanent Link |
"Adam H." | Thanks Roy Tim and Robert for your replies!
Robert, this looks exactily like what I might need. I'll try adapting it and see how it goes. Best Regards Adam. > select bankID, trdate, tramount, cast(0 as decimal) rd > into memory\temp > from rsum > order by bankid, trdate; > optimize table memory\temp nobackup; > alter table memory\temp add a autoinc; > update memory\temp set rd = tramount where a = 1; > update memory\temp t1 set rd = if((t2.bankid = t1.bankid) or (t2.bankid = > null) then t2.rd + t1.tramount else t1.tramount) > from memory\temp t1 > join memory\temp t2 on t2.a + 1 = t1.a; > select * from memory\temp; |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |