• 财务对账-资金统计



    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')

  • 相关阅读:
    ACM实用C语言函数
    Java中的数组和方法
    eclipse常用快捷键
    【转载】第十章 五种对称加密算法总结
    【转载】ZooKeeper学习第二期--ZooKeeper安装配置
    【转载】Java垃圾回收机制
    【转载】JVM系列三:JVM参数设置、分析
    【转载】JVM系列二:GC策略&内存申请、对象衰老
    【转载】JVM系列一:JVM内存组成及分配
    java项目性能测试过程记录
  • 原文地址:https://www.cnblogs.com/zxtceq/p/5703748.html
Copyright © 2020-2023  润新知