Greetings,
If you could get your data into any version of
server 2000 you could, (I think
, use the RAC
utility to simplify the solution,ie. without
a lot of nasted sql coding.Note that this is
just one possible solution using some basic
sql and RAC.
I've included some additional data.
I've also not introduced any indexes which would
be necessary.
Post back if I have misunderstood your problem
create table #Invoices (Client int,Inv int,Amount money,InvDate
smalldatetime)
go
insert #Invoices values(2,10,100.00,'10/1/03')
insert #Invoices values(2,11,300.00,'10/2/03')
insert #Invoices values(2,12,500.00,'10/5/03')
insert #Invoices values(5,45,500.00,'10/3/03')
insert #Invoices values(5,49,100.00,'10/5/03')
insert #Invoices values(5,51,100.00,'10/6/03')
insert #Invoices values(5,55,100.00,'10/6/03')
insert #Invoices values(5,56,100.00,'10/7/03')
insert #Invoices values(5,58,100.00,'10/7/03')
-- Some additional data
insert #Invoices values(7,65,40.00,'10/14/03')
insert #Invoices values(7,66,50.00,'10/15/03')
insert #Invoices values(7,67,20.00,'10/16/03')
insert #Invoices values(7,68,80.00,'10/16/03')
-- Get %50 AR values for each client
select Client,Sum(Amount)*0.50 as testamt
into #Invoices1
from #Invoices
group by Client
-- Append the testamt column to each client record.
-- This will be used for testing running sums
-- (See @wherecounters parameter below).
select a.*,testamt
into #Invoices2
from #Invoices as a inner join #Invoices1 as b
on a.Client=b.Client
The above two steps could be incorported in the RAC
@from parameter but lets keep it as straighforward as possible
-- Now get the meat
.
Exec Rac
@transform='max(Amount) as amount',
-- Sort by Client,Inv descending (or Cliend, IvDate descending).
@rows='Client & Inv(d) & InvDate(date) & testamt',
@pvtcol='Report Mode',
@from='#Invoices2',@rowbreak='n',@grand_totals='n',
-- Compute running sum of Amount within each Client.Note that
-- runs are accumulated based on the sort order specified in @rows.
@rowruns='(amount){Client}',
-- Logic to include those records that make up the over 50% of
-- that Client's AR.Each row (including the calculated run
-- of amount) is tested for inclusion.Any row that doesn't meet
-- the OR logic is excluded.
@wherecounters='(amount>testamt) or (run1<(testamt+amount))',
-- Select the data not excluded by the @wherecounters logic
-- sorting by ascending Inv (or InvDate)
@select='select Client,1*Inv as Inv,Amount,InvDate
from rac
order by client,1*Inv'
Result:
Client Inv Amount InvDate
------ ----------- ---------- --------
2 12 500.00 10/05/03
5 49 100.00 10/05/03
5 51 100.00 10/06/03
5 55 100.00 10/06/03
5 56 100.00 10/07/03
5 58 100.00 10/07/03
7 67 20.00 10/16/03
7 68 80.00 10/16/03
drop table #Invoices
drop table #Invoices1
drop table #Invoices2
It is possible to write an Access *query* to solve this.
If the above solution is correct you have a model to
base it on
.
RAC v2.2 and QALite for S2k @
www.rac4sql.net