第6行,f3不是等于'0',而是f3=1411;
你的条件是code<=c.code 实际上第6行的code=20012,而第五行的CODE=20013
这样就造成code>c.code,与条件不符
第9行,f3不是等于'-5266',而是f3=0;
你的条件是code<=c.code 实际上第9行的code=90013,而第8行的CODE=90016
这样就造成code<c.code,与条件不符
第11行,f3不是等于'-1962',而是f3=0;
错误同上所以你可能要从新排序一下
order by listdate,code
这样会照成收款在前,销售在后
你的条件是code<=c.code 实际上第6行的code=20012,而第五行的CODE=20013
这样就造成code>c.code,与条件不符
第9行,f3不是等于'-5266',而是f3=0;
你的条件是code<=c.code 实际上第9行的code=90013,而第8行的CODE=90016
这样就造成code<c.code,与条件不符
第11行,f3不是等于'-1962',而是f3=0;
错误同上所以你可能要从新排序一下
order by listdate,code
这样会照成收款在前,销售在后
好像code+billcode+listdate,也不是的。
--因为你的这个比较无法正常进行listdate<=c.listdate and code<=c.code and billcode<=c.billcode
create table #tmp_e(id int,
code nvarchar(20) ,billcode varchar(10),listdate smalldatetime , type varchar(20), customercode varchar(20), customername varchar(20), department varchar(20), currency varchar(20),f1 numeric(8,3), f2 numeric(8,3), f3 numeric(8,3))
drop table #tmp_e
truncate table #tmp_e
insert #tmp_e
select 1, '10010' ,'A329', '2005-09-01','本期销售码单','099','姚生','中大江南纺','人民币', 1975, 0, 0
union all
select 2,'10010','A329','2005-09-01','本期销售收款','099','姚生','中大江南纺','人民币', 0,1975, 0
union all
select 3,'20012' ,'A343','2005-09-02','本期销售码单','099','姚生','中大江南纺','人民币',1302, 0, 0
union all
select 4,'20013' ,'A344','2005-09-02','本期销售码单','099','姚生','中大江南纺','人民币', 1411, 0, 0
union all
select 5,'20012','A343','2005-09-02','本期销售收款','099','姚生','中大江南纺','人民币',0, 1302, 0
union all
select 6,'20013','A344','2005-09-02','本期销售收款','099','姚生','中大江南纺','人民币', 0, 1411, 0
union all
select 7,'90016','A440','2005-09-09','本期销售码单','099','姚生','中大江南纺','人民币', 5226 ,0 , 0
union all
select 8,'90013','A440','2005-09-09','本期销售收款','099','姚生','中大江南纺','人民币', 0 ,5226, 0
union all
select 9,'00010','A459','2005-09-10','本期销售码单','099','姚生','中大江南纺','人民币', 1962 , 0 , 0
union all
select 10,'00008','A459','2005-09-10','本期销售收款','099','姚生','中大江南纺','人民币', 0 ,1962, 0
union all
select 11,'20001','A473','2005-09-12','本期销售码单','099 ','姚生','中大江南纺','人民币', 540 , 0 , 0
union all
select 12,'20001','A473','2005-09-12','本期销售收款','099','姚生','中大江南纺','人民币', 0 , 540, 0select * from #tmp_eselect a.listdate,a.code,a.billcode,a.type,a.customercode ,a.customername ,a.department ,a.currency ,a.f1,a.f2,a.f3
from
#tmp_e a
where a.type='上期结存'
union all
select
null,null,null,'上期结存',b.customercode ,b.customername ,b.department ,b.currency ,0,0,0
from
#tmp_e b
where
not exists(select 1 from #tmp_e where customercode=b.customercode and customername=b.customername and
department=b.department and currency=b.currency and type='上期结存')
and
b.type in('本期销售码单','本期销售退货','本期销售预收','本期销售收款')
Group By b.customercode ,b.customername ,b.department ,b.currencyunion all
select listdate,code,billcode,type,customercode ,customername ,department ,currency ,f1,f2,
isnull((select f3 from #tmp_e where customercode=c.customercode and customername=c.customername and
department=c.department and currency=c.currency and type='上期结存'),0)+
(select sum(case type when '本期销售码单' then f1 else -1*f2 end) from #tmp_e where listdate is not null and
code is not null and billcode is not null and customercode=c.customercode and customername=c.customername and
department=c.department and currency=c.currency and id<=c.id
-- listdate<=c.listdate and code<=c.code and billcode<=c.billcode
)
from
#tmp_e c
where
c.type in('本期销售码单', '本期销售退货', '本期销售预收' ,'本期销售收款')
union allselect null,null,null,'本期结存',d.customercode ,d.customername ,d.department ,d.currency,
null,null,sum(case d.type when'上期结存'then f3 when'本期销售码单'then f1 when'本期销售退货'then f1 when'本期销售预收'then -1*f2
when'本期销售收款'then -1*f2 end)
from
#tmp_e d
group by
d.customercode ,d.customername ,d.department ,d.currency
2005-09-01 00:00:00 10010 A329 本期销售码单 099 姚生 中大江南纺 人民币 1975.000 .000 1975.000
2005-09-01 00:00:00 10010 A329 本期销售收款 099 姚生 中大江南纺 人民币 .000 1975.000 .000
2005-09-02 00:00:00 20012 A343 本期销售码单 099 姚生 中大江南纺 人民币 1302.000 .000 1302.000
2005-09-02 00:00:00 20013 A344 本期销售码单 099 姚生 中大江南纺 人民币 1411.000 .000 2713.000
2005-09-02 00:00:00 20012 A343 本期销售收款 099 姚生 中大江南纺 人民币 .000 1302.000 1411.000
2005-09-02 00:00:00 20013 A344 本期销售收款 099 姚生 中大江南纺 人民币 .000 1411.000 .000
2005-09-09 00:00:00 90016 A440 本期销售码单 099 姚生 中大江南纺 人民币 5226.000 .000 5226.000
2005-09-09 00:00:00 90013 A440 本期销售收款 099 姚生 中大江南纺 人民币 .000 5226.000 .000
2005-09-10 00:00:00 00010 A459 本期销售码单 099 姚生 中大江南纺 人民币 1962.000 .000 1962.000
2005-09-10 00:00:00 00008 A459 本期销售收款 099 姚生 中大江南纺 人民币 .000 1962.000 .000
2005-09-12 00:00:00 20001 A473 本期销售码单 099 姚生 中大江南纺 人民币 540.000 .000 540.000
2005-09-12 00:00:00 20001 A473 本期销售收款 099 姚生 中大江南纺 人民币 .000 540.000 .000
NULL NULL NULL 本期结存 099 姚生 中大江南纺 人民币 NULL NULL .000