Last working day from last year!

Dear Friends,

How can I retrieve the last working day from the currentyear-1?

Thanks

In SQL, or an expression inside SSIS?|||

Not sure what this has got to do with SSIS.

Which country are you in? Because different countries have different public holidays around the end of the year and this obviously makes a difference.

-Jamie

|||

I need to create a new column with the last working day for each row in the datasource... as for example inside a derived column...

How can I do it?

And I have the problem about holidays... but could be resolved if I have a table wich indicate the holidays for this case in my country Portugal... or have to apply for all the cases using a key to each country...

could help me?

Thanks!!

|||How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!|||

PedroCGD wrote:

How can I get the last working day for the last year for each row in data source.. how can I include it inside a transform like derivaded column?
Help me!!

What is considered the last working day of the year? The last Friday?|||YES|||

This is usually a table-driven information item such as a time dimension table. The DIMTIME records would be flagged for weekdays.

As Jamie mentioned, holidays are a regional thing so you would join entries in a DIMTIME table against a local holiday table to derive a list of working days. Then you could query the join to find the last working day of a given year.

This is not a SSIS issue, but a TSQL or engine question.

|||

No Benda, forget the holidays....

I need to transform each row, to add a new column that for each date I need to get the Reference Rate that is the last day of the last year for the each row of the data source....

thanks

|||This assumes you have a VARIABLE containing the year. If you don't have that, and need to dynamically read the year, replace the variable here with DATEPART("yyyy",[your_date_field])

So you'd replace "(DT_WSTR,4)@.[User::Year]" with "DATEPART("yyyy",[your_date_field])" if that's the case.

DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) < 6 ? DATEADD("d",-1 * (DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) + 1),(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : DATEPART("dw",(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) > 6 ? DATEADD("d",-1,(DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])) : (DT_DBDATE)("12/31/" + (DT_WSTR,4)@.[User::Year])|||

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

|||

PedroCGD wrote:

Phil,

i was cheking the dates, and your code is not returning the last working day of the year, in spite is returning the saturday...

My last working day of a week is friday not saturday! :-)

Where you define it in your code?

Thanks!

That's a locale issue then... DATEPART("dw",[datefield]) == 6 for Fridays here in the US.

Never the less, you should be able to adjust the logic by playing around with it a bit. Try to read the expression I've built for you and work on it your own. It's pretty simple logic, really, despite the fact the expression looks complicated.|||your code in US give you the friday?|||

PedroCGD wrote:

your code in US give you the friday?

Yes, absolutely. I tested it for several years. 2006 = 12/29/2006 = Friday|||

your code for me gives me 2006 = 12/30/2006 = Saturday!

:-(

bbrrr.... :-(