17,140
社区成员




select y.branid,
MAX(z.branname) branname,
SUM(y.amt1) amt1,
SUM(y.amt2) amt2,
SUM(y.amt2 - y.amt1) zjamt,
DECODE(SUM(y.amt1),
0,
0,
SUM(y.amt2 - y.amt1) / SUM(y.amt1) * 100) zjf
FROM (SELECT c.branid, b.taxexpensemoney amt1, 0 amt2
FROM bud_charge_expense a,
bud_charge_expensedetail b,
(SELECT a.branid branid
FROM sys_bran a
START WITH a.branid in
(SELECT branid
FROM bud_rpt_brangroupdtl
WHERE 1 = 1
and brangroupno = '1111')
CONNECT BY PRIOR a.branid = a.pbranid) c
WHERE a.expenseid = b.expenseid
AND a.usestatus = 8
AND b.useorgid IN
(SELECT branid FROM sys_bran_user WHERE userid = 1)
AND b.useorgid = c.branid
AND a.expensedate BETWEEN
to_date('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
UNION ALL
SELECT c.branid, 0 amt1, b.taxexpensemoney amt2
FROM bud_charge_expense a,
bud_charge_expensedetail b,
(SELECT a.branid branid
FROM sys_bran a
START WITH a.branid in
(SELECT branid
FROM bud_rpt_brangroupdtl
WHERE 1 = 1
and brangroupno = '1111')CONNECT BY PRIOR a.branid = a.pbranid) c
WHERE a.expenseid = b.expenseid
AND a.usestatus = 8
AND b.useorgid IN
(SELECT branid FROM sys_bran_user WHERE userid = 1)
AND b.useorgid = c.branid
AND a.expensedate BETWEEN
to_date('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) y,
sys_bran z
WHERE y.branid = z.branid
GROUP BY y.branid
select sum(c.amt1) amt1,
sum(c.amt2) amt2,
sum(c.amt2 - c.amt1) zjamt,
case
when (decode(SUM(c.amt1),
0,
1,
SUM((c.amt2 - c.amt1)) / sum(c.amt1)) * 100) = 0 then
'0' || '%'
else
to_char(decode(sum(c.amt1),
0,
1,
(SUM((c.amt2 - c.amt1)) / sum(c.amt1)) * 100),
'fm999999990.00') || '%'
end zjf
from (select y.branid,
MAX(z.branname) branname,
SUM(y.amt1) amt1,
SUM(y.amt2) amt2,
SUM(y.amt2 - y.amt1) zjamt,
DECODE(SUM(y.amt1),
0,
0,
SUM(y.amt2 - y.amt1) / SUM(y.amt1) * 100) zjf
FROM (SELECT c.branid, b.taxexpensemoney amt1, 0 amt2
FROM bud_charge_expense a,
bud_charge_expensedetail b,
(SELECT a.branid branid
FROM sys_bran a
where a.branid in
(SELECT branid
FROM bud_rpt_brangroupdtl
WHERE 1 = 1
and brangroupno = '1111')) c
WHERE a.expenseid = b.expenseid
AND a.usestatus = 8
AND b.useorgid IN
(SELECT branid FROM sys_bran_user WHERE userid = 1)
AND b.useorgid = c.branid
AND a.expensedate BETWEEN
to_date('2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
UNION ALL
SELECT c.branid, 0 amt1, b.taxexpensemoney amt2
FROM bud_charge_expense a,
bud_charge_expensedetail b,
(SELECT a.branid branid
FROM sys_bran a
where a.branid in
(SELECT branid
FROM bud_rpt_brangroupdtl
WHERE 1 = 1
and brangroupno = '1111')) c
WHERE a.expenseid = b.expenseid
AND a.usestatus = 8
AND b.useorgid IN
(SELECT branid FROM sys_bran_user WHERE userid = 1)
AND b.useorgid = c.branid
AND a.expensedate BETWEEN
to_date('2013-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
to_date('2013-07-30 23:59:59', 'YYYY-MM-DD HH24:MI:SS')) y,
sys_bran z
WHERE y.branid = z.branid
GROUP BY y.branid) c