Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 16 total |
Key off transactions |
Wed, Jul 3 2013 4:56 PM | Permanent Link |
Ben Sprei CustomEDP | I have an accounts receiveable ledger with primarily two types of
transactions - Charges and Payments. All corresponding transactions bear the same invoice number. To arrive at a balance you will deduct payments from charges. If you will group by account you will arrive at an account balance if you will group by invoice number you will arrive at an invoice balance. To maintain order in the account you will mark (keyoff) all groups of items (with the same invoice number) that equal 0. To create a report with all items equal 0 or not equal 0 is simple. However I would like to keyoff all items where the GROUP total of the items = 0. How do I do this. |
Wed, Jul 3 2013 5:22 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | --
Jeff Cook Aspect Systems Ltd www.aspect.co.nz "Ben" <arrow1432@verizon.net> wrote in message news:EA6BF7C4-78B5-4E62-9853-08CA8A92ACAA@news.elevatesoft.com... >I have an accounts receiveable ledger with primarily two types of >transactions - Charges and Payments. > All corresponding transactions bear the same invoice number. > To arrive at a balance you will deduct payments from charges. If you will > group by account you will arrive > at an account balance if you will group by invoice number you will arrive > at an invoice balance. To maintain > order in the account you will mark (keyoff) all groups of items (with the > same invoice number) that equal 0. > To create a report with all items equal 0 or not equal 0 is simple. > However I would like to keyoff all items > where the GROUP total of the items = 0. How do I do this. > Ben I think what you need is the HAVING clause in your SELECT statement. I don't have an example but the online help says:- HAVING Clause The HAVING clause is used to filter any rows after they have been grouped using the GROUP BY clause, but before they are output to the result set. The HAVING clause can contain any valid boolean SQL expression. Also, aggregate functions are allowed to be used in the HAVING clause. HTH Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz |
Thu, Jul 4 2013 5:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
I'm afraid I got lost with the keyoff and the various groups and items so I'm not sure just what it is you're dealing with. I don't know if Jeff's idea of HAVING will work. My guess is that you'll need either some form of subselect which is liable to be slow or an intermediate table (temporary or in-memory) which can then be used to determine which rows to include in the query which generates the report. What you seem to be wanting to achieve is, for my experience of accounting systems, a bit unusual. It may help if you post the table structure and a small amount of data and examples of the output you want. Roy Lambert [Team Elevate] |
Thu, Jul 4 2013 11:24 AM | Permanent Link |
Ben Sprei CustomEDP | Thanks for your reply Ill give you an example in code although its wrong
but you will get the jist of the idea. Update ARLines Set KeyOff = (InvNum) where sum(TranAmtD - TranAmtP) = 0 Group by InvNum This code will probably not work. Can this be done. "Roy Lambert" <roy@lybster.me.uk> wrote in message news:62E4B2CE-EE7B-4130-84D7-ADC70ED2E815@news.elevatesoft.com... > Ben > > > I'm afraid I got lost with the keyoff and the various groups and items so > I'm not sure just what it is you're dealing with. I don't know if Jeff's > idea of HAVING will work. My guess is that you'll need either some form of > subselect which is liable to be slow or an intermediate table (temporary > or in-memory) which can then be used to determine which rows to include in > the query which generates the report. > > What you seem to be wanting to achieve is, for my experience of accounting > systems, a bit unusual. > > It may help if you post the table structure and a small amount of data and > examples of the output you want. > > Roy Lambert [Team Elevate] > |
Thu, Jul 4 2013 11:28 AM | Permanent Link |
Ben Sprei CustomEDP | Thanks for your reply. For a better understanding of what I am looking for
please see my reply to Roy. I will into the having function if I can apply that for updates to a file. Ben "Jeff Cook" <jeffc@aspect.co.nz> wrote in message news:2DDD164A-D54E-449F-A763-24E53F0CA901@news.elevatesoft.com... > -- > Jeff Cook > Aspect Systems Ltd > www.aspect.co.nz > > "Ben" <arrow1432@verizon.net> wrote in message > news:EA6BF7C4-78B5-4E62-9853-08CA8A92ACAA@news.elevatesoft.com... >>I have an accounts receiveable ledger with primarily two types of >>transactions - Charges and Payments. >> All corresponding transactions bear the same invoice number. >> To arrive at a balance you will deduct payments from charges. If you will >> group by account you will arrive >> at an account balance if you will group by invoice number you will arrive >> at an invoice balance. To maintain >> order in the account you will mark (keyoff) all groups of items (with the >> same invoice number) that equal 0. >> To create a report with all items equal 0 or not equal 0 is simple. >> However I would like to keyoff all items >> where the GROUP total of the items = 0. How do I do this. >> > > Ben > > I think what you need is the HAVING clause in your SELECT statement. I > don't have an example but the online help says:- > > HAVING Clause > > The HAVING clause is used to filter any rows after they have been grouped > using the GROUP BY clause, but before they are output to the result set. > The HAVING clause can contain any valid boolean SQL expression. Also, > aggregate functions are allowed to be used in the HAVING clause. > > HTH > > Jeff > -- > Jeff Cook > Aspect Systems Ltd > www.aspect.co.nz > > |
Thu, Jul 4 2013 1:55 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>Update ARLines >Set KeyOff = (InvNum) >where sum(TranAmtD - TranAmtP) = 0 >Group by InvNum I'm still guessing. If I'm right you want to set the value of the KeyOff column to the InvNum for all rows where some condition is met. The problem is that the condition you want to apply is for a group of rows not an individual row. It is possible but you'll have to use a subselect, or as I said an intermediate table. Without the information I asked for the best I can do is give a few examples from the newsgroups: update techniquesdocs T set onDisk = True where T.Path=(SELECT D.Path FROM diskdocs D WHERE D.Path=T.Path) UPDATE trans t SET eodflag = true WHERE EXISTS(SELECT status FROM items i WHERE i.Ref = t.Ref AND status = 30) UPDATE reservation R SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM reservation R2 WHERE R2.res_num = '1234') WHERE {no idea}.guestID = 22 AND R.res_num <> '1234' AND {no idea}.status <= 3 AND NOT EXISTS (SELECT C.Flag FROM Companies C WHERE R.companyID = C.companyID) Roy Lambert [Team Elevate] |
Fri, Jul 5 2013 5:59 AM | Permanent Link |
Adam Brett Orixa Systems | Ben
I am also not 100% sure of your desired outcome, but here is a suggestion: CREATE VIEW ClearedTransactions as SELECT IDFieldName, SUM(Charges - Payments) as NetValue FROM Ledger GROUP BY IDFieldName HAVING NetValue = 0 This creates an accessible dataset on the database which lists all the "fully paid" Transactions. You can now write a fairly simple UPDATE statement, using the ClearedTransactions VIEW in your WHERE clause: UPDATE Ledger SET(Keyoff = 'Value you choose') WHERE IDFieldName IN (SELECT IDFieldName FROM ClearedTransactions) To stop the above statement running on the whole database you might want to add an AND clause to the UPDATE statement so you only process transactions above a certain key-value ... or something. |
Sun, Jul 7 2013 11:10 PM | Permanent Link |
Ben Sprei CustomEDP | All these examples are where the condition is met by a single record. In my
cać÷Roy Lambert" <roy@lybster.me.uk> wrote in message news:FB4C2E0F-F46C-412E-A870-E8C3C7E9A380@news.elevatesoft.com... > Ben > >>Update ARLines >>Set KeyOff = (InvNum) >>where sum(TranAmtD - TranAmtP) = 0 >>Group by InvNum > > I'm still guessing. If I'm right you want to set the value of the KeyOff > column to the InvNum for all rows where some condition is met. The problem > is that the condition you want to apply is for a group of rows not an > individual row. It is possible but you'll have to use a subselect, or as I > said an intermediate table. > > Without the information I asked for the best I can do is give a few > examples from the newsgroups: > > update techniquesdocs T set onDisk = True > where T.Path=(SELECT D.Path FROM diskdocs D WHERE D.Path=T.Path) > > UPDATE trans t > SET > eodflag = true > WHERE > EXISTS(SELECT status FROM items i WHERE i.Ref = t.Ref AND status = 30) > > UPDATE reservation R > SET (companyID, firstname) = (SELECT R2.companyID, R2.firstname FROM > reservation R2 WHERE R2.res_num = '1234') > WHERE > {no idea}.guestID = 22 > AND > R.res_num <> '1234' > AND > {no idea}.status <= 3 > AND > NOT EXISTS (SELECT C.Flag FROM Companies C WHERE R.companyID = > C.companyID) > > Roy Lambert [Team Elevate] |
Mon, Jul 8 2013 3:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>All these examples are where the condition is met by a single record. In my >ca Your post stopped at this point. Roy Lambert [Team Elevate] |
Mon, Jul 8 2013 10:55 AM | Permanent Link |
Ben Sprei CustomEDP | sorry about that
All these examples are where the condition is met by a single record. In my case the condition is contingent on the sum of at least tow records and perhaps more. Ben "Roy Lambert" <roy@lybster.me.uk> wrote in message news:42F054FC-3719-484B-ADDB-DBE6C1BC0BB5@news.elevatesoft.com... > Ben > > >>All these examples are where the condition is met by a single record. In >>my >>ca > > Your post stopped at this point. > > Roy Lambert [Team Elevate] |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, May 15, 2024 at 08:40 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |