select
null as QYDM,
null as SBNF,
null as SBYF,
本期发生_本年累计_科目余额表.会计年度 as
会计年度,
本期发生_本年累计_科目余额表.会计期间 as 会计期间,
本期发生_本年累计_科目余额表.借贷方向_1借_负1贷
as 借贷方向_1借_负1贷,
本期发生_本年累计_科目余额表.科目级次 as
科目级次,
本期发生_本年累计_科目余额表.明细科目_1是_0否 as
明细科目_1是_0否,
本期发生_本年累计_科目余额表.科目编码 as
KM,
本期发生_本年累计_科目余额表.科目名称 as
科目代码,
本期发生_本年累计_科目余额表.本币借方发生额 as
本期借方发生数BQJF,
本期发生_本年累计_科目余额表.本币贷方发生额 as
本期贷方发生数BQDF,
本期发生_本年累计_科目余额表.本年本币借方发生额 as
本年借方发生数BNJF,
本期发生_本年累计_科目余额表.本年本币贷方发生额 as
本年贷方发生数BNDF,
年初借方_年初贷方_余额_C.年初借方余额 as
年初借方余额NCJF,
年初借方_年初贷方_余额_C.年初贷方余额 as
年初贷方余额NCDF,
期初借方_期初贷方_余额_A.期初借方余额 as
上期借方余额SQJF,
期初借方_期初贷方_余额_A.期初贷方余额 as
上期贷方余额SQDF,
期末借方_期末贷方_余额_B.期末借方余额 as
期末借方余额QMJF,
期末借方_期末贷方_余额_B.期末贷方余额 as
期末贷方余额QMDF
from
(
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本年索引码_年_币_科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本年索引码_年_币_科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Balance.FDebit as 本币借方发生额,
t_Balance.FCredit as 本币贷方发生额,
t_Balance.FYtdDebit as 本年本币借方发生额,
t_Balance.FYtdCredit as 本年本币贷方发生额,
t_Balance.FAccountID as 科目内码_余额表,
t_Account.FAccountID as 科目内码_科目表,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否
from
t_Balance left outer join t_Account on
t_Balance.FAccountID = t_Account.FAccountID
where
t_Balance.FCurrencyID = 1
)
as 本期发生_本年累计_科目余额表
--左连接
余额数据
left outer
join
(
select *
from
(
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FBeginBalance ) as 期初借方余额,
null as
期初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FBeginBalance >= 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
期初借方余额,
( - t_Balance.FBeginBalance ) as 期初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FBeginBalance < 0
)and
t_Balance.FCurrencyID = 1
--以下为
会计科目方向为 的语句
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FBeginBalance ) as 期初借方余额,
null as
期初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FBeginBalance > 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
期初借方余额,
( - t_Balance.FBeginBalance ) as
期初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FBeginBalance <= 0
)and
t_Balance.FCurrencyID = 1
) as 期初借方_期初贷方_余额
) as 期初借方_期初贷方_余额_A
on
本期发生_本年累计_科目余额表.本期索引码_年月币科目内码 = 期初借方_期初贷方_余额_A.本期索引码_年月币科目内码
left outer join
--左连接
余额数据
(
select *
from
(
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FEndBalance ) as 期末借方余额,
null as
期末贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FEndBalance >= 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
期末借方余额,
( - t_Balance.FEndBalance ) as 期末贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FEndBalance < 0
)and
t_Balance.FCurrencyID = 1
--以下为
会计科目方向为 的语句
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FEndBalance ) as 期末借方余额,
null as
期末贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FEndBalance > 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本期索引码_年月币科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FPeriod)))
+ '-' + convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本期索引码_年月币科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
期末借方余额,
( - t_Balance.FEndBalance ) as
期末贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FEndBalance <= 0
)and
t_Balance.FCurrencyID = 1
) as 期末借方_期末贷方_余额
) as
期末借方_期末贷方_余额_B
on
本期发生_本年累计_科目余额表.本期索引码_年月币科目内码 = 期末借方_期末贷方_余额_B.本期索引码_年月币科目内码
--左连接
余额数据
left outer
join
(
select *
from
(
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本年索引码_年_币_科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本年索引码_年_币_科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FBeginBalance ) as 年初借方余额,
null as
年初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FBeginBalance >= 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本年索引码_年_币_科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本年索引码_年_币_科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
年初借方余额,
( - t_Balance.FBeginBalance ) as 年初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = 1
科目默认为借方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC = 1
AND
t_Balance.FBeginBalance < 0
)and
t_Balance.FCurrencyID = 1
--以下为
会计科目方向为 的语句
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本年索引码_年_币_科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本年索引码_年_币_科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
( + t_Balance.FBeginBalance ) as 年初借方余额,
null as
年初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance >= 0本币期初 大于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FBeginBalance > 0
)and
t_Balance.FCurrencyID = 1
union all
select
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Balance.FAccountID))) ) as
本年索引码_年_币_科目内码,
(convert(varchar(10),ltrim(rtrim(t_Balance.FYear)))
+ '-' +
convert(varchar(10),ltrim(rtrim(t_Balance.FCurrencyID)))
+ '-' + convert(varchar(20),ltrim(rtrim(t_Account.FNumber))) )
as 本年索引码_年_币_科目编码,
t_Balance.FYear as 会计年度,
t_Balance.FPeriod as 会计期间,
t_Account.FNumber as 科目编码,
t_Account.FName as 科目名称 ,
t_Account.FDC as 借贷方向_1借_负1贷,
t_Account.FLevel as 科目级次,
t_Account.FDetail as 明细科目_1是_0否,
null as
年初借方余额,
( - t_Balance.FBeginBalance ) as
年初贷方余额
from t_Balance left
outer join t_Account on t_Balance.FAccountID =
t_Account.FAccountID
--1.t_Account.FDC = -1
科目默认为贷方;/2.t_Balance.FBeginBalance < 0本币期初 小于
0;/3.t_Balance.FCurrencyID = 1币种 = 人民币;/
where (t_Account.FDC =
-1 AND
t_Balance.FBeginBalance <= 0
)and
t_Balance.FCurrencyID = 1
) as 年初借方_年初贷方_余额
where 会计期间 = 1
) as
年初借方_年初贷方_余额_C
on
本期发生_本年累计_科目余额表.本年索引码_年_币_科目内码 =
年初借方_年初贷方_余额_C.本年索引码_年_币_科目内码
