Skip to Content
avatar image
Former Member

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Jun 14, 2011 at 10:12 AM

    My code works fine, help me insert in proper way

    @prompt('b. begin date:','D',,Mono,Free,Persistent,,User:0)

    @prompt('a. end date:','D',,Mono,Free,Persistent,,User:0)

    instead of '2011-03-13', '2011-03-20'

    
    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, SUM(lusdper) / cnt AS lusdper
      FROM (SELECT contragentid
                  ,(lead(arcdate, 1, DATE '2011-03-20' + 1) over(PARTITION BY contragentid ORDER BY arcdate) - arcdate) * luah luahper
                  ,(lead(arcdate, 1, DATE '2011-03-20' + 1) over(PARTITION BY contragentid ORDER BY arcdate) - arcdate) * lusd lusdper
                  ,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')
                   )
           )
     GROUP BY contragentid, cnt 
    
    

    Add comment
    10|10000 characters needed characters exceeded