两个表:
表一:Agency_code Agency_Name Amount COA_ID
----------- ------------- ------ --------
MVSIN Agency Name a 200.01 1950-000
MVSIN Agency Name b 300 1011-002
MVSIN Agency Name c 200.01 1111-002
MVSIN Agency Name d 200.01 1011-005
MVSIN Agency Name e 200.01 1111-099
MAXSGP Agency Name f 200.01 1211-003
MAXSGP Agency Name g 200.01 1111-005
MAXSGP Agency Name h 200.01 8150-000
MAXSGP Agency Name i 200.01 9160-101
MAXSGP Agency Name j 200.01 8380-001
MAXSGP Agency Name k 200.01 1540-000 表2:
Agency_Code Cagetory Group Range_Start Range_End
----------- ------------- ------ ------------- ---------
MAXSGP Net Billings: MEDIA 1011-100 1011-001
MAXSGP Cost of Billings: MEDIA 1111-100 1111-001
MAXSGP Service Fee: MEDIA 1211-100 1211-001
MAXSGP Commission: MEDIA 1211-100 1211-100
MAXSGP T&E Direct a/cs: OPERATING COSTS 1605-800 1600-400
MAXSGP Total Operating Costs: OPERATING COSTS 2601-000 1405-001
MVSIN Net Billings: MEDIA 1011-100 1011-001
MVSIN Cost of Billings: MEDIA 1111-100 1111-001
MVSIN Service Fee: MEDIA 1211-100 1211-001
MVSIN T&E Direct a/cs: OPERATING COSTS 1605-800 1600-200
MVSIN Total Operating Costs: OPERATING COSTS 2601-000 1405-200 要两个计算:
当agency_code=MVSIN时,所有group为Midia, 而且 coa_id 要在 range_start和range_end之间的, 所有的category为: Net Billings,Service Fee和Commission的Amout加起来。也就是公式为: Net Billings(amount) + Service Fee(Amount) + Commission(Amount) 并且要符合我说的那些条件。其它所有在表一中的coa_id如果不落在表二任何一个range_start和range_end之间的不用参加计算。谢谢。
表一:Agency_code Agency_Name Amount COA_ID
----------- ------------- ------ --------
MVSIN Agency Name a 200.01 1950-000
MVSIN Agency Name b 300 1011-002
MVSIN Agency Name c 200.01 1111-002
MVSIN Agency Name d 200.01 1011-005
MVSIN Agency Name e 200.01 1111-099
MAXSGP Agency Name f 200.01 1211-003
MAXSGP Agency Name g 200.01 1111-005
MAXSGP Agency Name h 200.01 8150-000
MAXSGP Agency Name i 200.01 9160-101
MAXSGP Agency Name j 200.01 8380-001
MAXSGP Agency Name k 200.01 1540-000 表2:
Agency_Code Cagetory Group Range_Start Range_End
----------- ------------- ------ ------------- ---------
MAXSGP Net Billings: MEDIA 1011-100 1011-001
MAXSGP Cost of Billings: MEDIA 1111-100 1111-001
MAXSGP Service Fee: MEDIA 1211-100 1211-001
MAXSGP Commission: MEDIA 1211-100 1211-100
MAXSGP T&E Direct a/cs: OPERATING COSTS 1605-800 1600-400
MAXSGP Total Operating Costs: OPERATING COSTS 2601-000 1405-001
MVSIN Net Billings: MEDIA 1011-100 1011-001
MVSIN Cost of Billings: MEDIA 1111-100 1111-001
MVSIN Service Fee: MEDIA 1211-100 1211-001
MVSIN T&E Direct a/cs: OPERATING COSTS 1605-800 1600-200
MVSIN Total Operating Costs: OPERATING COSTS 2601-000 1405-200 要两个计算:
当agency_code=MVSIN时,所有group为Midia, 而且 coa_id 要在 range_start和range_end之间的, 所有的category为: Net Billings,Service Fee和Commission的Amout加起来。也就是公式为: Net Billings(amount) + Service Fee(Amount) + Commission(Amount) 并且要符合我说的那些条件。其它所有在表一中的coa_id如果不落在表二任何一个range_start和range_end之间的不用参加计算。谢谢。
from b1,b2
where b1.agency_code = b2.agency_code
and b1.agency_code = 'MVSIN'
and (b1.coa_id > b2.range_start and b1.coa_id < b2.range_end)
and b2.category in ('Net Billings','Service Fee','Commission')
from
(
select distinct a.*
from a,b
where a.agency_code=b.agency and group='Midia' and a.agency_code='MVSIN'
and coa_id between range_start and range_end
and cagetory in('Net Billings','Service Fee','Commission')
)aa
'Net Billings','Service Fee','Commission的Amout') and replace(a.COA_ID,'-',0) between replace(Range_Start,'-',0) and replace(Range_End,'-',0)
select sum(a.Amount) from table1 a,table2 b where a.Agency_code=b.Agency_Code and b.Groups='MEDIA ' and b.Cagetory in(
'Net Billings','Service Fee','Commission的Amout') and cast(replace(a.COA_ID,'-',0) as int) between cast(replace(Range_Start,'-',0) as int)and cast(replace(Range_End,'-',0) as int)
没测试的数据太长了
where a.Agency_Code = 'MVSIN' and
b.Agency_Code = 'MVSIN' and
b.Cagetory in ('Net Billings' , 'Service Fee' , 'Commission') and
a.coa_id >= b.Range_Start and a.coa_id <= b.Range_End