用友--客户科目余额表sql语句:注意两条语句结果集的合并

原创 2012年03月30日 15:52:39

Select Code.cCode as NaCo1,Code.ccode_name as Name1,Customer.cCusCode as NaCo2,Customer.cCusAbbName as Name2,Sum(case when iperiod=3 then (case when cbegind_c<>'贷' then mb else -mb end) else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,Sum(case when iperiod=3 then (case when cendd_c<>'贷' then me else -me end) else 0 end) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then mb_f else -mb_f end) else 0 end) end) as mbb_f,Sum(case when code.cexch_name is null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then me_f else -me_f end) else 0 end) end) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then nb_s else -nb_s end) else 0 end) end) as nbb_s,Sum(case when code.cmeasure is null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then ne_s else -ne_s end) else 0 end) end) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accass ON Code.cCode=GL_accass.cCode) ON Customer.cCusCode=GL_accass.ccus_id  where iPeriod <= 3 and not GL_accass.cCode is Null and not GL_accass.ccus_id is Null  and code.bcus=1  and GL_accass.ccode=N'112202' Group By Code.cCode,Code.ccode_name,Customer.cCusCode,Customer.cCusAbbName UNION  Select '柞柞柞' as Naco1,'柞柞柞' as Name1,'柞柞柞' as Naco2,'柞柞柞' as Name2,Sum(case when iperiod=3 then (case when cbegind_c<>'贷' then mb else -mb end) else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,Sum(case when iperiod=3 then (case when cendd_c<>'贷' then me else -me end) else 0 end) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then mb_f else -mb_f end) else 0 end) end) as mbb_f,Sum(case when code.cexch_name is null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then me_f else -me_f end) else 0 end) end) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod=3 then (case when cbegind_c<>'贷' then nb_s else -nb_s end) else 0 end) end) as nbb_s,Sum(case when code.cmeasure is null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod=3 then (case when cendd_c<>'贷' then ne_s else -ne_s end) else 0 end) end) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accass ON Code.cCode=GL_accass.cCode) ON Customer.cCusCode=GL_accass.ccus_id   where iperiod<=3 and not GL_accass.cCode is Null  and code.bcus=1  and GL_accass.ccode=N'112202' and not GL_accass.ccus_id is Null Order By NaCo1,NaCo2
------------------------------------------------------------------------------------------------------------------------

Select Code.cCode as NaCo1,Code.ccode_name as Name1,Customer.cCusCode as NaCo2,Customer.cCusAbbName as Name2,Sum(case when iperiod<3 then md-mc else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,(Sum(case when iperiod<3 then md-mc else 0 end) + Sum(case when iperiod>=3 then md else 0 end) - Sum(case when iperiod>=3 then mc else 0 end)) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) as mbb_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,(Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) + Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) - Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end)) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) as nbb_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,(Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) + Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) - Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end)) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accvouch ON Code.cCode=GL_accvouch.cCode) ON Customer.cCusCode=GL_accvouch.ccus_id  where iperiod<= 3 and not GL_accvouch.cCode is Null and not GL_accvouch.ccus_id is Null  and code.bcus=1  and GL_accvouch.ccode=N'112202' and ibook=0 and (iflag is null or iflag=2) and iperiod>=1  Group By Code.cCode,Code.ccode_name,Customer.cCusCode,Customer.cCusAbbName UNION  Select '柞柞柞' as Naco1,'柞柞柞' as Name1,'柞柞柞' as Naco2,'柞柞柞' as Name2,Sum(case when iperiod<3 then md-mc else 0 end) as mbb,Sum(case when iperiod>=3 then md else 0 end) as mdd,Sum(case when iperiod>=3 then mc else 0 end) as mcc,Sum(md) as Sumd,Sum(mc) as Sumc,(Sum(case when iperiod<3 then md-mc else 0 end) + Sum(case when iperiod>=3 then md else 0 end) - Sum(case when iperiod>=3 then mc else 0 end)) as mend,Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) as mbb_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) as mdd_f,Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end) as mcc_f,Sum(case when code.cexch_name is Null then 0 else md_f end) as Sumd_f,Sum(case when code.cexch_name is Null then 0 else mc_f end) as Sumc_f,(Sum(case when code.cexch_name is null then 0 else (case when iperiod<3 then md_f-mc_f else 0 end) end) + Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then md_f else 0 end) end) - Sum(case when code.cexch_name is Null then 0 else (case when iperiod>=3 then mc_f else 0 end) end)) as mend_f,Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) as nbb_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) as ndd_s,Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end) as ncc_s,Sum(case when code.cmeasure is Null then 0 else nd_s end) as Sumd_s,Sum(case when code.cmeasure is Null then 0 else nc_s end) as Sumc_s,(Sum(case when code.cmeasure is null then 0 else (case when iperiod<3 then nd_s-nc_s else 0 end) end) + Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nd_s else 0 end) end) - Sum(case when code.cmeasure is Null then 0 else (case when iperiod>=3 then nc_s else 0 end) end)) as mend_s  from  Customer INNER JOIN (Code INNER JOIN  GL_accvouch ON Code.cCode=GL_accvouch.cCode) ON Customer.cCusCode=GL_accvouch.ccus_id   where iperiod<=3 and not GL_accvouch.cCode is Null  and code.bcus=1  and GL_accvouch.ccode=N'112202' and  not GL_accvouch.ccus_id is Null and ibook=0 and (iflag is Null or iflag=2) and iperiod>=1 Order By NaCo1,NaCo2

NC常用SQL

select * from sm_product_version where 1=1 for update; select * from sm_codetocode where 1=1 for up...
  • sir_jun
  • sir_jun
  • 2013年06月24日 18:37
  • 1263

用友--客户科目余额表sql语句:注意两条语句结果集的合并

Select Code.cCode as NaCo1,Code.ccode_name as Name1,Customer.cCusCode as NaCo2,Customer.cCusAbbName ...
  • shj777
  • shj777
  • 2012年03月30日 15:52
  • 2825

用友NC二次开发问题汇总【转】

一、供应链采购订单问题 在采购订单参照请购单时,若选择了10行请购单的记录,采购订单的表体也就显示了10条记录,如何才能实现还可以再弹出请购单的参照,追加一条记录,而不需要取消单据后重现选择请购单...
  • softwave
  • softwave
  • 2013年03月30日 13:27
  • 26345

金蝶EAS,后台代码查询科目余额,SQL查询科目余额

以下代码演示了如何从EAS系统查询科目余额,用于后台业务逻辑(app目录下的ControllerBean)。 /** * 查询科目余额,封装数据,返回值映射中,键:科目ID;值:科目余额队列 *...
  • guosyu
  • guosyu
  • 2017年06月06日 16:03
  • 799

查询某科目某个公司下的往来单位的余额的sql语句

select e.FNumber as 机构号, g.FNumber AS 科目编码, n.FNumber as 币种编码, s.FNumber as 往来单位编码, s.FName_L2 as ...
  • douml88
  • douml88
  • 2014年11月19日 11:23
  • 730

用友NC二次开发问题汇总【转】

一、供应链采购订单问题 在采购订单参照请购单时,若选择了10行请购单的记录,采购订单的表体也就显示了10条记录,如何才能实现还可以再弹出请购单的参照,追加一条记录,而不需要取消单据后重现选择请购单...
  • softwave
  • softwave
  • 2013年03月30日 13:27
  • 26345

NC 63凭证辅助相关表

1:通过公司主键(pk_org)在AccChartVO(表:bd_accchart)找:pk_accchart 2:通过科目编码和pk_accchart在AccAsoaVO(表:bd_accas...
  • lJean
  • lJean
  • 2017年12月15日 14:33
  • 98

用友NC总账辅助余额表与应收应付模块余额表对账技巧

总账辅助余额表与应收应付模块余额表对账注释:因为应付余额表的查询范围是“不包含未核销的数据”,因此应付余额表的数据是不完整的,所以需要核对的是总账的辅助余额表的期末数和应付模块的余额表的期末数(即使应...
  • redcap2006
  • redcap2006
  • 2008年05月16日 19:33
  • 5679

为什么账龄分析的余额与总账应收账款科目的余额对不上?如何分析?

(1)查看期末总额对账或期末科目对账时,科目选择应收账款科目,检查总账与 应收系统是否一致。如果存在差额,则分析具体是哪个客户,哪项数据不对。 (2)如果期末对账能对上,则检查期末对账中应收系统的...
  • sqlmanage
  • sqlmanage
  • 2011年09月02日 17:39
  • 3069

NC账簿套打设置流程

  • 2013年08月02日 08:56
  • 2.08MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:用友--客户科目余额表sql语句:注意两条语句结果集的合并
举报原因:
原因补充:

(最多只允许输入30个字)