Search
Search

# average for each contragentid

Hi all! This code works fine, it returns proper average value for contragentid = 1. So, correct result is LUAHPER = 14996837,94

But when I add values for contragentid = 2 (see commented strings), my statement returns incorrect result LUAHPER = 5497801,81

How do I improve my code in order to calculate average for each contragentid?

```
with t as (

select to_date('12.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,14275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('14.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,14275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('15.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,14275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('16.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,15274795.50 as luah, 215274795.50 as lusd from dual
union all
select to_date('17.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,15431807.40 as luah, 215431807.40 as lusd from dual
union all
select to_date('18.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,15480730.00 as luah, 215480730 as lusd from dual
union all
select to_date('21.03.2011','dd.mm.yyyy') as arcdate, 1 as contragentid,15480730.00 as luah, 215480730 as lusd from dual

/*union all
select to_date('12.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,214275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('14.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,214275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('15.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,214275303.54 as luah, 214275303.54 as lusd from dual
union all
select to_date('16.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,215274795.50 as luah, 215274795.50 as lusd from dual
union all
select to_date('17.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,215431807.40 as luah, 215431807.40 as lusd from dual
union all
select to_date('18.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,215480730 as luah, 215480730 as lusd from dual
union all
select to_date('21.03.2011','dd.mm.yyyy') as arcdate, 2 as contragentid,215480730 as luah, 215480730 as lusd from dual*/
)

select contragentid, sum(luahper) / cnt as luahper
from (
select contragentid, (lead(arcdate,1,date '2011-03-20' + 1) over(order by arcdate) - arcdate) * luah luahper,
date '2011-03-20' - date '2011-03-13' + 1 cnt
from (
select arcdate, contragentid, luah, lusd
from

t

where arcdate > date '2011-03-13'
and arcdate <= date '2011-03-20'
union all
select greatest(arcdate,date '2011-03-13'),
contragentid, luah, lusd
from

t

where arcdate = (select max(arcdate) from

t

where arcdate <= date '2011-03-13')
) )
where contragentid = 1
group by contragentid, cnt

```

##### Former MemberJune 14, 2011 at 12:12 PM0 Likes
View more on this topic or