SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to do this complex logic!!!!!!
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/08/2012 :  14:19:52  Show Profile  Reply with Quote
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.

Edited by - SCHEMA on 11/08/2012 15:42:09

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/08/2012 :  21:01:26  Show Profile  Reply with Quote
Any Help.
Go to Top of Page

samskolli
Starting Member

2 Posts

Posted - 11/08/2012 :  21:39:15  Show Profile  Reply with Quote
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 - 11/08/2012 :  21:47:09  Show Profile  Reply with Quote
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 - 11/09/2012 :  00:04:42  Show Profile  Reply with Quote
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 - 11/09/2012 :  07:24:27  Show Profile  Reply with Quote
Thanks
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/09/2012 :  10:14:28  Show Profile  Reply with Quote
Any help guys!!!
Go to Top of Page

Ifor
Aged Yak Warrior

572 Posts

Posted - 11/09/2012 :  12:58:10  Show Profile  Reply with Quote
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 - 11/09/2012 :  15:44:25  Show Profile  Reply with Quote
Is it possible to do this in SQL. Please help
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/09/2012 :  16:02:07  Show Profile  Reply with Quote
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 - 11/09/2012 :  16:08:34  Show Profile  Reply with Quote
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.


Edited by - SCHEMA on 11/09/2012 16:09:06
Go to Top of Page

Ifor
Aged Yak Warrior

572 Posts

Posted - 11/09/2012 :  17:07:59  Show Profile  Reply with Quote
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 - 11/09/2012 :  17:34:25  Show Profile  Reply with Quote

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 - 11/09/2012 :  22:08:30  Show Profile  Reply with Quote
Where is Visakh,Peso,Khtan and Jsmith nowdays ??? Gurus!!!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/10/2012 :  02:58:25  Show Profile  Reply with Quote
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 - 11/10/2012 :  10:00:07  Show Profile  Reply with Quote
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

572 Posts

Posted - 11/10/2012 :  10:01:39  Show Profile  Reply with Quote
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 - 11/10/2012 :  10:06:01  Show Profile  Reply with Quote
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 - 11/10/2012 :  10:10:29  Show Profile  Reply with Quote
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

Edited by - SCHEMA on 11/10/2012 10:16:07
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 11/10/2012 :  12:29:16  Show Profile  Reply with Quote
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 - 11/10/2012 :  15:21:48  Show Profile  Reply with Quote

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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000