|
16.05.2016, 16:16
#39236721
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Получился не очень через селектом но пока работает
теперь как добавить накопительный баланс как в пример
вот что получилось без накопительного баланса
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
select 'Приход',
' ' as "January",
' ' as "February"
from rdb$database
union all
select a.source_id,
sum(decode(extract(month from a.paydate),1,(a.amt),0)) as "January",
sum(decode(extract(month from a.paydate),2,(a.amt),0)) as "February"
from TPAYMENTS_TEST a
where extract(year from a.paydate)='2016'
and a.paytype=1
group by a.source_id
union all
select 'Расход',
' ' as "January",
' ' as "February"
from rdb$database
union all
select a.source_id,
sum(decode(extract(month from a.paydate),1,(a.amt),0)) as "January",
sum(decode(extract(month from a.paydate),2,(a.amt),0)) as "February"
from TPAYMENTS_TEST a
where extract(year from a.paydate)='2016'
and a.paytype=-1
group by a.source_id
union all
select 'Итог',
' ' as "January",
' ' as "February"
from rdb$database
union all
select 'Сумма прихода',
sum(decode(extract(month from a.paydate),1,(a.amt),0)) as "January",
sum(decode(extract(month from a.paydate),2,(a.amt),0)) as "February"
from TPAYMENTS_TEST a
where extract(year from a.paydate)='2016'
and a.paytype=1
union all
select 'Сумма расхода',
sum(decode(extract(month from a.paydate),1,(a.amt),0)) as "January",
sum(decode(extract(month from a.paydate),2,(a.amt),0)) as "February"
from TPAYMENTS_TEST a
where extract(year from a.paydate)='2016'
and a.paytype=-1
union all
select 'Сальдо',
sum(decode(extract(month from a.paydate),1,(a.paytype*a.amt),0)) as "January",
sum(decode(extract(month from a.paydate),2,(a.paytype*a.amt),0)) as "February"
from TPAYMENTS_TEST a
where extract(year from a.paydate)='2016'
Если есть другой более оптимальный вариант покажите
заранее благодарен |
|
|
