ignore weekends and bank holidays

building semantic layer
1

Hello
I have a calculation as a measure to work out the mean time for product orders and product invoices. I am using a derived table where the start and end dates for each month are retained. However, we only work Monday to Friday and I need to exclude weekends and bank holidays. At present the mean time is high and not reflected correctly. I am using BOB version 2003. I am new to sql so any assistance you can provide would be greatly appreciated.

Code of measure is as follows:

NULLIF (
SUM
(CASE WHEN PRODUCT.END BETWEEN DT_DATES_STARTMON + ' 00:00:00' AND DT_DATES_ENDMON + ' 23:59:59' AND NOT(DT_PRODUCT_ORDER_DATES.MAX_ORDER_DATE <= DT_PRODUCT_ORDER_DATES.MAX_INV_ORDER_DATE) 
THEN CAST(DT_PRODUCT_ORDER_DATES.HOURS_DIFFERENCE AS FLOAT) 
ELSE NULL END
) 

/ 

COUNT (DISTINCT 
CASE WHEN PRODUCT.END BETWEEN DT_DATES_STARTMON + ' 00:00:00' AND DT_DATES_ENDMON + ' 23:59:59' AND NOT(DT_PRODUCT_ORDER_DATES.MAX_ORDER_DATE <= DT_PRODUCT_ORDER_DATES.MAX_INV_ORDER_DATE)
THEN DT_PRODUCT_ORDER_DATES.UNIQUE_KEY ELSE NULL END)
,0)

[Moderator Edit: Added code formatting - Andreas]

Rustyotters (BOB member since 2011-02-26)

2

What is your underlying database?

Here is a link to a guide using SQL Server
Another link for SQL Server.
An Ask Tom link for discoutning weekends in Oracle.

I too would focus on a SQL based solution and not a BO based solution.

Logically it should look something like this.
work days = (true end_date - true start_date) - (weekend dates in range) - (holidays in range)

To know the holidays in the range you’ll need a table of those dates.

The holiday portion of that formula would look like this.
select count(*) from holiday_table ht where ht.date between true_start_date and true_end_date;

I’m using Oracle date math logic here numerically 1.0 = 1 day and 0.5 = 12 hours. I can’t vouch for other SQL providers.

kbrazell :us: (BOB member since 2003-08-19)

3

Thank you :smiley:
I will review the links and see if this works.

Rustyotters (BOB member since 2011-02-26)

4

I’ve looked at the links you provided, thank you. I’m using SQL Server not Oracle. I am not sure how to change my measure to exclude weekends. I can not add or make any changes to the table in the universe as this is fixed only to contain e.g. 01/01/2012 00:00:00 to 31/01/2012 23:59:59 and so on. I can not also make any changes to the actual sql database.
Can you suggest how the sql in the measure can be fixed to exclude weekends? Thanks in advance. :frowning:

Rustyotters (BOB member since 2011-02-26)

5

There are some bank holidays that vary and some that are fixed and herein lies your problem. You can create some horrendous calculations to code around Christmas Day but what about Easter and other variable days? You need a calendar table with a working day flag in it to correctly and efficiently calculate working days.

You need to raise it as an issue.

6

ok thanks

If the issue can not be resolved at the universe level, what code would you suggest for a FUNCTION in the database and how would I relate this back to my measure?

Rustyotters (BOB member since 2011-02-26)

7

I’d honestly do it with a calendar table in the database and calculations in the universe based on that or more likely a SQL Server function that you can use at the universe level.

8

When I faced this problem before, we used a calendar table, combined with a function that took care of the holidays. This seemed to perform OK.

Ideally, you would use a calendar table for everything. It should be noted that any solution for holidays has to be maintained on a yearly basis as these, of course, vary.

Mak 1 :uk: (BOB member since 2005-01-06)

9

Thanks again

I’ve been advised that our calendar for month is going to have to stay as start of month and end of month. If a function is required to relate to this calendar what would you suggest the code would look like? I understand this will have to be maintained every year etc.

Rustyotters (BOB member since 2011-02-26)

10

Not sure what you mean by this.

Does your calendar table have individual days in it?

11

it looks like this:

SELECT 13 SEQ, ‘Y’ FLAG, ‘01/01/2010’ STARTMON, ‘01/31/2010’ ENDMON UNION SELECT 14, ‘Y’, ‘02/01/2010’, ‘02/28/2010’ UNION SELECT 15, ‘Y’, ‘03/01/2010’, ‘03/31/2010’ UNION SELECT 16, ‘Y’, ‘04/01/2010’, ‘04/30/2010’ UNION SELECT 17, ‘Y’, ‘05/01/2010’, ‘05/31/2010’ UNION SELECT 18, ‘Y’, ‘06/01/2010’, ‘06/30/2010’ UNION SELECT 19, ‘Y’, ‘07/01/2010’, ‘07/31/2010’ UNION SELECT 20, ‘Y’, ‘08/01/2010’, ‘08/31/2010’ UNIONSELECT 21, ‘Y’, ‘09/01/2010’, ‘09/30/2010’ UNION SELECT 22, ‘Y’, ‘10/01/2010’, ‘10/31/2010’ UNION SELECT 23, ‘Y’, ‘11/01/2010’, ‘11/30/2010’ UNION SELECT 24, ‘Y’, ‘12/01/2010’, ‘12/31/2010’ UNIONSELECT 25, ‘Y’, ‘01/01/2011’, ‘01/31/2011’ UNION SELECT 26, ‘Y’, ‘02/01/2011’, ‘02/28/2011’ UNION SELECT 27, ‘Y’, ‘03/01/2011’, ‘03/31/2011’ UNION SELECT 28, ‘Y’, ‘04/01/2011’, ‘04/30/2011’ UNION SELECT 29, ‘Y’, ‘05/01/2011’, ‘05/31/2011’ UNION SELECT 30, ‘Y’, ‘06/01/2011’, ‘06/30/2011’ UNION SELECT 31, ‘Y’, ‘07/01/2011’, ‘07/31/2011’ UNION SELECT 32, ‘Y’, ‘08/01/2011’, ‘08/31/2011’ UNIONSELECT 33, ‘Y’, ‘09/01/2011’, ‘09/30/2011’ UNION SELECT 34, ‘Y’, ‘10/01/2011’, ‘10/31/2011’ UNION SELECT 35, ‘Y’, ‘11/01/2011’, ‘11/30/2011’ UNION SELECT 36, ‘Y’, ‘12/01/2011’, ‘12/31/2011’ UNION SELECT 37, ‘Y’, ‘01/01/2012’, ‘01/31/2012’ UNION SELECT 38, ‘Y’, ‘02/01/2012’, ‘02/29/2012’ UNION SELECT 39, ‘Y’, ‘03/01/2012’, ‘03/31/2012’ UNION SELECT 40, ‘Y’, ‘04/01/2012’, ‘04/30/2012’ UNION SELECT 41, ‘Y’, ‘05/01/2012’, ‘05/31/2012’ UNION SELECT 42, ‘Y’, ‘06/01/2012’, ‘06/30/2012’ UNION SELECT 43, ‘Y’, ‘07/01/2012’, ‘07/31/2012’ UNION SELECT 44, ‘Y’, ‘08/01/2012’, ‘08/31/2012’ UNIONSELECT 45, ‘Y’, ‘09/01/2012’, ‘09/30/2012’ UNION SELECT 46, ‘Y’, ‘10/01/2012’, ‘10/31/2012’ UNION SELECT 47, ‘Y’, ‘11/01/2012’, ‘11/30/2012’ UNION SELECT 48, ‘Y’, ‘12/01/2012’, ‘12/31/2012’ UNION SELECT 49, ‘Y’, ‘01/01/2013’, ‘01/31/2013’ UNION SELECT 50, ‘Y’, ‘02/01/2013’, ‘02/28/2013’ UNION SELECT 51, ‘Y’, ‘03/01/2013’, ‘03/31/2013’ UNION SELECT 52, ‘Y’, ‘04/01/2013’, ‘04/30/2013’ UNION SELECT 53, ‘Y’, ‘05/01/2013’, ‘05/31/2013’ UNION SELECT 54, ‘Y’, ‘06/01/2013’, ‘06/30/2013’ UNION SELECT 55, ‘Y’, ‘07/01/2013’, ‘07/31/2013’ UNION SELECT 56, ‘Y’, ‘08/01/2013’, ‘08/31/2013’ UNION SELECT 57, ‘Y’, ‘09/01/2013’, ‘09/30/2013’ UNION SELECT 58, ‘Y’, ‘10/01/2013’, ‘10/31/2013’ UNION SELECT 59, ‘Y’, ‘11/01/2013’, ‘11/30/2013’ UNION SELECT 60, ‘Y’, ‘12/01/2013’, ‘12/31/2013’ UNION SELECT 61, ‘Y’, ‘01/01/2014’, ‘01/31/2014’

Rustyotters (BOB member since 2011-02-26)

12

I have to say thats a real piece of work :rotf:. Why can you not have a proper calendar table built in SQL Server?

Mak 1 :uk: (BOB member since 2005-01-06)

13

exactly! I have to work with what I’ve got. Very badly designed. Can you help?

Rustyotters (BOB member since 2011-02-26)

14

These posts may give you a couple of ideas to play around with:-

https://bobj-board.org/t/104238

https://bobj-board.org/t/119096

Mak 1 :uk: (BOB member since 2005-01-06)

15

ok thanks

Rustyotters (BOB member since 2011-02-26)