To find the total number of days
Posted: Fri Jan 11, 2008 7:27 am
Hi, Is there any way to find the total number of days in a month using the transformer stage?
DSXchange
A short text to describe your forum
http://dsxchange.com./
To find the total number of days
Page 1 of 1
Posted: Fri Jan 11, 2008 8:00 am
Sure. How about subtracting the first day of the target date (internal format) from the first day of the next month, then subtract 1?
Posted: Fri Jan 11, 2008 9:27 am
Use a TIME dimension table and make one of its columns the number of days in the month of the date.
Thnk u guys
Posted: Mon Jan 14, 2008 7:25 am
I used the last_day(field name) fn of oracle in the sql space n got the number of days..
I ll try out your ideas too..
thnx anyway..
I ll try out your ideas too..
thnx anyway..
Re: Thnk u guys
Posted: Mon Jan 14, 2008 8:21 am
As Kenneth suggested, you could use the TIME dimension. Assuming that you are developing a data warehouse, the model will always contain a TIME dimension. Further, it is a common practice to include the number of days in the month as a column in that dimension.
Posted: Mon Jan 14, 2008 8:27 am
Yes, of course. I didn't pony that up (or the Oracle solution) as I thought the OP asked for a code solution 'using the transformer stage' and didn't mention the 'O' word.
Time to mark the thread as something - Resolved or Worked Around, regardless.
Time to mark the thread as something - Resolved or Worked Around, regardless.
Posted: Mon Jan 14, 2008 1:15 pm
In that case - except for when the Pope fiddled with the calendar - you could use a simple mapping from month number (and year number in the case of February) to number of days in the month.
You *could* do that all in one expression; I feel that stage variables make it neater, more efficient (only need to calculate Iconv() once each) and easier to maintain.
Code: Select all
Iconv(TheDate, "DM") -----> svMonth
Iconv(TheDate, "DY") -----> svYear
"31;28;31;30;31;30;31;31;30;31;30;31" -----> svMonthDaysList
Mod(svYear,4) = 0 And Mod(svYear,400) <> 0 -----> svIsLeapYear
Field(svMonthDaysList, ";", svMonth, 1) + svIsLeapYearRe: Thnk u guys
Posted: Mon Jan 14, 2008 1:25 pm
A Time "table" should exist in every single business IMO. We used to call them a "CALENDAR" table back in my early days. An algorithm is worthless when companies have different "open for business" rules. Some honor one holiday and not the other, etc. It was intolerable for IT (or DP as we used to be called) to have to always update programs/common libraries with the latest information as for which days were exceptions to the rules in order to get numbers to turn out right. A simple calendar table makes everything easier, but seems to be out-of-fashion these days.gateleys wrote:As Kenneth suggested, you could use the TIME dimension. Assuming that you are developing a data warehouse, the model will always contain a TIME dimension. Further, it is a common practice to include the number of days in the month as a column in that dimension.
Posted: Mon Jan 14, 2008 1:55 pm
Totally agree, Ken, it should. When I was warehousing we always had one and it came in quite handy for things like you mentioned - all things date were answered there. However, recent efforts I've witnessed seemed to not even consider it. Damn kids.
The OP only asked for the total number of days in any given month (something suited to an algorithm) not anything like 'business days' or my answer would have closely mirrored yours.
Now, the 'content store' that is my life's work at the moment has no such beastie in it and no need for one, it seems. For whatever that is worth.
The OP only asked for the total number of days in any given month (something suited to an algorithm) not anything like 'business days' or my answer would have closely mirrored yours.
Now, the 'content store' that is my life's work at the moment has no such beastie in it and no need for one, it seems. For whatever that is worth.