Page 1 of 1
How to get the last business day of the given month.
Posted: Mon May 04, 2009 8:01 am
by 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......
Posted: Mon May 04, 2009 12:32 pm
by 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.
Posted: Mon May 04, 2009 1:19 pm
by 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.
Posted: Tue May 05, 2009 12:46 am
by divya824
I can get last date of the month like 2009-05-31 but I want to get in this format 2009-05-Sun
Posted: Tue May 05, 2009 1:32 am
by ray.wurlod
Time dimension table.
Hey i found very easy way to get Last business date
Posted: Wed May 06, 2009 1:42 am
by 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)
Posted: Wed May 06, 2009 2:52 am
by 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.
Posted: Wed May 06, 2009 6:22 pm
by rameshrr3
Better to implement a Calendar Dimension table, which also has a column with Holiday Flag ( Who says Friday Cant be a holiday
Posted: Wed May 06, 2009 6:46 pm
by 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.