No of days excluding weekend in a given month and year

general discussion
1

Does anyone has build logic to find out number of days excluding sunday and saturday in a given month and year?

Also number of days in month excluding just Sundays (including saturdays)

Thanks
Reema

reemagupta (BOB member since 2002-09-18)

2

As far as I know there is no simple answer to your question.

A month may not start on the 1st.

Once you excluded the week end you may also want to exclude public holidays, …

The best solution in these cases is to build a date table with all the relevant entries you require. It takes time to build but it is dead accurate and matches your business logic.

Talking about Logic.

By default SAP can manage 16 month and 6 quarters.
In retail a Month General last 4 weeks and ends on a Friday to exclude (this way you have a consistent number of “shopping days”

Sorry if this is not the answer. Hopefully this helps you to avoid some of the time traps.

ClaireB :de: (BOB member since 2002-08-09)

3

But if you really must try it then I think

to_number(to_char(last_day(row_date),'DD'))-(floor((to_number(to_char(last_day(row_date),'DD'))-1)/7)*2)-(decode(to_char(trunc(row_date,'MM'),'DY'),'SUN',1,'SAT',2,0))-(decode(to_char(last_day(row_date),'DY'),'SUN',2,'SAT',1,0))

should work (in Oracle anyway)
Once you figure it out you should be able to modify it to work with exclusion of Sunday only, I’m afraid I don’t have time to do that for you right now.

ken.gaul :uk: (BOB member since 2002-06-18)

4

Thanks Ken,

Do you have something similar for finding working days between two given days
1)once excluding saturdays and sundays
2)once excluding only saturdays

Reema

reemagupta (BOB member since 2002-09-18)

5

As I said don’t have time to figure out the sunday only thing but I guess if you just set the adjustment value to 0 for Saturdays and change the multipler from 2 to 1 (or miss it out altogether) it should work.

Also if you replace trunc(row_date,‘MM’) with your startdate and last_day(row_date) with your enddate and change the -1 just before the divide by 7 to the day number of month of your start date it should work for any arbitrary start and end dates.

I haven’t tried it yet but that should work.
This solution is provided with no explicit or implied warranty and any damage to systems or data either directly or indirectly caused by this solution will not in any way be traced back to me for damages or recompense. This does affect your statutary rights :wink:

ken.gaul :uk: (BOB member since 2002-06-18)

6

One more question
in the first statement what is row date, is the strat date of the month?

Thanks

reemagupta (BOB member since 2002-09-18)

7

Row_Date is just the date under consideration so given any date it will work out the number of working days in that month.

ken.gaul :uk: (BOB member since 2002-06-18)