How to get the last business day of the given month.

[divya824]

Hello Experts,
I have an issue which sholud bring the last working date or Business date in a month.So how can i do this with the help of datastage Functions.Since my job having Source as Txt file and Target is DB2.

Ex:-
Date from Source:-
2009-05-02

Target sholud be:-
2009-05-29 (friday)


plz help me yaar......

[chulett]

What is a "working" date? Do you need to handle holidays as well?

Either leverage an existing Time dimension or convert the date to the first of the next month and then subtract one day from it and see if that is a "working" day. If not, keep going backwards until you hit one.

[dsedi]

Logic? google it... u'll find a lot..
u can do it thru UNIX or via SQL

but,as Chu mentioned, to determine if that day is a holiday the idea is to store the holiday list in a database table and check your input against that.

[divya824]

I can get last date of the month like 2009-05-31 but I want to get in this format 2009-05-Sun

[ray.wurlod]

Time dimension table.

[divya824]

Replace lnkname.colname with your linkName.ColName accordingly :D
If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='0' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-2 Else If (WeekdayFromDate( (DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),'Sunday')) ='6' Then Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)-1 Else Left((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")),7):"-": Right((DateFromDaysSince(-1,StringToDate( (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1 Else YearFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))):"-": (If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='11' Then '12' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='10' Then '11' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='9' Then '10' Else If MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))='12' Then '01' Else 0:MonthFromDate((StringToDate(lnkname.colname,"%yyyy-%mm-%dd")))+1):"-":'01',"%yyyy-%mm-%dd"))),2)

[Sainath.Srinivasan]

That appears quite a huge code - especially to debug in case of any issues.

Didn't go through it.

Time dimension is the best answer.

But assume it can also be resolved as

Code: Select all

svLastDayOfCurrMonth = 
  (If CurrMM = 12 
   Then CurrYYYY : '-12-31' 
   Else (CurrYYYY : '-' : CurrMM + 1 : -01') - 1 )
svWeekDay=WeekDayFromDate(svFirstDayOfNextMonth)
svRequiredDate = 
  (If svWeekDay in (Sat, Sun) 
   Then PreviousWeekDay(svLastDayOfCurrMonth, 'Fri') 
   Else svLastDayOfCurrMonth)

Need to refine few the above code but I leave that to you.

[rameshrr3]

Better to implement a Calendar Dimension table, which also has a column with Holiday Flag ( Who says Friday Cant be a holiday

[ray.wurlod]

Indeed, at least one Friday is a holiday in Christian cultures, and Friday is not a business day in Islamic cultures.

A Time (or Calendar) dimension is the preferred solution. A Time dimension at daily granularity might even include a "last business day of month" column if that makes good business sense.