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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/11/2012 :  20:13:19  Show Profile  Reply with Quote
Any thought !!! Thank you

Edited by - SCHEMA on 11/11/2012 20:46:04
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 11/12/2012 :  12:10:03  Show Profile  Reply with Quote
Here is an ugly procedural solution. It simply loops through an ordered set and figures out row by row what the answer should be. It will is very inefficient but seems to work.

I started to do the sequential update thing (UPDATE SET @variable=Column=Expression) but gave up on it (I see Jeff Moden lurking around today so maybe he will do it ).


set nocount on

if object_id('tempdb.dbo.#company') is not null drop table #company
if object_id('tempdb.dbo.#ProgramAccount') is not null drop table #ProgramAccount
if object_id('tempdb.dbo.#staging') is not null drop table #staging

create table #Company ( CompanyName char(1), Program char(2), ActualReceivedAmount money)
insert #company
select 'A', 'P1', 140000 union all
select 'B', 'P2', 136000 union all
select 'C', 'P2', 76000 union all
select 'D', 'P2', 220000 union all
select 'E', 'P2', 52000

create table #ProgramAccount (Program char(2), AccountNumber int, AccountAmount money)
insert #ProgramAccount
select 'P1', 100011, 24000 union all
select 'P1', 100012, 116000 union all
select 'P2', 100045, 180000 union all
select 'P2', 100046, 304000

create table #staging 
       (seq int identity(1,1)primary key clustered
       , AccountNumber int
       , CompanyName char(1)
       , AccountAmount money
       , ActualReceivedAmount money
       , AccountAmountDistributed money
       , program char(2))

insert #staging
       (AccountNumber
       , CompanyName
       , AccountAmount
       , ActualReceivedAmount
       , AccountAmountDistributed 
       , program)
select p.AccountNumber
       , c.CompanyName
       , p.AccountAmount
       , c.ActualReceivedAmount
       , 0
       , c.program
from   #company c
join   #programAccount p on p.program = c.program
order by AccountNumber
       ,companyName

declare @seq int
       ,@accountAmount money
       ,@acctBal money
       ,@companyRecBal money
       ,@accountNumber int
       ,@prevAcctNumber int
       ,@AcctNumber int
       ,@CompanyName char(1)
       ,@compAcctDist money
       ,@actualReceivedAmount money
select @prevAcctNumber = 0
       ,@companyRecBal = 0
       
select @seq = min(seq) from #staging
while @seq is not null
begin
       select @AccountNumber = AccountNumber
              ,@CompanyName = CompanyName
              ,@accountAmount = accountAmount
              ,@actualReceivedAmount = actualReceivedAmount
       from   #staging
       where  seq = @seq

       if @AccountNumber != @prevAcctNumber
       begin
              select @acctBal = @accountAmount
                     ,@prevAcctNumber = @accountNumber
       end

       select @companyRecBal = sum(AccountAmountDistributed)
       from   #staging
       where  companyName = @companyName

       set @compAcctDist = 
              case
              when @companyRecBal < @actualReceivedAmount then
                     case
                     when @ActualReceivedAmount <= @acctBal then @actualReceivedAmount - @companyRecBal
                     else @acctBal
                     end
              else 0
              end
       
       update s set
              AccountAmountDistributed = @compAcctDist
       from   #staging s
       where  seq = @seq
       and    @compAcctDist > 0

       set @companyRecBal = @companyRecBal + @compAcctDist
       set @acctBal = @acctBal - @compAcctDist

       select @seq = min(seq) from #staging where seq > @seq
end

select companyName
       , program
       , AccountNumber
       , AccountAmountDistributed [ActualReceivedAmount] 
from   #staging 
where accountAmountDistributed > 0 
order by companyName, accountNumber

select accountNumber, sum(AccountAmountDistributed) SumAccountDist from #staging group by accountNumber
select companyName, sum(AccountAmountDistributed) sumCompanyDist from #staging group by companyName

OUTPUT:

companyName program AccountNumber ActualReceivedAmount
----------- ------- ------------- ---------------------
A           P1      100011        24000.00
A           P1      100012        116000.00
B           P2      100045        136000.00
C           P2      100045        44000.00
C           P2      100046        32000.00
D           P2      100046        220000.00
E           P2      100046        52000.00

accountNumber SumAccountDist
------------- ---------------------
100011        24000.00
100012        116000.00
100045        180000.00
100046        304000.00

companyName sumCompanyDist
----------- ---------------------
A           140000.00
B           136000.00
C           76000.00
D           220000.00
E           52000.00


Be One with the Optimizer
TG
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/12/2012 :  17:50:07  Show Profile  Reply with Quote
You are genious!!!!! Thank you ...Phew.. it almost broke my head.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 11/12/2012 :  22:46:57  Show Profile  Reply with Quote
I'm not but thanks - glad its working for you

Be One with the Optimizer
TG
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/12/2012 :  23:00:31  Show Profile  Reply with Quote
Sorry It is working but when i have sample data. It is not working
same accountnumber can be used for different program.I will need F and G as it is.

create table #Company ( CompanyName char(1), Program char(2), ActualReceivedAmount money)
insert #company
select 'A', 'P1', 140000 union all
select 'B', 'P2', 136000 union all
select 'C', 'P2', 76000 union all
select 'D', 'P2', 220000 union all
select 'E', 'P2', 52000 union all
select 'F', 'P3', 200000 union all
select 'G', 'P3', 200000

create table #ProgramAccount (Program char(2), AccountNumber int, AccountAmount money)
insert #ProgramAccount
select 'P1', 100011, 24000 union all
select 'P1', 100012, 116000 union all
select 'P2', 100045, 180000 union all
select 'P2', 100046, 304000 union all
Select 'P3', 100011, 400000

My OutPut will be like this . Thanks


companyName program AccountNumber ActualReceivedAmount
A P1 100011 24000.00
A P1 100012 116000.00
B P2 100045 136000.00
C P2 100045 44000.00
C P2 100046 32000.00
D P2 100046 220000.00
E P2 100046 52000.00
F P3 100011 200000.00
G P3 100011 200000.00

Edited by - SCHEMA on 11/12/2012 23:05:44
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 11/13/2012 :  08:48:49  Show Profile  Reply with Quote
Then for the insert to #staging just add program to the order by as the first sort column:

order by program
       ,AccountNumber
       ,companyName


Be One with the Optimizer
TG
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 11/15/2012 :  22:25:53  Show Profile  Reply with Quote
I know above solution is correct.

I want to see if Visakh,Peso,Khtan or Jsmith can more optimize it
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts

Posted - 12/14/2012 :  15:13:19  Show Profile  Reply with Quote
Excellent TG!!!!!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.12 seconds. Powered By: Snitz Forums 2000