Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

insert @PROMPT in proper way

Hi all!

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

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question