Построение отчетов со сложными временными периодами

 oracle sql

 Построение отчетов со сложными временными периодами

(плавающие, нечеткие , временные границы отчета)

 с использованиен иерахических запросов, с конструкцией connect by

 

 подготовим данные для построения наглядного примера

 таблица RMT со следующими полями номер склада, дата изменения остатков, остаток на складе

 

create table RMT( whnum  number,  dataremain DATE,  remain  number);

 

 это могут быть остатки на счете клиента, остатки по биллингу зависит от задачи

 задача достаточно типовая для разных видов деятельности

 правила заполнения таблица следующие, как только меняются остаток добавляется новая запись в таблицу RMT

 заполним таблицу случайными значениями остатков

delete from rmt;

insert into rmt (  select distinct '111',trunc( sysdate - trunc(dbms_random.value*22)), trunc(dbms_random.value*1000,2) from

  dual connect by level < 11 )

 

 задача 1

 отобразить остатки на каждый день заданного периода

 приведем решение задачи ипoльзованием connect by

 для начала построим запрос, который выводит последовательного каждый день указанного периода и отметки на этот день

select dd.daten ,r.remain

 from

(

select trunc(&sDateBegin-1+level) daten

   from dual

connect by level <= (&sDateEnd-&sDateBegin)+1

) dd , rmt r where dd.daten = r.dataremain(+)

-- результат

--25.08.2012      812,8

--26.08.2012      728,12

--27.08.2012      850,47

--28.08.2012      229,19

--29.08.2012     

--30.08.2012     

--31.08.2012     

--01.09.2012     

--02.09.2012     

--03.09.2012     

--04.09.2012      854,91

-- но следует еще и заполнить пробелы между суммами остатков

select dd.daten , nvl(r.remain,(select remain from (select * from

rmt t order by t.dataremain desc) tt where (tt.dataremain)<daten and rownum<2)) rmn

 from

(

select trunc(&sDateBegin-1+level) daten

   from dual

connect by level <= (&sDateEnd-&sDateBegin)+1

) dd , rmt r where dd.daten = r.dataremain(+)

--25.08.2012      812,8

--26.08.2012      728,12

--27.08.2012      850,47

--28.08.2012      229,19

--29.08.2012      229,19

--30.08.2012      229,19

--31.08.2012      229,19

--01.09.2012      229,19

--02.09.2012      229,19

--03.09.2012      229,19

--04.09.2012      854,91

-- задача выполнена

 

 

 Задача 2

 отобразить изменение остатков на конец месяца и на начало месяца

 это продолжение предыдущей задачи

 Необходимо добавить признак конца месяца, а так же признак начала месяца

 используем для этого сделанный нами запрос

 и расширим несколько временной диапазон

with q as

(select decode(daten,trunc(daten,'MM'),1) fn

, decode(last_day(daten),daten,1) em, dd.daten

, nvl(r.remain,(select remain from

  (

   select * from

    rmt t order by t.dataremain desc

  ) tt where (tt.dataremain)<daten and rownum<2)) rmn

 from

(

select trunc(&sDateBegin-1+level) daten

   from dual

connect by level <= (&sDateEnd-&sDateBegin)+1

) dd , rmt r where dd.daten = r.dataremain(+))

select * from q where fn=1 or em=1

--    1     31.05.2012 

--1         01.06.2012 

--    1     30.06.2012 

--1         01.07.2012 

--    1     31.07.2012 

--1         01.08.2012 

--    1     31.08.2012  229,19

--1         01.09.2012  229,19

 

 Задача3

 отобразить изменение остатков на конец месяца ,на начало месяца а так же на дату изменения остатков

 построить периоды смены остатков

 добавим в наш запрос данные по числам когда менялись остатки

 для этого используем аналитическую функцию, а так же несколько условий по смене периода

 получим следующий запрос

with q as

(

 select decode(daten,trunc(daten,'MM'),1) fd

      , decode(last_day(daten),daten,1) ld, dd.daten

      , nvl(r.remain,(select remain from

           (

             select * from  rmt t order by t.dataremain desc

           ) tt where (tt.dataremain)<daten and rownum<2)

           ) rmn

 from

   (

     select trunc(&sDateBegin-1+level) daten

      from dual

    connect by level <= (&sDateEnd-&sDateBegin)+1

   ) dd , rmt r where dd.daten = r.dataremain(+))

   select daten, next_daten, rmn from (

            select decode(ld,1, decode(nnn,1,daten,null)

             , decode(LEAD(ld) over(order by daten)||decode(LEAD(nnn) over(order by daten),1,1,0),'10'

             , LEAD(daten) over(order by daten)

             , LEAD(daten) over(order by daten)-1)) next_daten -- дата конца периода

             , ttt.*  from

            (

             select DENSE_RANK() OVER(PARTITION BY rmn ORDER BY daten) nnn,q.* from q

            ) ttt where  (nnn = 1 or ld = 1 or fd = 1)  order by daten -- выбираем нач мес , кон мес , ранж остатки

            )

            where next_daten is not null

-- результат           

--29.05.2012      31.05.2012 

--01.06.2012      30.06.2012 

--01.07.2012      31.07.2012 

--01.08.2012      15.08.2012 

--16.08.2012      16.08.2012  511,58

--17.08.2012      17.08.2012  58,17

--18.08.2012      20.08.2012  649,77

--21.08.2012      22.08.2012  399,42

--23.08.2012      24.08.2012  704,61

--25.08.2012      25.08.2012  812,8

--26.08.2012      26.08.2012  728,12

--27.08.2012      27.08.2012  850,47

--28.08.2012      31.08.2012  229,19

--01.09.2012      03.09.2012  229,19

 {jcomments on}