Home » SQL & PL/SQL » SQL & PL/SQL » Logic to apportion given value among 2 dates (Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production)
Logic to apportion given value among 2 dates [message #362629] Wed, 03 December 2008 07:38 Go to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Hi guys,
I have this scenario, I have a table which has a "RA", "Start date", "End date", "EFF_DAYS" and also columns like "mon", "mon1", "mon2", "tue", tue1", etc

My problem:
Given : 
start date SD, 
end date ED, 
days - number of workdays between SD and ED (i.e no Saturdays or sundays)
"amt" - Value that needs to be distributed .


I need to distribute the given "amt" to all the workdays between SD and ED.

Structure of the table:
RA                              NOT NULL  NUMBER(14,3)
RL                              NOT NULL  NUMBER(6,0)
DR                                        NUMBER(6,0)
Start_Date                                DATE
End_date                                    DATE
EFF_DAYS                                  NUMBER(4,0)
DAILY_RA                                  NUMBER(14,3)
DAYS_IN_PRD                               NUMBER(4,0)
SW_FCST_SD                                DATE
SW_FCST_ED                                DATE
W1_MON                                    NUMBER(4,2)
W1_TUE                                    NUMBER(4,2)
W1_WED                                    NUMBER(4,2)
.
.
.


EX: If there is a "amt" of 8, and the SD and ED is 01-DEC-08 and ED is 12-DEC-08, then

W1_MON = 8,
W1_TUE = 8,
W1_WED = 8
..
..
..
W2_THU = 8,
W2_FRI = 8




Re: Logic to apportion given value among 2 dates [message #362630 is a reply to message #362629] Wed, 03 December 2008 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Before anyone attempts to give you answer to that you're going to have to give us the complete definition of that table (all columns and primary key for starters).
A set of insert statements to populate it would also be exceedingly usefull.
Re: Logic to apportion given value among 2 dates [message #362631 is a reply to message #362629] Wed, 03 December 2008 07:48 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
And are your column names really in mixed case? Bad design.
Re: Logic to apportion given value among 2 dates [message #362633 is a reply to message #362631] Wed, 03 December 2008 07:55 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
1. At the current situation i am in, i am unable to produce sample data and inserts stmts.

2. The column names will anyways be stored in UPPER CASE in the DDL, i have typed a few and copied a few.

3rd and final, it would be good if any of you guys can please help out.

Smile
Re: Logic to apportion given value among 2 dates [message #362634 is a reply to message #362633] Wed, 03 December 2008 07:58 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
jagannathkiran wrote on Wed, 03 December 2008 07:55
1. At the current situation i am in, i am unable to produce sample data and inserts stmts.



That is not really an acceptable response. You want help but you won't provide sample data? Is it financial data? Then why not try to think outside the box and produce bogus data?
Re: Logic to apportion given value among 2 dates [message #362635 is a reply to message #362634] Wed, 03 December 2008 08:04 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
It's not financial data , i wam running short of time, anyways please give me a few minutes, i will come up with the data set
Re: Logic to apportion given value among 2 dates [message #362638 is a reply to message #362635] Wed, 03 December 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take all the time you need to provide us the requested information.

Regards
Michel
Re: Logic to apportion given value among 2 dates [message #362639 is a reply to message #362635] Wed, 03 December 2008 08:28 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member

CREATE TABLE TEST_TABLE
    (
    daily_ra                       NUMBER(14,3) NOT NULL,
    days_in_prd                    NUMBER(4,0)  NOT NULL,
    sw_fcst_sd                     DATE         NOT NULL,
    sw_fcst_ed                     DATE         NOT NULL,
    w1_mon                         NUMBER(4,2),
    w1_tue                         NUMBER(4,2),
    w1_wed                         NUMBER(4,2),
    w1_thu                         NUMBER(4,2),
    w1_fri                         NUMBER(4,2),
    w2_mon                         NUMBER(4,2),
    w2_tue                         NUMBER(4,2),
    w2_wed                         NUMBER(4,2),
    w2_thu                         NUMBER(4,2),
    w2_fri                         NUMBER(4,2),
    w3_mon                         NUMBER(4,2),
    w3_tue                         NUMBER(4,2),
    w3_wed                         NUMBER(4,2),
    w3_thu                         NUMBER(4,2),
    w3_fri                         NUMBER(4,2),
    w4_mon                         NUMBER(4,2),
    w4_tue                         NUMBER(4,2),
    w4_wed                         NUMBER(4,2),
    w4_thu                         NUMBER(4,2),
    w4_fri                         NUMBER(4,2),
    w5_mon                         NUMBER(4,2),
    w5_tue                         NUMBER(4,2),
    w5_wed                         NUMBER(4,2),
    w5_thu                         NUMBER(4,2),
    w5_fri                         NUMBER(4,2));

/************************/

insert into test_table(daily_ra, days_in_prd, sw_fcst_sd, sw_fcst_ed) 
values(4.32,20,'01-DEC-2008','26-DEC-2008');
insert into test_table(daily_ra, days_in_prd, sw_fcst_sd, sw_fcst_ed) 
values(156,20,'01-DEC-2008','26-DEC-2008');
insert into test_table(daily_ra, days_in_prd, sw_fcst_sd, sw_fcst_ed) 
values(48,20,'01-DEC-2008','26-DEC-2008');
insert into test_table(daily_ra, days_in_prd, sw_fcst_sd, sw_fcst_ed) 
values(34.14,15,'03-NOV-2008','21-NOV-2008');
/***************************/

Select * from test_table;

/*******************************/

I want to write a procedure that updates the above table such that for example in the last row (4th insert) , the RA of 34.14 should be set to 34.14 for the 1st 3 weeks of November 2008 (only workdays)


[Updated on: Wed, 03 December 2008 08:35] by Moderator

Report message to a moderator

Re: Logic to apportion given value among 2 dates [message #362643 is a reply to message #362639] Wed, 03 December 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is start_date a Monday each time?

Regards
Michel

[Updated on: Wed, 03 December 2008 08:38]

Report message to a moderator

Re: Logic to apportion given value among 2 dates [message #362644 is a reply to message #362635] Wed, 03 December 2008 08:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, I wouldn't touch that data model with a 10' pole.

I'd do something like this to generate the data, and then backfill that denormalised disaster area with the data.:
with src as (select 1000 amt
                   ,trunc(sysdate)-30 from_date
                   ,trunc(sysdate)    to_date
             from dual)
select work_date
      ,to_char(work_date,'DY','NLS_DATE_LANGUAGE=english') work_day
      ,week
      ,per_day
      ,amt
from  (select from_date + level work_date
             ,to_number(to_char(from_date + level,'IW')) - to_number(to_char(from_date,'IW')) +1 week
             ,round(amt/(to_date-from_date+1),2) per_day
             ,amt
       from src connect by level <= to_date - from_date +1)
where  to_char(work_date,'DY','NLS_DATE_LANGUAGE=english') not in ('SAT','SUN');
Re: Logic to apportion given value among 2 dates [message #362645 is a reply to message #362643] Wed, 03 December 2008 08:39 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Not necessarily sorry please put this case in too :

start and dates can be anywhere:

NOTE: the 2nd column gives us the number of workdays between the 2 dates

insert into test_table(daily_ra, days_in_prd, sw_fcst_sd, sw_fcst_ed) 
values(23.12,10,'05-NOV-2008','18-NOV-2008');

[Updated on: Wed, 03 December 2008 08:40] by Moderator

Report message to a moderator

Re: Logic to apportion given value among 2 dates [message #362646 is a reply to message #362644] Wed, 03 December 2008 08:45 Go to previous messageGo to next message
NewLife
Messages: 170
Registered: April 2008
Senior Member


Hey, that reply really flew over my head, i am still an amateur in PL/SQL, please could you elaborate ?

Re: Logic to apportion given value among 2 dates [message #362647 is a reply to message #362645] Wed, 03 December 2008 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A number(14,3) can't be inserted into a number(4,2).

Regards
Michel
Re: Logic to apportion given value among 2 dates [message #362648 is a reply to message #362629] Wed, 03 December 2008 09:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
This table represents a months worth of weekdays doesn't it?
Re: Logic to apportion given value among 2 dates [message #362653 is a reply to message #362646] Wed, 03 December 2008 10:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member

This clause just provides a data source to select from that returns a single row with columns Amt, From_Date and To_Date
with src as (select 1000 amt
                   ,trunc(sysdate)-30 from_date
                   ,trunc(sysdate)    to_date
             from dual)


This bit is the guts of the query.
It uses a row-generator technique (the Connect by level <= <value> bit) to return a single row with a different value of LEVEL for each date between the From and To dates

select from_date + level work_date
      ,to_number(to_char(from_date + level,'IW')) - to_number(to_char(from_date,'IW')) +1 week
      ,round(amt/(to_date-from_date+1),2) per_day
      ,amt
from src connect by level <= to_date - from_date +1
Re: Logic to apportion given value among 2 dates [message #362654 is a reply to message #362639] Wed, 03 December 2008 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like that (I have to go and didn't check the result in depth but it seems correct):
SQL> update test_table set 
  2    w1_mon = case 
  3               when next_day(sw_fcst_sd+3,'Monday') - 7  + 0*7+0
  4                      between sw_fcst_sd and sw_fcst_ed
  5                 then daily_ra
  6             end,
  7    w1_tue = case 
  8               when next_day(sw_fcst_sd+3,'Monday') - 7  + 0*7+1
  9                      between sw_fcst_sd and sw_fcst_ed
 10                 then daily_ra
 11             end,
 12    w1_wed = case 
 13               when next_day(sw_fcst_sd+3,'Monday') - 7  + 0*7+2
 14                      between sw_fcst_sd and sw_fcst_ed
 15                 then daily_ra
 16             end,
 17    w1_thu = case 
 18               when next_day(sw_fcst_sd+3,'Monday') - 7  + 0*7+3
 19                      between sw_fcst_sd and sw_fcst_ed
 20                 then daily_ra
 21             end,
 22    w1_fri = case 
 23               when next_day(sw_fcst_sd+3,'Monday') - 7  + 0*7+4
 24                      between sw_fcst_sd and sw_fcst_ed
 25                 then daily_ra
 26             end,
 27    w2_mon = case 
 28               when next_day(sw_fcst_sd+3,'Monday') - 7  + 1*7+0
 29                      between sw_fcst_sd and sw_fcst_ed
 30                 then daily_ra
 31             end,
 32    w2_tue = case 
 33               when next_day(sw_fcst_sd+3,'Monday') - 7  + 1*7+1
 34                      between sw_fcst_sd and sw_fcst_ed
 35                 then daily_ra
 36             end,
 37    w2_wed = case 
 38               when next_day(sw_fcst_sd+3,'Monday') - 7  + 1*7+2
 39                      between sw_fcst_sd and sw_fcst_ed
 40                 then daily_ra
 41             end,
 42    w2_thu = case 
 43               when next_day(sw_fcst_sd+3,'Monday') - 7  + 1*7+3
 44                      between sw_fcst_sd and sw_fcst_ed
 45                 then daily_ra
 46             end,
 47    w2_fri = case 
 48               when next_day(sw_fcst_sd+3,'Monday') - 7  + 1*7+4
 49                      between sw_fcst_sd and sw_fcst_ed
 50                 then daily_ra
 51             end,
 52    w3_mon = case 
 53               when next_day(sw_fcst_sd+3,'Monday') - 7  + 2*7+0
 54                      between sw_fcst_sd and sw_fcst_ed
 55                 then daily_ra
 56             end,
 57    w3_tue = case 
 58               when next_day(sw_fcst_sd+3,'Monday') - 7  + 2*7+1
 59                      between sw_fcst_sd and sw_fcst_ed
 60                 then daily_ra
 61             end,
 62    w3_wed = case 
 63               when next_day(sw_fcst_sd+3,'Monday') - 7  + 2*7+2
 64                      between sw_fcst_sd and sw_fcst_ed
 65                 then daily_ra
 66             end,
 67    w3_thu = case 
 68               when next_day(sw_fcst_sd+3,'Monday') - 7  + 2*7+3
 69                      between sw_fcst_sd and sw_fcst_ed
 70                 then daily_ra
 71             end,
 72    w3_fri = case 
 73               when next_day(sw_fcst_sd+3,'Monday') - 7  + 2*7+4
 74                      between sw_fcst_sd and sw_fcst_ed
 75                 then daily_ra
 76             end,
 77    w4_mon = case 
 78               when next_day(sw_fcst_sd+3,'Monday') - 7  + 3*7+0
 79                      between sw_fcst_sd and sw_fcst_ed
 80                 then daily_ra
 81             end,
 82    w4_tue = case 
 83               when next_day(sw_fcst_sd+3,'Monday') - 7  + 3*7+1
 84                      between sw_fcst_sd and sw_fcst_ed
 85                 then daily_ra
 86             end,
 87    w4_wed = case 
 88               when next_day(sw_fcst_sd+3,'Monday') - 7  + 3*7+2
 89                      between sw_fcst_sd and sw_fcst_ed
 90                 then daily_ra
 91             end,
 92    w4_thu = case 
 93               when next_day(sw_fcst_sd+3,'Monday') - 7  + 3*7+3
 94                      between sw_fcst_sd and sw_fcst_ed
 95                 then daily_ra
 96             end,
 97    w4_fri = case 
 98               when next_day(sw_fcst_sd+3,'Monday') - 7  + 3*7+4
 99                      between sw_fcst_sd and sw_fcst_ed
100                 then daily_ra
101             end,
102    w5_mon = case 
103               when next_day(sw_fcst_sd+3,'Monday') - 7  + 4*7+0
104                      between sw_fcst_sd and sw_fcst_ed
105                 then daily_ra
106             end,
107    w5_tue = case 
108               when next_day(sw_fcst_sd+3,'Monday') - 7  + 4*7+1
109                      between sw_fcst_sd and sw_fcst_ed
110                 then daily_ra
111             end,
112    w5_wed = case 
113               when next_day(sw_fcst_sd+3,'Monday') - 7  + 4*7+2
114                      between sw_fcst_sd and sw_fcst_ed
115                 then daily_ra
116             end,
117    w5_thu = case 
118               when next_day(sw_fcst_sd+3,'Monday') - 7  + 4*7+3
119                      between sw_fcst_sd and sw_fcst_ed
120                 then daily_ra
121             end,
122    w5_fri = case 
123               when next_day(sw_fcst_sd+3,'Monday') - 7  + 4*7+4
124                      between sw_fcst_sd and sw_fcst_ed
125                 then daily_ra
126             end
127  /

5 rows updated.

SQL> set lines 90
SQL> set recsep each
SQL> set recsepchar '-'
SQL> select * from test_table;
  DAILY_RA DAYS_IN_PRD SW_FCST_SD  SW_FCST_ED      W1_MON     W1_TUE     W1_WED     W1_THU
---------- ----------- ----------- ----------- ---------- ---------- ---------- ----------
    W1_FRI     W2_MON     W2_TUE     W2_WED     W2_THU     W2_FRI     W3_MON     W3_TUE
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    W3_WED     W3_THU     W3_FRI     W4_MON     W4_TUE     W4_WED     W4_THU     W4_FRI
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
    W5_MON     W5_TUE     W5_WED     W5_THU     W5_FRI
---------- ---------- ---------- ---------- ----------
      4.32          20 01-DEC-2008 26-DEC-2008       4.32       4.32       4.32       4.32
      4.32       4.32       4.32       4.32       4.32       4.32       4.32       4.32
      4.32       4.32       4.32       4.32       4.32       4.32       4.32       4.32

------------------------------------------------------------------------------------------
       156          20 01-DEC-2008 26-DEC-2008        156        156        156        156
       156        156        156        156        156        156        156        156
       156        156        156        156        156        156        156        156

------------------------------------------------------------------------------------------
        48          20 01-DEC-2008 26-DEC-2008         48         48         48         48
        48         48         48         48         48         48         48         48
        48         48         48         48         48         48         48         48

------------------------------------------------------------------------------------------
     34.14          15 03-NOV-2008 21-NOV-2008      34.14      34.14      34.14      34.14
     34.14      34.14      34.14      34.14      34.14      34.14      34.14      34.14
     34.14      34.14      34.14

------------------------------------------------------------------------------------------
     23.12          10 05-NOV-2008 18-NOV-2008                            23.12      23.12
     23.12      23.12      23.12      23.12      23.12      23.12      23.12      23.12


------------------------------------------------------------------------------------------

5 rows selected.

Regards
Michel

[Updated on: Wed, 03 December 2008 10:16]

Report message to a moderator

Re: Logic to apportion given value among 2 dates [message #362659 is a reply to message #362654] Wed, 03 December 2008 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Actually it is:
update test_table set 
  w1_mon = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 0*7+0
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w1_tue = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 0*7+1
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w1_wed = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 0*7+2
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w1_thu = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 0*7+3
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w1_fri = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 0*7+4
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w2_mon = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 1*7+0
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w2_tue = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 1*7+1
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w2_wed = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 1*7+2
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w2_thu = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 1*7+3
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w2_fri = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 1*7+4
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w3_mon = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 2*7+0
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w3_tue = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 2*7+1
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w3_wed = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 2*7+2
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w3_thu = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 2*7+3
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w3_fri = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 2*7+4
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w4_mon = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 3*7+0
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w4_tue = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 3*7+1
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w4_wed = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 3*7+2
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w4_thu = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 3*7+3
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w4_fri = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 3*7+4
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w5_mon = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 4*7+0
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w5_tue = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 4*7+1
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w5_wed = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 4*7+2
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w5_thu = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 4*7+3
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end,
  w5_fri = case 
             when next_day(sw_fcst_sd+2,'Monday') - 7  + 4*7+4
                    between sw_fcst_sd and sw_fcst_ed
               then daily_ra
           end
/

Regards
Michel
Re: Logic to apportion given value among 2 dates [message #362742 is a reply to message #362659] Thu, 04 December 2008 00:49 Go to previous message
NewLife
Messages: 170
Registered: April 2008
Senior Member
Thanks all of you Smile
Razz Razz
Previous Topic: Excluding data
Next Topic: Invoking unix shell script from PL/SQL (merged)
Goto Forum:
  


Current Time: Thu Dec 08 20:10:56 CST 2016

Total time taken to generate the page: 0.11177 seconds