Last Business Day - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Last Business Day

Author

Topic

dz0001
Starting Member

35 Posts

Posted - 2012-04-11 : 17:27:57

Hi There,

I have SQL 2008 R2 Cube, which has time dimension Dim Date is the name of dimension, YQMD is the hierachy I set it up for (year, Quarter, Month and Day. There is attribute [Weekday Weekend] has two values Weekday or Weekend.

How do I get Last Busines Day, for example, if it is April 9 (Monday), then my last business day is April 6 (Friday).

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-12 : 03:33:20

Add a new column to the time dimension and update accordingly.
See this update statement

UPDATE	d
SET	LastBusinessDay =	CASE DATEDIFF(DAY, '19000101', theDay) % 7
					WHEN 0 THEN DATEADD(DAY, -3, theDay)
					WHEN 6 THEN DATEADD(DAY, -2, theDay)
					ELSE DATEADD(DAY, -1, theDay)
				END			
FROM	(
		SELECT	LastBusinessDay,
			DATEADD(MONTH, 12 * [Year] + [Month] - 22801, [Day] -1) AS theDay
		FROM	dbo.TimeDimension
	) AS d

N 56°04'39.26"
E 12°55'05.63"

dz0001
Starting Member

35 Posts

Posted - 2012-04-12 : 11:12:50

Thanks for the script, I just run it, it handles Monday - Friday perfectly, Monday will show Last Friday, Saturday will show Friday.

However Sunday doesn't seem right, it is show Saturday instead of Friday.

Thanks again for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-12 : 12:35:49

See the edited query above (in red).

N 56°04'39.26"
E 12°55'05.63"

dz0001
Starting Member

35 Posts

Posted - 2012-04-12 : 14:30:55

Sorry, I may make a mistake somewhere, for example, my last business day for April 1, 2012, is June 29, 2012. April 2, 2012, I got July 2, 2012...

Just want to confirm:

For DATEADD(MONTH, 12 * [Year] + [Month] - 22801, [Day] -1) AS theDay

What Year, Month and Day value I should use from my Time table, I am using, for example,

Year, for example, 2012,
Month, I am using Month of Year (1 - 12)
Day, I am using Day of Month (1-31) or Day of Year (1-365)?
Also why 22801?, my calendar table is from 1990 to 2050, do I need to change this value?

Anything I miss here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-04-12 : 16:53:05

Year is a year between 1900 and 9999
Month is a month between 1 and 12
Day is a day between 1 and 28/29/30/31.

N 56°04'39.26"
E 12°55'05.63"

dz0001
Starting Member

35 Posts

Posted - 2012-04-12 : 17:40:29

Thanks again for pointer, it works now!!

One more question, now I got lastbusinessday populated with data in my Time table, how do I use this column in my cube, and from reporting service report? Basically users want to have report LastBusinessDay (LBD) and MonthToDate (MTD) orders by Department, something like this:

LBD MTD
Department Order Order

Many thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources