看看是不是这个
存储过程，客户往来余额表。可以在bos直接报表里引用。
格式为： EXEC up_yszk @fyear@,@fpriod@
--应收余额
CREATE procedure up_yszk
@fcuryear varchar(4),
@fperiod varchar(2)
as
set nocount on
declare
@endday int,
@curenddate datetime,
@curstartdate datetime,
@period int,
@curyear int
set @period=convert(int,@fperiod)
set @curyear=convert(int,@fcuryear)

if @period in (1,3,5,7,8,10,12)
set @endday=31
if @period in (4,6,9,11)
set @endday=30
if @period=2
begin
if @curyear%4=0
set @endday=29
else
set @endday=28
end

set @curenddate=convert(datetime,convert(varchar(4),@curyear)+'-'+convert(varchar,@period)+'-'+convert(varchar(2),@endday))
set @curstartdate=convert(datetime,convert(varchar(4),@curyear)+'-'+convert(varchar,@period)+'-'+'01')
create table #temp
( FCustID int,
last_kpamount decimal(10,2), --上月开票应收账款
last_nkpamount decimal(10,2), --上月为止未开票金额
last_bal decimal(10,2), --上月应收余额
cur_kpamount decimal(10,2), --本月开票金额
cur_amount decimal(10,2), --本月销售出库发生额
cur_skamount decimal(10,2), --本月收款额
cur_kpbal decimal(10,2), --本月应收票面
cur_bal decimal(10,2) --本月应收账款
)
create table #temp2
( FCustID int,
last_kpamount decimal(10,2), --上月开票应收账款
last_nkpamount decimal(10,2), --上月为止未开票金额
last_bal decimal(10,2), --上月应收余额
cur_kpamount decimal(10,2), --本月开票金额
cur_amount decimal(10,2), --本月销售出库发生额
cur_skamount decimal(10,2), --本月收款额
cur_kpbal decimal(10,2), --本月应收票面
cur_bal decimal(10,2) --本月应收账款
)
--上月开票余额
insert into #temp(FCustID,last_kpamount)
select FCustomer,FBeginBalance from t_RP_ContactBal where FYear=@curyear and FPeriod=@period and frp=1
--查询在上期最后一天为止，尚未钩稽（即尚未开票)的销售出库单
insert into #temp(FCustID,last_nkpamount)
select FCustID,sum(famount) from (select ic.FSupplyID FCustID, ice.FConsignAmount as famount from icstockbill ic join icstockbillentry ice on ic.finterid=ice.finterid
where ic.FTranType=21 and ic.FDateunion all
--已经钩稽的单据但钩稽期间在大于或等于指定期间
select ic.FSupplyID FCustID,ice.FConsignAmount as famount from icstockbill ic join icstockbillentry ice on ic.finterid=ice.finterid
join ICHookRelations ich on ic.FHookInterID=ich.FGroupNo AND
ich.FEntryID = ice.FEntryID AND ich.FItemID = ice.FItemID and ic.finterid =ich.fibinterid
where ic.FTranType=21 and ic.FDate0 and (ich.FYear>=@curyear and ich.FPeriod>=@period) and ich.ftrantype = 21) as aa
group by FCustID

--查询在本期开票金额
insert into #temp(FCustID,cur_kpamount)
select FCustID,sum(FStdAmountincludetax) from (SELECT ics.FCustID AS FCustID,
CASE ICS.FTranType WHEN 80 THEN ICSe.FAmount + ICSe.FTaxAmount ELSE ICSe.FAmount
END AS FStdAmountincludetax
FROM ICSale ics INNER JOIN
ICSaleEntry icse ON ics.FInterID = icse.FInterID where ics.FDate>=@curstartdate and ics.FDate--查询本期销售额
insert into #temp(FCustID,cur_amount)
select FCustID,sum(famount) from (select ic.FSupplyID FCustID,ice.FConsignAmount as famount from icstockbill ic join icstockbillentry ice on ic.finterid=ice.finterid
where ic.FTranType=21 and ic.FDate>=@curstartdate and ic.FDategroup by FCustID

--查询本期收款额
insert into #temp(FCustID,cur_skamount)
select Fcustomer,sum(Famount) from t_RP_NewReceiveBill where FYear=@curyear and FPeriod=@period and frp=1
group by Fcustomer
insert into #temp2 (FCustID,last_kpamount,last_nkpamount,cur_kpamount,cur_amount,cur_skamount)
select FCustID,sum(last_kpamount) as 上期开票余额,sum(last_nkpamount) as 上期未开票金额,sum(cur_kpamount) as 本期开票金额,sum(cur_amount) as 本期销售金额,sum(cur_skamount) as 本期收款金额 from #temp group by FCustID
update #temp2
set last_bal=isnull(last_kpamount,0)+isnull(last_nkpamount,0),cur_kpbal=isnull(last_kpamount,0)+isnull(cur_kpamount,0)-isnull(cur_skamount,0),
cur_bal=isnull(last_kpamount,0)+isnull(last_nkpamount,0)+isnull(cur_amount,0)-isnull(cur_skamount,0)

select org.fnumber as 客户代码,org.fname as 客户名称,t2.last_kpamount 上月应收票面, last_nkpamount 上月发货未开票,t2.last_bal 上月应收余额,t2.cur_kpamount as 本月应收票面,t2.cur_amount 本月应收金额,t2.cur_skamount 本月已收金额,t2.cur_kpbal 本月末应收票面,t2.cur_bal 本月末应收余额 from #temp2 t2 join t_Organization org on t2.FCustID=org.FItemID
union
select '合计' as 客户代码,'' as 客户名称,sum(t2.last_kpamount), sum(last_nkpamount),sum(t2.last_bal),sum(t2.cur_kpamount),sum(t2.cur_amount),sum(t2.cur_skamount),sum(t2.cur_kpbal),sum(t2.cur_bal) from #temp2 t2
order by 客户代码
drop table #temp
drop table #temp2
GO