How do I edit the code that calculates the value for the four weeks of the month for all months with-CodePudding

I divided the month into four weeks and printed the amount for each week. How do I set this up with a loop for 12 months?

declare 
    cursor c is 
    select varis_tar, tutar 
  from muhasebe.doviz_takip
  where trunc(varis_tar) BETWEEN TO_DATE('01/10/2021', 'DD/MM/YYYY') AND
  TO_DATE('31/10/2021', 'DD/MM/YYYY')
  group by varis_tar,tutar;
  tutar1 number(13,2):=0; 
  tutar2 number(13,2):=0; 
  tutar3 number(13,2):=0;
  tutar4 number(13,2):=0; 

begin  
    
 for r in c loop

    if r.varis_tar between   TO_DATE('01/10/2021', 'DD/MM/YYYY') AND
  TO_DATE('07/10/2021', 'DD/MM/YYYY') then  
    tutar1:=(r.tutar) tutar1;
    --message(r.tutar);
    
    elsif r.varis_tar between   TO_DATE('07/10/2021', 'DD/MM/YYYY') AND
  TO_DATE('14/10/2021', 'DD/MM/YYYY') then 
    tutar2:=(r.tutar) tutar2;
  --message(r.tutar);
    
    elsif r.varis_tar between   TO_DATE('14/10/2021', 'DD/MM/YYYY') AND
  TO_DATE('21/10/2021', 'DD/MM/YYYY') then 
    tutar3:=(r.tutar) tutar3;
  --message(r.tutar);
    
    elsif r.varis_tar between   TO_DATE('21/10/2021', 'DD/MM/YYYY') AND
  TO_DATE('31/10/2021', 'DD/MM/YYYY') then 
    tutar4:=(r.tutar) tutar4;
    --message(r.tutar);

    end if;
 end loop;

I tried to get the dates the same way for all the months. I tried that, but it worked wrong.

  where trunc(varis_tar) BETWEEN TO_DATE('1', 'DD') AND
  TO_DATE('31', 'DD')

    if r.varis_tar between   TO_DATE('1', 'DD') AND
  TO_DATE('07', 'DD') then  
    
    elsif r.varis_tar between   TO_DATE('7', 'DD') AND
  TO_DATE('14', 'DD') then 
    
    elsif r.varis_tar between   TO_DATE('14', 'DD') AND
  TO_DATE('21', 'DD') then 
    
    elsif r.varis_tar between   TO_DATE('21', 'DD') AND
  TO_DATE('31', 'DD') then

CodePudding user response：

I don't know if I'am understanding it correctly but:

try if extract(day from varis_tar) between 1 and 7

or more complex

l_week := to_char(varis_tar,'W'); --week number
if l_week = 1 then --first week
elsif l_week = 2 etc...

CodePudding user response：

Your code has several issues:

date in Oracle is actually a datetime, so between will not count any time after the midnight of the upper boundary.
you count the midnight of the week's end twice: in current week and in the next week (between includes both boundaries).
you do not need any PL/SQL and especially a cursor loop, because it occupy resources during calculation outside of SQL context.

Use datetime format to calculate weeks, because it is easy to read and understand. Then group by corresponding components.

with a as (
  select
    date '2021-01-01' - 1   level as dt
    , level as val
  from dual
  connect by level < 400
)
, b as (
select
  dt
  , val
  /*Map 29, 30 and 31 to 28*/
  , to_char(
      least(dt, trunc(dt, 'mm')   27)
      , 'yyyymmw'
  ) as w
from a
)
select
  substr(w, 1, 4) as y
  , substr(w, 5, 2) as m
  , substr(w, -1) as w
  , sum(val) as val
  , min(dt) as dt_from
  , max(dt) as dt_to
from b
group by
  w

Y    | M  | W  |  VAL | DT_FROM    | DT_TO     
:--- | :- | :- | ---: | :--------- | :---------
2021 | 01 | 1  |   28 | 2021-01-01 | 2021-01-07
2021 | 01 | 2  |   77 | 2021-01-08 | 2021-01-14
2021 | 01 | 3  |  126 | 2021-01-15 | 2021-01-21
2021 | 01 | 4  |  265 | 2021-01-22 | 2021-01-31
2021 | 02 | 1  |  245 | 2021-02-01 | 2021-02-07
2021 | 02 | 2  |  294 | 2021-02-08 | 2021-02-14
2021 | 02 | 3  |  343 | 2021-02-15 | 2021-02-21
2021 | 02 | 4  |  392 | 2021-02-22 | 2021-02-28
2021 | 03 | 1  |  441 | 2021-03-01 | 2021-03-07
2021 | 03 | 2  |  490 | 2021-03-08 | 2021-03-14
2021 | 03 | 3  |  539 | 2021-03-15 | 2021-03-21
2021 | 03 | 4  |  855 | 2021-03-22 | 2021-03-31
2021 | 04 | 1  |  658 | 2021-04-01 | 2021-04-07
2021 | 04 | 2  |  707 | 2021-04-08 | 2021-04-14
2021 | 04 | 3  |  756 | 2021-04-15 | 2021-04-21
2021 | 04 | 4  | 1044 | 2021-04-22 | 2021-04-30
2021 | 05 | 1  |  868 | 2021-05-01 | 2021-05-07
2021 | 05 | 2  |  917 | 2021-05-08 | 2021-05-14
2021 | 05 | 3  |  966 | 2021-05-15 | 2021-05-21
2021 | 05 | 4  | 1465 | 2021-05-22 | 2021-05-31
2021 | 06 | 1  | 1085 | 2021-06-01 | 2021-06-07
2021 | 06 | 2  | 1134 | 2021-06-08 | 2021-06-14
2021 | 06 | 3  | 1183 | 2021-06-15 | 2021-06-21
2021 | 06 | 4  | 1593 | 2021-06-22 | 2021-06-30
2021 | 07 | 1  | 1295 | 2021-07-01 | 2021-07-07
2021 | 07 | 2  | 1344 | 2021-07-08 | 2021-07-14
2021 | 07 | 3  | 1393 | 2021-07-15 | 2021-07-21
2021 | 07 | 4  | 2075 | 2021-07-22 | 2021-07-31
2021 | 08 | 1  | 1512 | 2021-08-01 | 2021-08-07
2021 | 08 | 2  | 1561 | 2021-08-08 | 2021-08-14
2021 | 08 | 3  | 1610 | 2021-08-15 | 2021-08-21
2021 | 08 | 4  | 2385 | 2021-08-22 | 2021-08-31
2021 | 09 | 1  | 1729 | 2021-09-01 | 2021-09-07
2021 | 09 | 2  | 1778 | 2021-09-08 | 2021-09-14
2021 | 09 | 3  | 1827 | 2021-09-15 | 2021-09-21
2021 | 09 | 4  | 2421 | 2021-09-22 | 2021-09-30
2021 | 10 | 1  | 1939 | 2021-10-01 | 2021-10-07
2021 | 10 | 2  | 1988 | 2021-10-08 | 2021-10-14
2021 | 10 | 3  | 2037 | 2021-10-15 | 2021-10-21
2021 | 10 | 4  | 2995 | 2021-10-22 | 2021-10-31
2021 | 11 | 1  | 2156 | 2021-11-01 | 2021-11-07
2021 | 11 | 2  | 2205 | 2021-11-08 | 2021-11-14
2021 | 11 | 3  | 2254 | 2021-11-15 | 2021-11-21
2021 | 11 | 4  | 2970 | 2021-11-22 | 2021-11-30
2021 | 12 | 1  | 2366 | 2021-12-01 | 2021-12-07
2021 | 12 | 2  | 2415 | 2021-12-08 | 2021-12-14
2021 | 12 | 3  | 2464 | 2021-12-15 | 2021-12-21
2021 | 12 | 4  | 3605 | 2021-12-22 | 2021-12-31
2022 | 01 | 1  | 2583 | 2022-01-01 | 2022-01-07
2022 | 01 | 2  | 2632 | 2022-01-08 | 2022-01-14
2022 | 01 | 3  | 2681 | 2022-01-15 | 2022-01-21
2022 | 01 | 4  | 3915 | 2022-01-22 | 2022-01-31
2022 | 02 | 1  | 1194 | 2022-02-01 | 2022-02-03

db<>fiddle here

Or the same in columns:

with a as (
  select
    date '2021-01-01' - 1   level as dt
    , level as val
  from dual
  connect by level < 400
)
, b as (
select
  val
  /*Map 29, 30 and 31 to 28*/
  , to_char(dt, 'yyyymm') as m
  , to_char(
      least(dt, trunc(dt, 'mm')   27)
      , 'w'
  ) as w
from a
)
select
  substr(m, 1, 4) as y
  , substr(m, 5, 2) as m
  , tutar1
  , tutar2
  , tutar3
  , tutar4
from b
pivot(
  sum(val)
  for w in (
    1 as tutar1, 2 as tutar2
    , 3 as tutar3, 4 as tutar4
  )
)

Y    | M  | TUTAR1 | TUTAR2 | TUTAR3 | TUTAR4
:--- | :- | -----: | -----: | -----: | -----:
2021 | 01 |     28 |     77 |    126 |    265
2021 | 02 |    245 |    294 |    343 |    392
2021 | 03 |    441 |    490 |    539 |    855
2021 | 04 |    658 |    707 |    756 |   1044
2021 | 05 |    868 |    917 |    966 |   1465
2021 | 06 |   1085 |   1134 |   1183 |   1593
2021 | 07 |   1295 |   1344 |   1393 |   2075
2021 | 08 |   1512 |   1561 |   1610 |   2385
2021 | 09 |   1729 |   1778 |   1827 |   2421
2021 | 10 |   1939 |   1988 |   2037 |   2995
2021 | 11 |   2156 |   2205 |   2254 |   2970
2021 | 12 |   2366 |   2415 |   2464 |   3605
2022 | 01 |   2583 |   2632 |   2681 |   3915
2022 | 02 |   1194 |   null |   null |   null

db<>fiddle here