Originally posted by: SystemAdmin

Hi - we are using Ascential DataStage 7.1 to load data to MS SQL Server database on MS Windows platform.

I have calculated day difference between two dates (MS SQL timestamp dates like: 20040702 and 20040830 12:30:34.000 with or without time part) by converting both dates to seconds and subtracting them. Now, the I need to exclude weekends from the day differerence interval.

Does anybody know an effective way how to calculate working days and exclude weekends from an interval between 2 dates?

Dina
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

Based on a Microsoft solution at http://support.microsoft.com/default.aspx?scid=kb;en-us;114946 the following will work if you are not adverse to using a lookup table.

Create the lookup table:

code:1:998e85cea9 DAYS LEFT TABLE

Day Started
Days Left Sat Sun Mon Tue Wed Thur Fri
0 0 0 0 0 0 0 0
1 0 1 1 1 1 1 0
2 1 2 2 2 2 1 0
3 2 3 3 3 2 1 1
4 3 4 4 3 2 2 2
5 4 5 4 3 3 3 3
6 5 5 4 4 4 4 4
[/code:1:998e85cea9]

I formatted mine as:
00000000
10111110
21222210
32333211
43443222
54543333
65544444

My type tree, DaysLeft.mtt, had 1:1 numeric fields per record.

I show two rules below for clarity. Rule 1 may be substituted in Rule 2 for ExtractedDaysLeftRecord.

Rule 1: code:1:998e85cea9LOOKUP(Record:DaysLeft, TEXTTONUMBER(LEFT(NUMBERTOTEXT(Field:Record:DaysLeft), 1) ) = MOD(DATETONUMBER(EndDate) - DATETONUMBER(StartDate ), 7 ) )

/* Retrieve the table row where Column 1 equals the modulus of (End Date - Start Date) / 7 [/code:1:998e85cea9]

Rule 2: code:1:998e85cea9 (INT( (DATETONUMBER(EndDate) - DATETONUMBER(StartDate) )/ 7) * 5 ) +
TEXTTONUMBER(MID(ExtractedDaysLeftRecord, MOD(DATETONUMBER(EndDate) - DATETONUMBER(StartDate), 7) + 1, 1 ) )

/* The number of working days equals the number of full seven week periods * 5 +
the table value corresponding to the column for the day of the week of the Start Date in the row corresponding to the remainder of the total elapsed days divided by seven[/code:1:998e85cea9]
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Nevermind. You may get a better response if you post in the the DataStage forum instead of DataStage TX (that's DataStage TX, b:52329af6a5i:52329af6a5TX[/i:52329af6a5][/b:52329af6a5]) forum.
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Will do so. Thanks for your suggestion and the solution. Looks kind of complicated to me. Besides, I am trying to put this calculation into a Basic routine without using a reference table.

Thanks anyway, Dina
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

As long as you have a FOR or WHILE loop function, there are solutions all over the Internet. DataStage TX doesn't have FOR or WHILE loops, hence the ugly solution.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender