用友数据--U8---------
USE UFDATA_010_2010
--资产负债表----------------------------
select * from code where ccode='113' order by ccode
SELECT (select ccode_name from code where ccode=a.ccode) ccode_name,a.* FROM gl_accsum a where a.ccode='113' order by a.ccode
--年初
SELECT sum((CASE WHEN gl_accsum.cbegind_c<>N'贷' THEN gl_accsum.mb ELSE -gl_accsum.mb End)) as SumVal
FROM code INNER JOIN gl_accsum ON code.ccode = gl_accsum.ccode
WHERE gl_accsum.iperiod=1 and gl_accsum.ccode = N'113'
--期末
SELECT sum((CASE WHEN gl_accsum.cendd_c<>N'贷' THEN gl_accsum.me ELSE -gl_accsum.me End)) as SumVal
FROM code INNER JOIN gl_accsum ON code.ccode = gl_accsum.ccode
WHERE gl_accsum.iperiod=11 and gl_accsum.ccode = N'113'
--利润表----------------------------
SELECT (select ccode_name from code where ccode=a.ccode) ccode_name,a.* FROM gl_accvouch a where a.ccode='502' order by a.ccode
--本月数----50101,50102都统计在501里面
SELECT sum((CASE WHEN 1=1 THEN gl_accvouch.md ELSE gl_accvouch.mc End)) as SumVal
FROM code INNER JOIN gl_accvouch ON code.ccode = gl_accvouch.ccode Inner Join DSign On gl_accvouch.isignseq=DSign.isignseq
WHERE gl_accvouch.iperiod>=11 AND gl_accvouch.iperiod<=11 AND gl_accvouch.iflag is null
AND gl_accvouch.ccode = N'501' AND GL_accvouch.ibook=1
--本年数----50101,50102都统计在501里面
SELECT sum((CASE WHEN 1=1 THEN gl_accvouch.md ELSE gl_accvouch.mc End)) as SumVal
FROM code INNER JOIN gl_accvouch ON code.ccode = gl_accvouch.ccode Inner Join DSign On gl_accvouch.isignseq=DSign.isignseq
WHERE gl_accvouch.iperiod>=1 AND gl_accvouch.iperiod<=11 AND gl_accvouch.iflag is null
AND gl_accvouch.ccode = N'501' AND GL_accvouch.ibook=1
--现金流量表----------------------------
--借--本期
select sum(case when b.bCashItem=0 then b.mc else b.md end ) as sumval from (select code.bCashItem,gl_cashtable.*,gl_accvouch.iflag,gl_accvouch.ibook from gl_cashtable inner join gl_accvouch on gl_cashtable.iperiod=gl_accvouch.iperiod and gl_cashtable.isignseq=gl_accvouch.isignseq and gl_cashtable.inid=gl_accvouch.inid and gl_cashtable.ino_id= gl_accvouch.ino_id left join code on gl_cashtable.ccode=code.ccode Union select code.bCashItem,gl_cashtable.*,null as iflag,1 as ibook from gl_cashtable inner join code on gl_cashtable.ccode=code.ccode where isignseq is null and ino_id is null and inid is null ) as b left Join DSign On b.isignseq=DSign.isignseq
where b.iperiod>=5 AND b.iperiod<=5 and b.cCashItem=N'03' and b.iflag is null and b.ibook=1 and b.iperiod<>0 and b.iperiod<>13
--借--上期
select sum(case when b.bCashItem=0 then b.mc else b.md end ) as sumval from (select code.bCashItem,gl_cashtable.*,gl_accvouch.iflag,gl_accvouch.ibook from gl_cashtable inner join gl_accvouch on gl_cashtable.iperiod=gl_accvouch.iperiod and gl_cashtable.isignseq=gl_accvouch.isignseq and gl_cashtable.inid=gl_accvouch.inid and gl_cashtable.ino_id= gl_accvouch.ino_id left join code on gl_cashtable.ccode=code.ccode Union select code.bCashItem,gl_cashtable.*,null as iflag,1 as ibook from gl_cashtable inner join code on gl_cashtable.ccode=code.ccode where isignseq is null and ino_id is null and inid is null ) as b left Join DSign On b.isignseq=DSign.isignseq
where b.iperiod>=1 AND b.iperiod<=5 and b.cCashItem=N'03' and b.iflag is null and b.ibook=1 and b.iperiod<>0 and b.iperiod<>13
--贷--本期
select sum(case when b.bCashItem=0 then b.md else b.mc end) as sumval from (select code.bCashItem,gl_cashtable.*,gl_accvouch.iflag,gl_accvouch.ibook from gl_cashtable inner join gl_accvouch on gl_cashtable.iperiod=gl_accvouch.iperiod and gl_cashtable.isignseq=gl_accvouch.isignseq and gl_cashtable.inid=gl_accvouch.inid and gl_cashtable.ino_id= gl_accvouch.ino_id left join code on gl_cashtable.ccode=code.ccode Union select code.bCashItem,gl_cashtable.*,null as iflag,1 as ibook from gl_cashtable inner join code on gl_cashtable.ccode=code.ccode where isignseq is null and ino_id is null and inid is null ) as b left Join DSign On b.isignseq=DSign.isignseq
where b.iperiod>=5 AND b.iperiod<=5 and b.cCashItem=N'07' and b.iflag is null and b.ibook=1 and b.iperiod<>0 and b.iperiod<>13
--贷--上期
select sum(case when b.bCashItem=0 then b.md else b.mc end) as sumval from (select code.bCashItem,gl_cashtable.*,gl_accvouch.iflag,gl_accvouch.ibook from gl_cashtable inner join gl_accvouch on gl_cashtable.iperiod=gl_accvouch.iperiod and gl_cashtable.isignseq=gl_accvouch.isignseq and gl_cashtable.inid=gl_accvouch.inid and gl_cashtable.ino_id= gl_accvouch.ino_id left join code on gl_cashtable.ccode=code.ccode Union select code.bCashItem,gl_cashtable.*,null as iflag,1 as ibook from gl_cashtable inner join code on gl_cashtable.ccode=code.ccode where isignseq is null and ino_id is null and inid is null ) as b left Join DSign On b.isignseq=DSign.isignseq
where b.iperiod>=1 AND b.iperiod<=5 and b.cCashItem=N'07' and b.iflag is null and b.ibook=1 and b.iperiod<>0 and b.iperiod<>13