Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 12 total |
Get a running balance. |
Tue, Dec 2 2008 12:13 AM | Permanent Link |
Phil Read | 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! |
Tue, Dec 2 2008 8:41 AM | Permanent Link |
"Robert" | "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! |
Tue, Dec 2 2008 9:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Dec 2 2008 10:03 AM | Permanent Link |
"Robert" | "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 |
Tue, Dec 2 2008 10:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Robert
If you get any better you'll be able to challenge Ole <vbg> Roy Lambert |
Tue, Dec 2 2008 7:16 PM | Permanent Link |
Phil Read | 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. |
Wed, Dec 3 2008 12:04 AM | Permanent Link |
"Robert" | "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 |
Wed, Dec 3 2008 12:05 AM | Permanent Link |
"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 |
Wed, Dec 3 2008 10:33 AM | Permanent Link |
"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 |
Wed, Dec 3 2008 11:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
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 |