Hi All,

I'm just wondering the most efficient way to get a balance from the
fields following in a table.

partnerFromID
partnerToID
quantity

The partnerFromID field and the partnerToID are dictating the quantity
moved from one partner to another. So what I'm trying to achieve is a
query that then can list all the partnerID's along with the balance of
credits they end up with.

I hope that makes sense,

Thanks for the time!

"Phil Read" <phil@vizualweb.com> wrote in message
news:DB172D11-C09E-4CA3-B930-FEBFDADEF04A@news.elevatesoft.com...
> Hi All,
>
> I'm just wondering the most efficient way to get a balance from the fields
> following in a table.
>
> partnerFromID
> partnerToID
> quantity
>
> The partnerFromID field and the partnerToID are dictating the quantity
> moved from one partner to another. So what I'm trying to achieve is a
> query that then can list all the partnerID's along with the balance of
> credits they end up with.
>

If I understand correctly and you just want the total credits for each
partner, you don't need to fromID field. Just a

SELECT partnerToID, sum(quantity) AS credits FROM table GROUP BY partnerToID

should do it.

Robert

> I hope that makes sense,
>
> Thanks for the time!

Robert

>> The partnerFromID field and the partnerToID are dictating the quantity
>> moved from one partner to another. So what I'm trying to achieve is a
>> query that then can list all the partnerID's along with the balance of
>> credits they end up with.
>>
>
>If I understand correctly and you just want the total credits for each
>partner, you don't need to fromID field. Just a
>
>SELECT partnerToID, sum(quantity) AS credits FROM table GROUP BY partnerToID

That was my first thought and I was typing it up when I thought "he wants the NET BALANCE for each partnerid" eg

PartnerFromID    PartnetToID    Quantity
1                        2                    1
1                        3                    5
2                        3                    1
3                        1                    2

would give (assuming everyone starts from 0)

PartnerID    Quantity
1                -4                
2                0
3                4

at which point I realised I was out of my depth and gave up.

I'm not even sure its doable which is why I thought "I'll let Robert answer it - he's good at this sort of thing".

So even if its not what he wants - what's the answer? <vbg>

Roy Lambert

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:16BBE871-FD54-42D2-8F00-DE4A867A00C5@news.elevatesoft.com...
>
> So even if its not what he wants - what's the answer? <vbg>
>

Something like this would do it

select quantity, partnertoid
into memory\temp
from atable
union all
select (0 - quantity), partnerfromid
from atable;
select sum(quantity) net, partnertoid partner
from memory\temp
group by 2;

Robert

Robert


If you get any better you'll be able to challenge Ole <vbg>

Roy Lambert

Robert, I have to say "YoU Da MaN" !

Wow now that's efficient and clean as I could ever have hoped.

Thank Robert and Roy for your input!

Phil.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:56513890-A29E-4CC7-8C66-E186BB743B44@news.elevatesoft.com...
> Robert
>
>
> If you get any better you'll be able to challenge Ole <vbg>
>

Nah, Ole never forgave me for calling him an amateur. I understand he joined
a monastery somewhere.

Robert

"Phil Read" <phil@vizualweb.com> wrote in message
news:67AA7A93-3E0D-4C8C-B9B5-C7CFE2456781@news.elevatesoft.com...
> Robert, I have to say "YoU Da MaN" !
>

Please use English when posting in these groups.

Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote in message
news:EDF1178E-E122-49EA-A2DA-767234B7C947@news.elevatesoft.com...
>
> "Phil Read" <phil@vizualweb.com> wrote in message
> news:67AA7A93-3E0D-4C8C-B9B5-C7CFE2456781@news.elevatesoft.com...
>> Robert, I have to say "YoU Da MaN" !
>>
>
> Please use English when posting in these groups.
>

Hope it's clear it was a joke. Sometimes humor does not come through in
plain text.

Glad you could use the solution.

Robert

Robert


I got it - but it helps if your add a or <bg> or even <vbg> after it - I've upset Tim a time or two without meaning to by missing the indicator of a joke. I've also upset him a few times deliberately as well.


Roy Lambert