determining order from a list without using date field

UDA
Enthusiast

determining order from a list without using date field

Hi all

I was wondering if someone could give advise as to whether the following can be done.

I have a table with opening_balance, movement_type, movement_quantity and closing_balance fields. A client can have movements up and down. Please see example below (using a single clientid to keep it simple but there are hundreds of clients)

clientid = 1
opening = 0
movement_type = +
movement = 1000
closing = 1000
-------------
clientid = 1
opening = 1000
movement_type = -
movement = 200
closing = 800
-------------
clientid = 1
opening = 800
movement_type = +
movement = 500
closing = 1300
-------------
clientid = 1
opening = 1300
movement_type = +
movement = 1700
closing = 3000
-------------
clientid = 1
opening = 3000
movement_type = -
movement = 1700
closing = 1300

The tricky part is I want to know what the client's final closing balance is in the data. In this example I have listed the order, but imagine I hadn't. I have no date fields to work with. However the opening and closing balances between entries will always match up (i.e. The closing balance for an entry will ALWAYS equal the opening balance of the next entry. The opening balance of an entry will ALWAYS equal the closing balance of the previous entry. The first entry for a client always begins with an opening amount of ZERO.
I need some way to compare the opening and closing balances of each entry so that I can find the order. Then I should be able to determine the client's final transaction which in this example would give them a final balance of 1300.

Can this be done?

Thanks in advance everyone.

Chris.
3 REPLIES
Enthusiast

Re: determining order from a list without using date field

Chris,
I have one question.

You mentioned
the opening and closing balances between entries will always match up (i.e. The closing balance for an entry will ALWAYS equal the opening balance of the next entry. The opening balance of an entry will ALWAYS equal the closing balance of the previous entry. The first entry for a client always begins with an opening amount of ZERO.

What will happen when a closing balance matches more than one opening balance. In your example the closing balance comes to 1300 twice. So when i try to match Closing balance with Opening balance then i will see two possible "next entry" since there are 2 rows where closing balance are matching with opening balance.

As a general phenomenon in series of transactions how will we determine the next entry when a closing balance matches multiple opening balance.

I have solution but depending your answer i may modify the code a bit..

Cheers,
Saurabh
Enthusiast

Re: determining order from a list without using date field

Hi Novice.

Thanks for the reply.

A closing balance can most certainly equal more than one opening balance.

In a real world case a person's balance can go up and down a hundred times
0+1000=1000
1000-100=900
900+200=1100
1100-200=900
900+200=1100
1100-200=900
900+1300=2200
2200+90000=92200
92200-90000=2200
etc

That's the problem I have. Multiple ups and downs. When manually reviewed by a person, one can see the transactional history, but automating it is proving difficult. Let me know if more information is needed.
Enthusiast

Re: determining order from a list without using date field

i think the below query will solve your purpose

sel
sum(
case when movement_type ='+' then movement_quantity
else -movement_quantity
end
) ,
clientid
from

group by 2 order by 2 asc;