------------------------------------------------------------------------------------

       --数量期初余额左连接凭证汇总

    drop view df_v_数量余额表

    select * from df_v_数量余额表 order by 科目编码

    

    create view df_v_数量余额表 as 

      (

       select    t_Account.FAccountID as 科目内码 , t_Account.FNumber as 科目编码  , t_Account.FName as 科目名称 , t_Account.FFullName as 科目全称  ,  t_Account.FLevel as 科目级次  , ( case   when t_Account.FDC = 1 then '借' when t_Account.FDC = -1 then '贷'  end ) as 科目方向  , ( case   when t_Account.FDetail = 1 then  '是'  when t_Account.FDetail = 0 then '否'    end ) as 是否末级    

             , ( case when t_quantityBalance.FBeginQty = 0 then '平' when t_quantityBalance.FBeginQty > 0 and t_Account.FDC = 1 then '借'  when t_quantityBalance.FBeginQty > 0 and t_Account.FDC = -1 then '贷'   when t_quantityBalance.FBeginQty < 0 and t_Account.FDC = 1  then '贷' when t_quantityBalance.FBeginQty < 0 and t_Account.FDC = -1  then '借' end ) as 数量期初方向

             , ( case when t_quantityBalance.FBeginQty > 0 then t_quantityBalance.FBeginQty  when t_quantityBalance.FBeginQty < 0 then -t_quantityBalance.FBeginQty end )  as 数量期初余额    

             ,   isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) as 借方发生_数量合计

             ,   isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) as 贷方发生_数量合计

             , ( case when  ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) )=0  then '平'  when  ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) )>0  then '借'    when  ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) )<0  then '贷'  end   ) as   数量期末方向

             , ( case when  ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) ) >=0 then ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) )   when  ( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) ) <0 then  -( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) )     end      )  as 数量期末余额  

       from t_quantityBalance left outer join    t_Account  on t_quantityBalance.FAccountID = t_Account.FAccountID    

                              left outer join    t_Currency on t_quantityBalance.FCurrencyID = t_Currency.FCurrencyID            

                              left outer join (  

                                                 select   t_Account.FAccountID as 科目内码 , t_Account.FNumber as 科目编码 , t_Account.FName as 科目名称 , sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  )  As  借方发生_数量合计 , sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  )  As  贷方发生_数量合计  

                                                 from     t_Voucher  left outer join    t_VoucherEntry on t_Voucher.FVoucherID = t_VoucherEntry.FVoucherID left outer join    t_Account on t_VoucherEntry.FAccountID = t_Account.FAccountID 

                                                 where    t_Account.Fquantities = 1   and                                 --数量核算的科目

                                                        ( t_Voucher.FYear >=2007  and  t_Voucher.FPeriod >=1  ) and       --**--<变量1>凭证<开始的年度和期间>                                           

                                                        ( t_Voucher.FYear <=2007  and  t_Voucher.FPeriod <=12 )           --**--<变量2>凭证<结束的年度和期间>      

                                                 group by t_Account.FAccountID , t_Account.FNumber , t_Account.FName

                                              )  as df_v_t_Voucher_qty on t_Account.FAccountID = df_v_t_Voucher_qty.科目内码

       where t_quantityBalance.FCurrencyID = 0                                         --只取综合本位币

             and  t_quantityBalance.FYear = 2007 and t_quantityBalance.FPeriod =1       --**--<变量3><必填>--<开始的年度和期间>

      --     and( t_quantityBalance.FBeginQty + isnull(df_v_t_Voucher_qty.借方发生_数量合计,0) - isnull(df_v_t_Voucher_qty.贷方发生_数量合计,0) ) <0    --<临时>使用方便查看

      --order by t_Account.FNumber                                                      --<临时>使用方便查看

       union all

       --判断凭证汇总中的科目存在,但是数量期初中不存在的,科目

       select  t_Account.FAccountID as 科目内码 ,  t_Account.FNumber as 科目编码 , t_Account.FName as 科目名称 , t_Account.FFullName as 科目全称  ,  t_Account.FLevel as 科目级次  , ( case   when t_Account.FDC = 1 then '借' when t_Account.FDC = -1 then '贷'  end ) as 科目方向  , ( case   when t_Account.FDetail = 1 then  '是'  when t_Account.FDetail = 0 then '否'    end ) as 是否末级    

             , '平' as 数量期初方向

             , 0 as 数量期初余额 

             , sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  )  As  借方发生_数量合计 

             , sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  )  As  贷方发生_数量合计  

             ,(case when ((sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) ) =0 ) then '平'  when ((sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) ) >0 ) then '借'    when ((sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) ) <0 ) then '贷'       end  )   as 数量期末方向

             ,(case when ((sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) )>=0 ) then (sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) )  when ((sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) ) <0 ) then  -(sum( case when t_VoucherEntry.FDC = 1 then t_VoucherEntry.FQuantity when t_VoucherEntry.FDC = 0 then 0 end  ) - sum( case when t_VoucherEntry.FDC = 1 then 0 when t_VoucherEntry.FDC = 0 then t_VoucherEntry.FQuantity end  ) )           end  )   as 数量期末余额

      from    t_Account RIGHT outer join  (  

                                                select FAccountID  from (select distinct  t_VoucherEntry.FAccountID from  t_VoucherEntry left outer join   t_Account on t_VoucherEntry.FAccountID = t_Account.FAccountID where t_Account.Fquantities = 1   ) as 表A

                                                where (select count(1) as num from t_quantityBalance as 表B where 表B.FAccountID = 表A.FAccountID) = 0                                          

                                           )    as 凭证存在期初不存在的科目 on t_Account.FAccountID = 凭证存在期初不存在的科目.FAccountID

                         left outer join    t_VoucherEntry on t_VoucherEntry.FAccountID = t_Account.FAccountID 

                         left outer join    t_Voucher on  t_VoucherEntry.FVoucherID = t_Voucher.FVoucherID 

       where ( t_Voucher.FYear >=2007  and  t_Voucher.FPeriod >=1  ) and       --**--<变量1>凭证<开始的年度和期间>                                           

             ( t_Voucher.FYear <=2007  and  t_Voucher.FPeriod <=12 )           --**--<变量2>凭证<结束的年度和期间>   

       group by t_Account.FAccountID , t_Account.FNumber , t_Account.FName , t_Account.FFullName ,  t_Account.FLevel , ( case   when t_Account.FDC = 1 then '借' when t_Account.FDC = -1 then '贷'  end ) , ( case   when t_Account.FDetail = 1 then  '是'  when t_Account.FDetail = 0 then '否'    end ) 

       --order by t_Account.FNumber                                                      --<临时>使用方便查看

    )