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.

--
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

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]

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]
>

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
>
>

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]

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.

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]

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]

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]