跨年跨帐套查询

zsl_7401

--跨帐套、跨年查询
--注意帐套、年份
--UfData_001_2010
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2010.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2010.dbo.GL_accvouch a where iperiod<>'0' order by 年份,月份,凭证号

union all
--UfData_001_2011
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2011.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2011.dbo.GL_accvouch a where iperiod<>'0' order by 年份,月份,凭证号

union all
--UfData_001_2012
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2012.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2012.dbo.GL_accvouch a where iperiod<>'0' order by 年份,月份,凭证号

union all
--UfData_001_2013
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2013.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2013.dbo.GL_accvouch a where iperiod<>'0' order by 年份,月份,凭证号

union all
--UfData_001_2014
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2014.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2014.dbo.GL_accvouch a where iperiod<>'0' order by 年份,月份,凭证号

union all
--UfData_001_2015
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_001_2015.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_001_2015.dbo.GL_accvouch a where iperiod<>'0'  order by 年份,月份,凭证号


------------------------
--UfData_002_2015
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_002_2015.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_002_2015.dbo.GL_accvouch a where iperiod='12'

union all
--UfData_002_2016
select CONVERT(varchar(4),dbill_date,120) as 年份, iperiod as 月份,ino_id as 凭证号,dbill_date as 凭证日期,cbill as 制单员,ccode as 科目编码,
(select ccode_name from UfData_002_2016.dbo.code b where  b.ccode=a.ccode)as 科目名称,
cdigest as 摘要, md as 借方, mc as 贷方 from UfData_002_2016.dbo.GL_accvouch a where iperiod<='12' and iperiod>='1'  order by 年份, iperiod,ino_id