sql 凭证明细表 科目余额表_sql查询金蝶科目余额表个期间的数据

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