Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to do this complex logic!!!!!!

Author  Topic 

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-08 : 14:19:52
I have this table which has companyname,Program and ReceivedAmount and other table where ProgramName,Accountnumber and AccountAmount is stored. So program will use money from this table based on account number.If money in one account is not enough it will go to next account for money.

CompanyName Program ActualReceivedAmount
A P1 140000
B P2 136000
C P2 76000
D P2 220000
E P2 52000

Program AccountNumber AccountAmount
P1 100011 24000
P1 100012 116000
P2 100045 180000
P2 100046 304000

I want to get output like this.

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100046 136000
C P2 100046 76000
D P2 100045 180000
D P2 100046 40000
E P2 100046 52000

Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number.

Please help
Thanks in advance.

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-08 : 21:01:26
Any Help.
Go to Top of Page

samskolli
Starting Member

2 Posts

Posted - 2012-11-08 : 21:39:15
If you use up all your money in program table for a company, lets say C, is that money in the program table still available for the next company?
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-08 : 21:47:09
No. Second table will say for which program how many accountnumber are setup and how much money is allocated for each account.

what i actually need is how i can distribute money between 2 account for organization which is participating in the program.
Go to Top of Page

samskolli
Starting Member

2 Posts

Posted - 2012-11-09 : 00:04:42
I understand your requirement now.

I can think of a rbar approach on top of my head. I will try to see tommorow morning if there could be a set based approach.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 07:24:27
Thanks
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 10:14:28
Any help guys!!!
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-11-09 : 12:58:10
This looks like a variation on the 'bin packing' problem.

There are some articles on this, by Hugo Kornelis, here:

http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Bin+Packing/default.aspx
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 15:44:25
Is it possible to do this in SQL. Please help
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-09 : 16:02:07
I would have liked to help, but after reading your post 3 or four times, I couldn't follow what you are trying to do - I admit that it is my failing because others have understood what you are trying to do, but I suspect there may be others on the forum who are in a similar situation as I am.

You refer to "this table" and "other table". If you can name the tables - call them Table A and Table B if you like - that would make it a little clearer Also, I did not follow which table acts as the source and which is the destination, or what the rules are. Can you describe what your logic would be if you were to do this manually, one step at a time?

Also, if you want to post code or prserve white spaces, use the [code] and [/code] tags and put the text in between those two tags.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 16:08:34
Sure Sunita. Thank you.

I have Company table which has companyname,Program and ReceivedAmount and ProgramAccount table where ProgramName,Accountnumber and AccountAmount is stored. So program will use money from this table based on account number.If money in one account is not enough it will go to next account for money.

Company ( Main table)

CompanyName Program ActualReceivedAmount
A P1 140000
B P2 136000
C P2 76000
D P2 220000
E P2 52000

ProgramAccount ( This is lookup table)

Program AccountNumber AccountAmount
P1 100011 24000
P1 100012 116000
P2 100045 180000
P2 100046 304000

I want to get output like this from above.

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100046 136000
C P2 100046 76000
D P2 100045 180000
D P2 100046 40000
E P2 100046 52000

Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number.

Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-11-09 : 17:07:59
You have not explained why company B with program P2 uses account 100046 etc.
The only reason I can think for this is to get the best fit. ie It is the bin packing problem.

Read the articles I referenced to get an idea of how to do this in SQL.

Here is some background information from wikipedia:

http://en.wikipedia.org/wiki/Bin_packing_problem
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 17:34:25

Because Program 2 has all money to support for
B,C, 40000 for D and E.

Company A is using P1 Accountnumber 100011(24000) and 100012(116000) which totals to 140000. Out of 220000 money received by D,it is first all money from 100045(180000) and remaining 40000 from 100046. Hope that is clear.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-09 : 22:08:30
Where is Visakh,Peso,Khtan and Jsmith nowdays ??? Gurus!!!
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-10 : 02:58:25
How can we know the distribution between accounts and across companies sharing the same program? Can't this answer be correct as well? The totals for program still add up as do the totals for company - just a different distribution.

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 10
B P2 100046 135990
C P2 100046 76000
D P2 100045 179990
D P2 100046 40010
E P2 100046 52000

Be One with the Optimizer
TG
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-10 : 10:00:07
Yes but we will not split organization into 2 account if the account itself has money. We will split only when money is not sufficient in the account.

For Example : For Program 2, there are 2 accounts 100045,100046).

Account(100045) has only 180000 so I have splitted Organization D into 2 Accountnumber. Organization D is receiving 220000.

D 100045 180000
D 100046 40000

If you add up this one below it will equal to total money for account 100046 which is 304000.

B P2 100046 136000
C P2 100046 76000
D P2 100046 40000
E P2 100046 52000



Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-11-10 : 10:01:39
quote:
Originally posted by TG

How can we know the distribution between accounts and across companies sharing the same program? Can't this answer be correct as well? The totals for program still add up as do the totals for company - just a different distribution.

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 10
B P2 100046 135990
C P2 100046 76000
D P2 100045 179990
D P2 100046 40010
E P2 100046 52000

Be One with the Optimizer
TG



Exactly the point I was trying to get over to the OP.
The only reason, I can see, for going with his results is that it is a better fit.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-10 : 10:06:01
Ifor,

We will not try to split unless it is required. we will try to use as money as possible in that account.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-10 : 10:10:29
It can be this combination also

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 136000
C P2 100045 44000
C P2 100046 32000
D P2 100046 220000
E P2 100046 52000

Or this combination

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 136000
C P2 100046 76000
D P2 100046 220000
E P2 100046 8000
E P2 100045 44000
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-10 : 12:29:16
Because there is not necessarily a single right answer will any right answer do? Or is there some preference like evenly distribute an account across all companies in a program? Or perhaps apply some sequence to the companies so that the first company gets all available then move to the next company, etc.?

Be One with the Optimizer
TG
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-10 : 15:21:48

This will work

apply some sequence to the companies so that the first company gets all available then move to the next company

Thank you TG.
Go to Top of Page
  Previous Page&nsp;  Next Page

- Advertisement -