This discussion is archived
10 Replies Latest reply: Oct 1, 2012 10:40 PM by 892740

# Query or Logic to Find number of Days Across each month.

Currently Being Moderated
Dear friends ,

I need your help in solving this problem.

We have a requierment where we need to pay allowance for the employees based on thier number of working days.

Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.

We have a fixed value for per month 300 Dirams. But the Number of Days on March s 31 and Number of days in April is 30.

So per day allowance for March day would be 300/31 and April would be 300/30.

We are looking for logic opr query which calcluates first eh number of days in each month ( across months) and then calcuate as below

Number of Working days in March is 31 - 3 + 1 = 29

Allowance A1 = (300 * 29 )/31

Number of Working days in April is 5 ( this also needs to find logical I am guess )
Allowance A2 = (300 * 5 )/30

Then A1 + A2.

The A(n) would be the total allowance where provided the number of month across.

Please guys any help would greatly appricated.

R
• ###### 1. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
You can use:
``````SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
31-OCT-12

SQL> select extract(day from last_day(sysdate)) from dual;

EXTRACT(DAYFROMLAST_DAY(SYSDATE))
---------------------------------
31``````
• ###### 2. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
Hi,

from where you pick the date ...from column?

if yes what is the datatype of column ?

IF datatype is date then you can directly subtract two dates :)

select sysdate - to_date('31-mar-2012','dd-mon-yyyy') from dual;

Edited by: Mr. singh on Oct 1, 2012 7:10 PM
• ###### 3. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
This way?
``````with data as
(
select 1000 amt from dual
)
select to_date('01-' || LPAD(level, 2, '0') || '-' || to_char(sysdate, 'YYYY'), 'DD-MM-YYYY') Month,
extract( day from last_day(to_date('01-' || LPAD(level, 2, '0') || '-' || to_char(sysdate, 'YYYY'), 'DD-MM-YYYY'))) month_days,
round(amt/extract( day from last_day(to_date('01-' || LPAD(level, 2, '0') || '-' || to_char(sysdate, 'YYYY'), 'DD-MM-YYYY'))), 2) avg_pay
from data
connect by level <= 12;

MONTH                     MONTH_DAYS             AVG_PAY
------------------------- ---------------------- ----------------------
01-JAN-12                 31                     32.26
01-FEB-12                 29                     34.48
01-MAR-12                 31                     32.26
01-APR-12                 30                     33.33
01-MAY-12                 31                     32.26
01-JUN-12                 30                     33.33
01-JUL-12                 31                     32.26
01-AUG-12                 31                     32.26
01-SEP-12                 30                     33.33
01-OCT-12                 31                     32.26
01-NOV-12                 30                     33.33
01-DEC-12                 31                     32.26

12 rows selected``````
• ###### 4. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
One way could be like this (I added a couple extra sample data to test the logic):
``````SQL> with testdata as (
2     select date '2012-03-03' as d1, date '2012-04-05' as d2 from dual union all
3     select date '2012-03-03' as d1, date '2012-05-02' as d2 from dual union all
4     select date '2012-03-03' as d1, date '2012-03-10' as d2 from dual union all
5     select date '2012-03-01' as d1, date '2012-07-31' as d2 from dual
6  )
7  --
8  -- end of test data --
9  --
10  select 300 * months allowance
11       , months
12    from (
13     select ((extract(day from last_day(d1))-extract(day from d1)+1) / extract(day from last_day(d1)))
14          + (months_between(trunc(d2,'MM'),trunc(d1,'MM'))-1)
15          + (extract(day from d2) / extract(day from last_day(d2))) months
16       from testdata
17         )
18  /

ALLOWANCE     MONTHS
---------- ----------
330.645161 1.10215054
600          2
77.4193548 .258064516
1500          5``````
The way this works is:
``((extract(day from last_day(d1))-extract(day from d1)+1) / extract(day from last_day(d1)))``
That line finds the number of days from d1 to the end of the month and divides by number of days in the month. So it becomes the fraction of a month from d1 to the end of the month.
``(months_between(trunc(d2,'MM'),trunc(d1,'MM'))-1)``
That line finds the number of "whole months" between the two dates.
``(extract(day from d2) / extract(day from last_day(d2))) months``
And finally that line takes the day of d2 and divides by number of days in that month. So it becomes the fraction of a month from the beginning of the month until d2.

Note:

If d1 and d2 is in the same month, line 13 calculates from d1 to the end of the month and line 15 from the beginning of the month until d2 - which may seem like a wrong result. But line 14 in those cases give "-1" which makes the calculation correct anyway ;-)
• ###### 5. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
Thank you Folks for such quick Response.

Purvesh , the qurery looks fine. THouhg on quick question.

If I have a start_date as 01/Jan/2012 and end_date as 25/Mar/2012 ... How does for the amount of 500 , how does it works.

I mean How can I modifiy this query ?

Thank you all agian :-)
• ###### 6. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
Just a followup ;-)

Note that you can almost get what you want with the standard [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions102.htm#i78039]MONTHS_BETWEEN function.
But notice this quote from the documentation:

>
If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.
>

It will always use a 31-day month even if a month has fewer days.

So you could also implement the logic from my previous answer as your own implementation of months_between function:
``````create or replace function my_months_between (
p_todate    date
, p_fromdate  date
)
return number
is
begin
return ((extract(day from last_day(p_fromdate))-extract(day from p_fromdate)+1) / extract(day from last_day(p_fromdate)))
+ (months_between(trunc(p_todate-1,'MM'),trunc(p_fromdate,'MM'))-1)
+ (extract(day from p_todate-1) / extract(day from last_day(p_todate-1)));
end my_months_between;
/``````
I changed this to make it "compatible" with the standard function in that it calculates up to but not including the to-date parameter (which is correct when you consider dates with time included, but I presume in your case you just have date values.)

Then you can compare the results of the standard months_between with the new my_months_between:
(Note I call the functions with d2+1 to give the desired result of including both from and to date in the calculation.)
``````SQL> with testdata as (
2     select date '2012-03-03' as d1, date '2012-04-05' as d2 from dual union all
3     select date '2012-02-03' as d1, date '2012-05-02' as d2 from dual union all
4     select date '2012-03-03' as d1, date '2012-03-10' as d2 from dual union all
5     select date '2012-03-01' as d1, date '2012-07-31' as d2 from dual union all
6     select date '2012-02-01' as d1, date '2012-03-01' as d2 from dual union all
7     select date '2012-01-01' as d1, date '2012-03-25' as d2 from dual
8  )
9  --
10  -- end of test data --
11  --
12  select months_between(d2+1, d1) std_months
13       , 300 * months_between(d2+1, d1) std_allowance
14       , my_months_between(d2+1, d1) my_months
15       , 300 * my_months_between(d2+1, d1) my_allowance
16    from testdata
17  /

STD_MONTHS STD_ALLOWANCE  MY_MONTHS MY_ALLOWANCE
---------- ------------- ---------- ------------
1.09677419    329.032258 1.10215054   330.645161
3           900 2.99555061   898.665184
.258064516    77.4193548 .258064516   77.4193548
5          1500          5         1500
1.03225806    309.677419 1.03225806   309.677419
2.80645161    841.935484 2.80645161   841.935484

6 rows selected.``````
For much of the data the two functions give the same result, particularly when both dates are in months with 31 days.
For working from '2012-03-03' to '2012-04-05' the standard function gives too little because it always uses 31 days while my_months_between uses the 30 days of April.

But notice working from '2012-02-03' to '2012-05-02'. He skipped 2 days in february and worked 2 days in may - the standard function concludes he worked precisely 3 months.
But my_months_between calculates fraction 27/29 months in february, then march and april as 2 whole months, then fraction 2/31 months in may - which gives a total of 2.99555061 months rather than 3.

Just something you need to be aware of ;-)
• ###### 7. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
[Edit: for a minute, I thought Kim's code didn't cover different years, but of course it did!]
[Second edit: looking up, Kim's first solution is equivalent to mine, so my solution adds nothing new, sorry.]

Here's a method that takes into account months of 28, 29, 30 or 31 days:
``````with dates as (
select trunc(sysdate-15)-level*3 startdate,
trunc(sysdate-15)+level*5 enddate
from dual connect by level <= 25
)
select startdate, enddate,
round (300 * (
(extract(year from enddate) - extract(year from startdate)) * 12
+ extract(month from enddate) - extract(month from startdate) - 1
+ (last_day(startdate)-startdate + 1) / extract(day from last_day(startdate))
+ (enddate-trunc(enddate, 'MM') + 1) / extract(day from last_day(enddate))
), 2) allowance
from dates;

STARTDATE  ENDDATE    ALLOWANCE
---------- ---------- ---------
2012-09-13 2012-09-21        90
2012-09-10 2012-09-26       170
2012-09-07 2012-10-01    249.68
2012-09-04 2012-10-06    328.06
2012-09-01 2012-10-11    406.45
2012-08-29 2012-10-16    483.87
2012-08-26 2012-10-21    561.29
2012-08-23 2012-10-26    638.71
2012-08-20 2012-10-31    716.13
2012-08-17 2012-11-05    795.16
2012-08-14 2012-11-10    874.19
2012-08-11 2012-11-15    953.23
2012-08-08 2012-11-20   1032.26
2012-08-05 2012-11-25   1111.29
2012-08-02 2012-11-30   1190.32
2012-07-30 2012-12-05   1267.74
2012-07-27 2012-12-10   1345.16
2012-07-24 2012-12-15   1422.58
2012-07-21 2012-12-20      1500
2012-07-18 2012-12-25   1577.42
2012-07-15 2012-12-30   1654.84
2012-07-12 2013-01-04   1732.26
2012-07-09 2013-01-09   1809.68
2012-07-06 2013-01-14    1887.1
2012-07-03 2013-01-19   1964.52``````
My logic is:
1) Take the difference in years and multiply by 12
2) Take the difference in months and subtract 1
3) Take the number of days from the starting date to the end of the starting month, divided by the number of days in that month
4) Take the number of days from the ending date back to the start of the ending month, divided by the number of days in that month
5) Add all of the above and multiply the sum by the monthly allowance.

Others may wonder how this works if both dates are in the same month. In that case 3) and 4) will add up to 1 month too many, but 2) will be -1, so everything cancels out and the result is correct.

Edited by: Stew Ashton on Oct 1, 2012 4:43 PM

Edited by: Stew Ashton on Oct 1, 2012 4:46 PM
• ###### 8. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
Dear Kim ,

Thanks a lot for the wonderful query. You saved lot of my time. Thanks a lot.

Though I have a query. Is this query is dependent on the type of Date Format as 'YYYY-MM-DD'.

Can we use the Format 'DD-MM-YYYY' or 'DD-MON-YYYY' formats also ?

R
• ###### 9. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
user13807115 wrote:

Though I have a query. Is this query is dependent on the type of Date Format as 'YYYY-MM-DD'.
Dates don't have formats so it is not dependent.

http://edstevensdba.wordpress.com/category/nls_date_format/

There is no type conversion in the query so you can set your display format however you want and it will make no difference.
• ###### 10. Re: Query or Logic to Find number of Days Across each month.
Currently Being Moderated
Dear Kim,

I am really really thankful to you for your queries and information.

The first Query is working fine for my scenario.

Thanks a lot again.

Have a Great Day.

Regards
R

#### Legend

• Correct Answers - 10 points