NEED HELP WITH TO BUILT QUERY 1

[Crystalboy1]

3.4.6 On a Friday the report will accrue for three days (ie for Fri, Sat and Sun) using the aggregate repo quantity amount outstanding at CoB Fri.
For example : -
BusinessDay Amount Accrual

Thurs 40 2,739.72
Fri 40 8,219.18 *

Mon 60 3,424.66


* Fri 40 2,739.72
Sat 40 2,739.72
Sun 40 2,739.72

8,219.18



3.4.7 Where the monthend date occurs on a Saturday or Sunday, the accrual on Friday should include the accrual for the 30th and/or 31st , calculated on Fridays CoB position.
For example : -
Sun 31st
BusinesDay Amount DMO Accrual

Thurs 28th 40 2,739.72
Fri 29th 40 8,219.17*
Sat 30th
Sun 31st
Mon 1st 40 2,739.72


* Fri 29th 40 2,739.72
Sat 30th 40 2,739.72
Sun 31st 40 2,739.72
8,219.17



Sun 1st
BusinessDay Amount Accrual


Thurs 29th 40 2,739.72
Fri 30th 40 5,479.45*
Sat 31st
Sun 1st
Mon 2nd 40 5,479.45**

* Fri 30th 40 2,739.72
Sat 31st 40 2,739.72
5,479.45

** Sun 1st 40 2,739.72
Mon 2nd 40 2,739.72
5,479.45




Where the 1st of the month occurs on a Saturday or Sunday, the accrual on Monday should include the accrual for the 1st and/or 2nd, calculated on Fridays CoB position.

Sat 1st
Business Day Amount DMO
Thurs 30th 40 2,739.72
Fri 31st 40 2,739.72
Sat 1st
Sun 2nd
Mon 3rd 40 8,219.16*


* Sat 1st 40 2,739.72
Sun 2nd 40 2,739.72
Mon 3rd 40 2,739.72
8,219.16


Can anyone help me built a SQL query to to select this data and calculate the accrual according to the detials given above.

 

[Dagon]

It would help if:

a) you formatted the output better (use the "code" TGML tags)

b) you used a bit less jargon and explained the problem in terms that someone not familiar with your application can understand.

Is the nub of the problem that you have data like:

Wednesday 28 400
Thursday 29 500
Friday   30 100
Saturday 31  50
Sunday    1 600
Monday    2 100

and you want the figures for the weekend rolled up into the Monday figures e.g.

Wednesday 28 400
Thursday 29 500
Friday   30 150
Monday    2 700

If so, then something like this should work:

create table amounts (thedate date, amount number);

insert into amounts values ('28-oct-09', 400);
insert into amounts values ('29-oct-09', 500);
insert into amounts values ('30-oct-09', 100);
insert into amounts values ('31-oct-09', 50);
insert into amounts values ('01-nov-09', 600);
insert into amounts values ('02-nov-09', 100);

select next_day('31-oct-09', 'Fri')-7 from dual

select acc_date, sum(amount)
from
(select case when to_char(thedate, 'DY') IN ('SAT', 'SUN') then 
                      case when to_char(thedate, 'DD') in (1,2) then next_day(thedate, 'Mon') 
                           else next_day(thedate, 'Fri')-7
                      end
                     else thedate 
       end as acc_date,
       amount
from amounts)
group by acc_date
order by acc_date

 

[Crystalboy1]

thanks for your reply. i want to calculate the accural over the weekend but there are 4 different Scenarios. i will give u some examples to explian.

"Code"
BusinessDay actual(accrual) results (i want)
Thurs 2,739.72 2,739.72
Fri 2,739.72 8,219.18 *
sat
Sun
Mon 2,739.72
"Code"
Explaintion of the above is that the we have multipiled the amount on friday by 3, to get that of satarday and sunday
"Code"
* Fri 2,739.72
Sat 2,739.72
Sun 2,739.72

8,219.18


BusinessDay actual(accrual) results (i want)
Thurs 3oth 2,739.72 2,739.72
Fri 31th 2,739.72 2,739.72
sat 1st
Sun 2nd
Mon 3rd 2,739.72 8,219.18 *
"code"
in the above example, end of month is on firday, so the weekend will be added to that of monday.


"code"

BusinessDay actual(accrual) results (i want)
Thurs 29th 2,739.72 2,739.72
Fri 30th 2,739.72 5479.45 *
sat 31th
Sun 1st
Mon 2nd 2,739.72 5479.45 *
"code"
in the above example, end of month is on satarday, so the weekend will be added to that of monday and friday.


"code"

BusinessDay actual(accrual) results (i want)
Thurs 28th 2,739.72 2,739.72
Fri 29th 2,739.72 8219.17 *
sat 30th
Sun 31st
Mon 1st 2,739.72 2,739.72
"code"

in the above example, end of month is on satrday or sunday, so the weekend will be added to that of friday.

hope this help.

 

[Dagon]

So are there actually any rows in the table for Saturday and Sunday ? Or are you just assuming there will be amount figure that is the same as the last Friday/next Monday ? Are all your amount figures always 2,739.72 ?

 

[Crystalboy1]

No there are no rows in the table for Satarday and Sunday. Yes the figure will be that of the Last friday/Next Monday. Currently the amount is 2739.72 for all the rows but there is a column for it,so i will use that column name in calculation incase it changes in future,

 

[Dagon]

I'll take a stab it with this:

select  thedate,
        case to_char(thedate, 'DY')
        when 'MON' then 
            case when to_char(thedate, 'DD') in (2,3) then  amount * to_number(to_char(thedate, 'DD'))
            else amount
            end
        when 'FRI' then
            case when to_number(to_char(last_day(thedate), 'DD'))-to_number(to_char(thedate, 'DD')) <= 2 then amount * (1+ to_number(to_char(last_day(thedate), 'DD'))-to_number(to_char(thedate, 'DD')))
            else amount * 3
            end     
       else amount 
       end as amount
from amounts

 

[Crystalboy1]

Thanks A lot. that was very helpful.