select dtime1,SUM(fkje) as fkje,

(select sum(borrow_amount) from yyd_borrow_borrowinfo where (status=10 or status=20) and loandate<=tongjitb.dtime1

--张亮修改,是否属于内部用户11.5

) sumFKJE,

(select sum(amount) from yyd_Account_moneyrecord where state=1 and (moneytype='充值' or moneytype='线下充值') and paytime<=tongjitb.dtime1

--张亮修改,是否属于内部用户11.5

) sumCZJE

,SUM(czje) as czje,

SUM(txje) as txje,

(select sum(amount) from yyd_Account_moneyrecord where state=1 and (moneytype='提现'or moneytype='线下提现') and createtime<=tongjitb.dtime1

--张亮修改,是否属于内部用户11.5

) sumTXJE,

SUM(ptje) as ptje,

(select sum(jfmoney) from HUR_PlatFormFinanceDetail where feeDate<=dtime1 and fType=3

--张亮修改,是否属于内部用户11.5

) sumPTJE,

SUM(lxje) as lxje,

(select sum(repay_Amount) from yyd_borrow_repayrecord where repaytype='利息' and repay_time<=tongjitb.dtime1

--张亮修改,是否属于内部用户11.5

)sumLXJE,

SUM(sxfje) as sxfje,

(select SUM( amount *( case recharge_type when 'chinapaywap' then 0.01 when 'chinapay_b2c' then 0.005 when '0' then 0.005 else 0 end) )from YYD_Account_RechargeRecord where state=1 and createtime<=dtime1

--张亮修改,是否属于内部用户11.5

)sumSXFJE,

--(select ISNULL(usableamount,0)+ISNULL(withdrawing,0) from (select sum(usableamount) usableamount,(select SUM(amount) from YYD_Account_MoneyRecord where (moneytype='提现' or moneytype='线下提现') and state=0

----张亮修改,是否属于内部用户11.5

--) withdrawing from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)

----张亮修改,是否属于内部用户11.5

--) data) UsableTotal,

--2016-07-14修改备兑总金额:UsableTotal =可用余额+冻结(UsableAmount+FrozenAmount)

(select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance

where CONVERT(datetime,period)=CONVERT(datetime,dtime1)) as UsableTotal,

 

--(select sum(balanceamount) from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)

----张亮修改,是否属于内部用户11.5

--) balanceAmount,

--2016-07-14修改--账户余额:balanceAmount = 可用余额总金额(UsableAmount)

(select sum(UsableAmount) from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)

) balanceAmount,

(select sum(repay_amount) from YYD_Borrow_RepayRecord where repay_time>=dtime1 and repaytype='本金' ) DSBJAmount

--待收本金

from

(select CONVERT(datetime, ISNULL(ISNULL(ISNULL(tt1.dt1,(ISNULL(tt1.dt2,tt1.dt3))),tt1.dt5),tt1.dt6)) as dtime1 ,tt1.fkje,tt1.czje,tt1.txje,tt1.ptje,tt1.lxje,tt1.sxfje from

(

select * from

(

--标的详情主表（放款金额）

select SUM (borrow_amount) as fkje,convert(nvarchar(10),loandate,120) dt1 from YYD_Borrow_BorrowInfo

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),loandate,120)

) as zb

full join

--平台费

(

select sum(jfmoney) ptje,convert(nvarchar(10),feeDate,120) feeDate from HUR_PlatFormFinanceDetail where ftype=3

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),feeDate,120)

)

as pt

on pt.feeDate=zb.dt1

full join

( ---充值金额

select convert(nvarchar(10),paytime,120) dt2,SUM(amount) czje from YYD_Account_MoneyRecord where (moneytype='充值' or moneytype='线下充值') and state=1

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),paytime,120)

) as cz

on zb.dt1=cz.dt2

full join

----开始其他的表合并

(

--手续费表

select convert(nvarchar(10),createtime,120) dt6, SUM( amount *( case recharge_type when 'chinapaywap' then 0.01 when 'chinapay_b2c' then 0.005 when '0' then 0.005 else 0 end) ) as sxfje from YYD_Account_RechargeRecord

where state=1

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),createtime,120)

) as sxf on zb.dt1=sxf.dt6

full join

(

--提现表

select convert(nvarchar(10),createtime,120) dt3,SUM(amount) txje from YYD_Account_MoneyRecord where (moneytype='提现' or moneytype='线下提现') and state=1

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),createtime,120)

) as tx on zb.dt1=tx.dt3

full join

(

--利息表

select convert(nvarchar(10),repay_time,120) dt5,SUM(repay_amount) lxje from YYD_Borrow_RepayRecord where status=1 and repaytype='利息'

--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),repay_time,120)

) as lx on zb.dt1=lx.dt5

)as tt1

) as tongjitb

group by dtime1 order by dtime1 desc

 

--注释说明对应的字段如下：

--备兑金额:UsableTotal =可用+冻结(UsableAmount+FrozenAmount)

--账户余额:balanceAmount = 可用余额总金额(UsableAmount)

--待收本金:DSBJAmount

--(select ISNULL(usableamount,0)+ISNULL(withdrawing,0) from

--(select sum(usableamount) usableamount,(select SUM(amount) from YYD_Account_MoneyRecord

--where (moneytype='提现' or moneytype='线下提现') and state=0

--)

-- withdrawing from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2016-07-13 00:00:00.000')

--) data) UsableTotal

-- select * from hur_accountinfo_balance order by ID desc

-- select UsableAmount+FrozenAmount from(

-- (select SUM(UsableAmount) as UsableAmount from hur_accountinfo_balance

-- where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')) sss2,

-- (select SUM(FrozenAmount) as FrozenAmount from hur_accountinfo_balance

-- where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')) sss

--)

-- select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')

-- select ss=ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0) from hur_accountinfo_balance

-- select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance

-- select sum(UsableAmount) as balanceAmount from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')