date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

date function/tool to determine future business days/ holidays

Hi,

Does SQL server 2005 provide a function to determine future BUSINESS day ? What about holidays ? I sthere a way to feed a holiday file into a table and have SQL Server determin if the future date falls on a business day ?

As far as you do not mean the fixed calculated public holidays (like Easter, which can be calculated with a Gauss formula) you will have to implement that on your own having a custom table with the special dates.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks but I am still unclear. If I'd like to know if today's date + 100 days from now would fall on a business day . Is there a ready-to-use MS SQL function that I can use or should I write it on my own ?

|||

Well, define business day. There is not a common understanding of business day (even in one countrym like here in Germany :-) )

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello. I think the following library of functions can help with the issue of finding Business Days:

http://www.SQLsharp.com/

I just added some date functions and one of them is finding the number of business days within a given date range. And because not everyone can agree on what is a business day, the function allows the user to select which days are to be filtered out as non-business days. I have not done a function to find if a particular day in the future is a business day, but that can be added quite easily. This function can determine holidays on any given year so you don't need the typical holiday table that most people suggest using.

Date Formatting

I have a report with a date field and I am trying to add another field that will be 10 business days from the current field. So it needs to add 10 days while skipping weekends and holidays. Can anyone help??Maybe you could try a loop:

I don't know the right syntax, but you get the idea... Also, this doesn't count for Holidays.

DateVar dtmNewDate := {CurrentDate}
NumberVar DaysAdded := 1

Do Until DaysAdded = 11
dtmNewDate := DateAdd("D", 1, dtmNewDate)

If DayOfWeek (dtmNewDate) <> "Saturday" And DayOfWeek (dtmNewDate) <> "Sunday" Then
DaysAdded := DaysAdded + 1
End If

Loop

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

Date Filtering vs Row placement misbehaviour

I am using the classic Add Business Intelligence date dimension hierachy and getting correct results when I had my calendar hierachy on the rows expanded to the day level and my dimension calculations on the columns. The results were wrong when I moved the calendar hierachy to the filter area and selected one date.

In particular, the [Date].[Date Calculations].[Current Member Name] = [Date].[Reporting Calendar].CurrentMember.Name shows that when you filter the Hierachy down to the day, the Current Member is always incorrectly showing the week (in every cell). It is correct when filtered to all other higher levels than date (week, month...).

I have tested this on multiple date dimensions for some days now and it is very consistent. Also If you bring the Date Attribute Hieracy onto the rows, it jumps to the correct Current Member.

Anyone seen anything like this - filtering by a Hierachy member does not work at the lowest level (the day level) but it does work as a row heading? It may be related to the lowest level being the Dimension Key?

Weird.

See my post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=517552&SiteID=1 with TOPCOUNT and many hierarchies. I am also getting inconsistent results.

Are we missing somehting when we set a 'conformed' hierarchy? I did not have the lowest level as the dimesion key. Then I set it to the DImension key for my 11 hierarchies in the Product dimension. It worked for a small sample data set and then it started failing when I introduced it to the main project with 10+ dimensions and many hierarchies.

Let me have your views on this.

|||

I looked at your issue. It is not obvious to me that the problem I had with .CurrentMember being wrong under a lowest level filter member is the same as your TOPCOUNT problem. I had to move my formulae out to a different dimension and put them on an attribute hierachy to work.

However, by lots of trial and error with the display of simple diagnostic formulae, it is likely that you can identify a key simple value that is not correct. We need more such simple problem examples because I am convinced there is a basic structural problem in the implimentation of MDX against UDM and it will show up as the wrong answer in many different circumstances.

Good luck with finding a structure where the formulae works - at least UDM had many choices.

|||

If you have defined date hierarchies such as Fiscal or calendar, have you ensured that the key element of the attributes used in the hierarchy is unique? You might have to use the key collection to define a combined key of Year, Quarter for the quarter in the attribute hierarchy.

I think that is why you maybe seeing the wrong results. I am looking through all my attribute hierarchies to ensure that I have these set-up correctly.

|||

I am using unique running integers as the key of the attributes in the hierachy.

Also I have repeated the problem in any other dimension hierachy where the lowest level used as a filter member causes the .Currentmember to get the incorrect value (it gets the parent member of the filter value)

So it seems to be an issue on even the simplest dimension hierachy when the formulae is added to a hierachy within the filter dimension. I fixed it by moving all the formulae out to a completely different dimension.

date business intelligence

Hi,

I have a date dimension with the following attributes:

datekey

fulldate

weeknumber

month

quarter

semester

year

I have also created Named Calculation for Quarter, Semester and Month to make them more descriptive and associated them with the name column of each attribute.

e.g. Month: January 2005

Quarter: Q1 2005

However, I notice that the dates that were displayed are not correct. Sometimes I would have January 2017 even the records doesn't show any year 2017.

Why is this so? and how can i resolve this?

cherriesh

Take a look at the relationships between your attributes in the Date dimension. This may be the source of your problem.

To illustrate, consider the two month-related attributes in your dimension. You have both Month of Year, e.g. January, and Month, e.g. January 2007, attribute hierarchies. The Month Of Year attribute hierarchy is related to date but itself has no other relations. The Month attribute hierarchy is also related to date and has a relation to Quarter, e.g. Q1 2007, which has a relation to Year.

B.

|||

hi,

sorry but how do i do the checking? i went to Dimension Structure of my Date Dimension and in Quarter attribute (this has the named calculation 'QuarterDesc' on the Name Column) I dropped the Year attribute on the <New Attribute Relationship> and reprocess the cube. It's still the same.

anything i missed?

thanks a lot.

cherriesh

|||

No problem. For a first step, let's verify the TYPE property of each of your attribute hierarchies. Right-click each of the hierarchies, select Properties, and record the TYPE property value. Here are the attribute hierarchies you identified:

datekey

fulldate

weeknumber

month

quarter

semester

year

Once we have that info, we'll look at the relationships.

Thanks,
Bryan

|||

thanks a lot!

cherriesh